1 of 1 people found this helpful
Unfortunately, table calculations are sensitive to the discrete non-aggregate pills in the view. As you've seen, whenever a user expands or collapses the hierarchy, that changes the partitioning of the table calculations and, given your data set, the results change. There might be a way to get the total you want using a combination of PREVIOUS_VALUE(), LOOKUP(), and other calcs, but that will get really messy really fast, and not be particularly fast.
So, here's an alternative - duplicate your data source, then edit the relationships to delete the hierarchy levels as linking fields, so the only field that links is the User Attribute. Then you can drag calculations from the secondary data source into the view and have them aggregated at the proper level and be invariant to setting of the pills.
I've set this up in the attached using Superstore Sales data, using Region in place of User Attribute and the Category/Sub-Category/Product Name hierarchy.
Thanks Jonathan. This is an interesting approach, I will try playing around with this. My initial concern would be the size of the Tableau data set. This approach will double the size, correct?
And I believe the answer is "yes" but I'll ask anyway: will this approach work if -- as is the case for what I'm building -- the User Attribute field (the "Region" field in your example) is a parameter? As such it could be 1 of many dimensions that the user can pick. The way your calcs are set up they will work regardless, no?
Thanks again for your help.
Yes, this approach will double the size if the data is files and not live. An alternative if you are using Tableau Data Extracts is that the secondary data source could connect to same extract used in the primary data source.
To use a parameter to pick the dimension, you're going to need to have the calculated field that returns the appropriate Dimension in both the primary and secondary, and edit the relationships so that is a linking field between the two data sources. I hadn't done this before, so I set it up in the attached. so you can see it at work.