I think you are correct about the issue. When you filter by ***, the row containing the count of males that doesn't have a corresponding department/year row for females is dropped from the table. So, although the LOD expression is calculating the value correctly, it isn't included. One way to work around this would be to use a parameter and new calculated field. Here's what I did:
Create a string parameter with values of All, Female, & Male. (I called it [*** Parameter].)
Create a calculated field with the following formula, which basically filters to the count based on the parameter selection:
if [*** Parameter]="All" then [Count]
elseif [*** Parameter]=[***] then [Count]
You can then use the calculated field in the calculation of the percent instead of the original count. The row with no female (or male) applications will still show in the table since there is a value for total applications (the LOD calculation) so that value is now included in the totals. If you wanted, you could add an "else 0" before the end statement so that the missing row will show in the table the same way the rows with 0 currently appear for consistency.
Hope this helps
Sample Data - Thread 203005.twbx 57.6 KB
Great solution Marc. Providing a null for a undesired value rather than utilizing a filter keeps the data desired in the view and allows the calculation to work.
Since Tableau documentation indicated that dimension filters were ignored in FIXED LOD's, I thought I was ok. Your explanation/solution suggests that there is a least one caveat to that statement. Thanks, again!
This is like an inverse LOD for comparing total vs specific departments. Solved a two week on and off head-wall bashing mystery for me. Thank you.
This is super useful for dual-axis histograms comparing departments to corporate totals.
Hi Kevin! I am having a similar issue and my head is starting to hurt from all the head-wall bashing I have been doing. Will you be willing to share how you solved your issue in comparing total vs specific departments?
My issue is such that although I was able to create a calculated field for the system total and average, this number changes when I filter out some of the specific departments. I would like for it to be such that the system totals will not change, and are not affected by the department filters.
Any help is greatly appreciated!
You can try using a set and hiding the values not in the set (values you filtered by) so that your view only shows the departments you want but the calculations are done across all data.
I'd try Lisa's response if you are looking for filtering multiple departments. Though I'm not 100% how you can select different combos using sets (I haven't studied this yet.)
My solution involved selecting only one department at a time and comparing it against the total, a separate measure value in the view. So my parameter is the department I want information on, and I use the Parameter Control to filter between departments, and not the filter function.
If this doesn't make sense let me know and I will explain in more detail.
edit: just to note, you're running into one of the most popular requested features for Tableau, which is the ability to select multiple values of a parameter at a time:
In Tableau 10 you can use a parameter to filter across datasets: As Requested, You Can Filter across Data Sources in Tableau 10 | Tableau Software
You might be able to jerry-rig this into having one table with the departments, and the other table with the totals, and then joining the table on itself.. would be a bit of work though. ...Ask Lisa about the sets!
Thanks! Let me try that!