Here’s a scenario that you may run into now and then using Tableau – you need to conditionally aggregate a dimension based on privacy or other concerns.
Example:
You’re in HR, and you need to build a report which displays managers and all of their respective reports – including each employee’s salary.
The employee names must be anonymized. If the manager has very few reports, you don’t want to show salary-per-anonymous-employee at all. It still would be pretty easy to guess who is who in a department of two people – we therefore need to aggregate.
So, for managers with “many” reports, we’ll show many individual salaries. For a manager with “few” reports, we only get a sum of payroll.
I approached this with table calcs and got pretty close. That said, I couldn’t get quite what I wanted and I asked for help.
Mark “the first Jedi” Rueter responded with a completely different beautifully simple solution using Sets. It’s his work, I just made it pretty.
Created a Calculated Set
Create a set based on a condition that counts the number of employee on a per-manager basis. In the screenshot below, EmpCount is a parameter in which hardcode the “cut off” number of employees-per-manager that we need to see before we aggregate. The [Category] field is a stand-in for Employee Name] – Pretend it says employees!:
Drop that set in a Calculated Field
Create a field which shows either the Employee Name ([Category]) OR the word “Restricted” if the user isn’t in the Set. Use this Calculated Field to drive color:
Create a Hierarchy
If you want, add the new Calculated Field to a Manager > Employee hierarchy. You can use this later to open up the bar chart and see employees underneath each manager.
With those items in place, you’re set.
Here’s my dashboard with a “cutoff” set to 2. In the dataset, each manager DOES have at least 2 employees, so we see everything:
And now, set the cutoff to 4. I only have one manager with at least four employees, so we aggregate on the other two. Could I have done a better job with the axes below? Maybe used an Average aggregation instead of Sum in the Box-and-Whisker? Sure. But I didn’t.
