There's not a way to include/exclude values from your calculated field, unless you created a parameter and separate calculated fields for all possible combinations of departments [very clunky way to get where you want to be].
You're correct in thinking restructuring the data would make this much easier.
A quick fix would be to pivot the data within tableau. This doesn't require you to restructure the data at the true source (you could apply the pivot after HR sends over the data).
This website gives pretty simple directions to applying a pivot using custom SQL
The pivot will result in a single column for your departments titled "Department" and within that column each week will be labeled with the corresponding department, likewise, there will be a single column for hires, with the number of hires by week listed for each week. When the pivot is applied, "Department" will be a dimension in your data and users will easily be able to select which department they want to view.
I'd encourage you to try the pivot, if possible.
I stalked your LinkedIn profile. I see you're a CU-Boulder grad... me too, haha.
sweet! go buffs! hope that works ^
lizabeth Kreitler CU Buffs must be really smart. Your way was great, but it didn't give me a way to filter on both data and measure. BUUUT, I found a different way to get what I was looking for:
- Created a dual axis with all the measures (columns) as values.
- Added a filter on measure names
- Put the aggregate values (calculated field) on the opposite axis, and marked them as "Gantt Bar"
Now you have the total values of all the columns, and a drop-down filter will allow my VP to select what proportion of each measure is contributing to the full one.