Forum Discussion
Measure returns 100% after slicing data
Actually, I spoke too soon. I did not test the slicers & their behaviour properly. AlexisOlson , as I menioned in the other reply, now, the FFF slicer does have desired effect on the line-chart. However, you might have noticed that, there's another slicer called 'Amt'. So this is when I found out the logical bug that's still there in the report - I set the range for the Amt slicer from lowest value to 100,000. In this case, it works fine. But, in addition to that, as soon as I select Standard/Extended category from the FFF slicer, again the values in line-chart don't match with those in the matrix(screenshot below for your reference; sorry abt the poor editing to hide certain fields. unable to reveal all in a public forum). I'm confused quite terribly here.
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 Resultand
% 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. 😐