The reason you are seeing this is that the filter is evaluated first (at the database level), then the percent is calculated after the data is returned to the Tableau Data Engine.
So, the trick is going to be to apply a filter after the data is returned. You can do this by filtering a field that is a table calculation. So, in the attached workbook (which is evaluated after the data is returned from the database), I created a calculated field:
LOOKUP(ATTR([Area Description]), 0)
And then put that on the filter shelf. That filter is then applied after the data is returned, thus it does not affect the percentages.
That worked well, thanks Joshua.
Thanks Ed. I too had a similar requirement and your solution worked. However I want to provide for additional filters. So expanding on the example above if there was also a Region attribute associated with each Area, and if I were to provide filtering by Region or specific areas, how can that be accomplished? I am thinking that there must be some kind of 'Or' logic involved, just don't know how/if that can be coded. Following possibilities arise:
1. At least 1 Area selected but no Region selected - Shows only on selected Areas
2. At least 1 Area selected and 1 or more regions selected - Shows all selected Regions and only the Areas selected
3. All Areas selected and 1 or more regions selected - Shows Areas of the selected Regions
4. No Area Selected and No Region Selected (nothing to report)
I think I figured one part of it. Missed the fact that LOOKUP function has index or position of column that one can specify. So that solved the logic for filter by Region. That said, if I select all areas and only 1 region it shows all areas and the measures only for the regions selected. Trying to see if there is a way to also just show the areas associated with selected regions.
Really loved your solution.Could you explain how this function works as i am not very familiar. I understand only the basic definition as mentioned in Tableau.
Hello, Sorry to enter your conversation but I would like to know if anyone knows how to do the opposite to what you request ED KUKEC. I need to display the 50% of the products in this table and the overall column totals.
Product Sub-Category Sales RUNNING_SUM(SUM([Sales])) SUM([Sales]) / TOTAL(SUM([Sales])) RUNNING_SUM(SUM([Sales]) / TOTAL(SUM([Sales]))) Office Machines 131,381.18 131,381.18 17.51% 17.51% Tables 110,544.98 241,926.16 14.73% 32.25% Copiers and Fax 94,935.84 336,862.00 12.65% 44.90% Binders and Binder Accessories 73,799.70 410,661.70 9.84% 54.74% Telephones and Communication 65,317.25 475,978.94 8.71% 63.44% Storage & Organization 64,612.86 540,591.80 8.61% 72.06% Office Furnishings 47,691.86 588,283.66 6.36% 78.41%
I put the calculation of tables in filters and samples the smaller ones to .5 but in the general total of column shows the total of 100% not of 50%.