Forum Discussion
Measure returns 100% after slicing data
Super UserI suspect it's a similar problem but with the Amount slicer instead of the FFF slicer. Maybe you want to remove all filters on the whole table with ALL ( 'Tmp' ) rather than just on specific columns like ALL ( 'Tmp'[FFF] )?
I don't know exactly how you expect each slicer to interact, so I can't give a definitive answer.
Ok. Let me shed some light on some specific details. So there are three slicers on the page - FFF, Region and Amount. Out of these 3, Program Type & Amount come from the dataset in DirectQuery mode (mentioned in the question) AND Region comes from a dimension table which has a relationship with the dataset (one-to-many, single direction). Now, all 3 slicers have effect on the line-chart but only two of them (Region & Amount) will have effect on the matrix (as the matrix already shows grouped values for each Program Type). The current state of the page & current definitions of measures can be seen below:
[Link] Sorry, I had to post this link instead of actually pasting the image. Stupid website was giving me error abt HTML.
If you'll examine closely, you'll see that the line-chart has 3 slicers acting on it & the percentages in the line-chart match with those of matrix(highlighted in red outline). But, what I want is that the Total % values should always add up to 100% in the matrix. So, in order to achieve this, in the values section of the matrix, I select Show value as % of Column Total. After doing this, I get following percentages:
[Link]
So, now, again, the values in the two visuals don't match. This is what I'm trying to fix. The computation that's done when showing percentages out of column total, I guess I have to do the same for each month and for each Program Type in the line chart. That is what I'm unable to figure out. Pasted below are the current measure definitions I have:
% out of Total = // this one's being used in Matrix
var max_prog_type = MAX( 'Tmp'[FFF] )
var max_yyyy_mm = MAX( 'Tmp'[YYYY_MM] )
var numerator = CALCULATE( SUM( 'Tmp'[Amt] ) )
var denominator = CALCULATE( SUM( 'Tmp'[Amt] ),
FILTER( ALL( 'Tmp' ), 'Tmp'[YYYY_MM] = max_yyyy_mm ) )
var Result = DIVIDE( numerator, denominator )
Return Result
and
% out of Total (for Line chart) = // this one I created for Line-chart
var max_prog_type = MAX( 'Tmp'[FFF] )
var max_yyyy_mm = MAX( 'Tmp'[YYYY_MM] )
var numerator = CALCULATE( SUM( 'Tmp'[Amt] ) )
var denominator = CALCULATE( SUM( 'Tmp'[Amt] ),
FILTER( ALL( 'Tmp' ), 'Tmp'[YYYY_MM] = max_yyyy_mm ) )
var Result = DIVIDE( numerator, denominator )
Return (Result)
So, essentially, both measures have same definition but I need them to show the same value in both
places too. Hope these details help understand the problem better.
P.S. : AlexisOlson , I appreciate you reading through long posts/replies and taking the time out to
respond, man. Much obliged. 🙂
- AlexisOlson
Given 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.