I haven't been able to figure out how to get the 'Grand Total' to show the median value. In the workbook, I have a sample data with the following hierarchy (Major Category -> Category -> Sub category -> Test). Attached is the workbook for reference.
In the measure I have 'Result'. What I am trying to do is to show the median value as a single value or in the 'Grand Total' based on the selection from dimensions 'Category' or 'Test'. In the workbook, I created an LOD calculation ('Fixed LOD Median') but it did not return the desired results when everything is selected or when only a subset of 'Category' is selected.
For example, in the workbook, I have a filter to filter for values from 0 - 50,000. And the expected median value should be 30,420. But the Grand Total in Tableau is returning 31,020. I'm not sure what is wrong with the LOD for it to not return the desired result of 30,420. Any help would be greatly appreciated!
Note: the median value can be a single value on a sheet. It's not a must to be in Grand Total.
Hi,
I believe you want 'median of average by test' (not by category).
In that case, LOD should be MEDIAN ({FIXED [Test]:AVG([result])}).
Please see the 2nd sheet to confirm if that is what you want. The result becomes 30,420 (third column).
I just wondered why the same row was always repeated 10 times in your sample data though...
