Forum Discussion
Measure returns 100% after slicing data
Super UserGiven that you want the chart to match the matrix and the difference between the two is that the product type slicer does not filter the matrix, then the difference in the measures should be related to that.
What do you get if you use the following for both the line and the matrix (with no Show as % of Column Total adjustments)?
% of Total =
DIVIDE (
SUM ( 'Tmp'[Amt] ),
CALCULATE ( SUM ( 'Tmp'[Amt] ), ALL ( 'Tmp'[FFF] ) )
)Well, I also needed the Amount slicer to have effect on the matrix as well as the line-chart. So, I tweaked the measure you pasted like below:
% of Total =
DIVIDE (
SUM ( 'Tmp'[Amt] ),
CALCULATE ( SUM ( 'Tmp'[Amt] ), ALL ( 'Tmp'[FFF] ), ALL('Tmp'[Amt]) )
)
Now, without the Show as % of Column Total adjustment, the values in I see in both visuals do match. However, this leads to another issue where some of the Totals in the bottom row of the matrix aren't 100%. Pls find a screenshot below for reference:
There something else that I tried out, ONLY when I reset ALL the slicers on the page (Region, the one that's darkened, FFF/Program Type and Amount), the numbers match in visuals AND ALL the % totals become 100 at the bottom row of the matrix. Should I include the columns which are being used for other slicers (Region & the darkened one) too, in the definition of the measure, within ALL() like we did for FFF and Amount ?? 🤔
Edit : Actually, I tried that too. But the same issue persists. Not ALL Totals add upto 100%.
This one's got me scratchin' my head for some time now. 😐
- AlexisOlson
Writing ALL ( 'Tmp'[Amt] ) removes any filtering done by the slicer in the denominator. I don't know if this is actually what you want or not so I've just been shooting in the dark.
The measure I specified would allow the amount slicer to filter both the numerator and denominator (since the filter isn't removed with an ALL). When you add ALL ( 'Tmp'[Amt] ) to the denominator, then your columns will not add up to 100% when the amount is filtered via slicer because the numerator is filtered but not the denominator (because of the ALL).
I'd recommend slowing down and trying to fundamentally understand how the filters and calculations are working rather than trying all kinds of combinations and getting confused about what you're seeing. For each cell in the matrix and point on your lines, figure out exactly what numerator and denominator you expect. In particular, for each different slicer when you are calculating the denominator, do you want to include ALLSELECTED values from the slicer or ALL of the values whether selected or not? For Program Type you've decided on ALL, but it's not clear to me what choice you intend for the other slicers.