Measure returns 100% after slicing data

[theDarkPrince]

Hi all. This one's got me stumped. Following is the dataset that I'm working with (in DirectQuery mode):

I created a couple of measures as below to use in a matrix & a line-chart respectively.

% 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( ALLSELECTED( 'Tmp' ), 'Tmp'[YYYY_MM] = max_yyyy_mm ) )

var Result = DIVIDE( numerator, denominator )

Return Result

% 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( ALLSELECTED( 'Tmp' ), 'Tmp'[YYYY_MM] = max_yyyy_mm ) )

var Result = DIVIDE( numerator, denominator )

Return (Result * 100)

Below are the screenshot of the visuals:

As it can be seen, I have created the measures to show the % of each FFF within each YYYY_MM out of the total for that Month.

When I make a selection in the Region slicer, the measure values changes correctly in both matrix & the line chart.

However, when I select anything in the FFF slicer, the line chart shows all the values as 100% for all months.

Instead of 100 everywhere, it should be showing 5%, 8%, 6%, 11% and so on. My guess is the ALLSELECTED()

being used in second measures definition is causing it. I tried a bunch of things like different modifier functions etc.

But I am not able to get the result I need. Please guys. Any help is appreciated. Many thanks in advance.

P.S. : The dimension table which contains the region field used for slicer, is connected with the data table

mentioned above.

[theDarkPrince]

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.

[AlexisOlson]

I 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.

[theDarkPrince]

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] ) )
)

[theDarkPrince]

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.

[AlexisOlson]

ALLSELECTED includes the filter on FFF. If you don't want that, then you need a denominator that includes all FFF. I don't know if this is exactly what you want but you can try e.g.

var denominator =
    CALCULATE( SUM( 'Tmp'[Amt] ),
        ALL ( 'Tmp'[FFF] ),
        'Tmp'[YYYY_MM] = max_yyyy_mm
    )

[theDarkPrince]

Wow. That did the trick. Thank you so much @AlexisOlson 🤗 . But I still can't quite wrap my mind around the logic. If I want the selection in FFF Slicer to affect Line-chart values, shouldn't I use ALLSELECTED() so that it'd consider ALL the datapoints that are selected using different slicers on the page ? And I get that the measure you pasted does work, however, shouldn't ALL() in there, enforce selection of ALL the categories that are available in FFF column. 🤔

[AlexisOlson]

When you use ALL in a measure, it does not change your slicer selections on the page but does modify them when computing the value it returns. In particular, putting ALL in a measure does not prevent the slicer from filtering what program types display in a visual, it only affects the values for the things that remain displayed after filtering. The visual still only displays the value you've selected with your slicer but the measure computes the percentage for that particular value over not just that particular value but over the ALL of the values (by removing that slicer filter for the calculation).