topic Re: Matrix Subtotals - Making them work in Desktop

Matrix Subtotals - Making them work

jure_rak — Thu, 11 Nov 2021 09:05:54 GMT

Hi,

I'm tiding up the matrix visual I've got so that the subtotals work. I don't understand the following.

First see the screenshot:

My original measure is "Var Price CurrF NewB" and the subtotals are wrong there. The "Measure" is a testing measure that works. The syntax of the "Measure" is:

Measure = SUMX( SUMMARIZE( MAPPING_SEGMENTS,MAPPING_SEGMENTS[Level4],"Total",[Var Price CurrF NewB]), [Var Price CurrF NewB])

Having this tested, I thought I would just adjust the "Var Price CurrF NewB" measure to make it work with the help of VAR function:

Var Price CurrF NewB = VAR XYZ = IF( SUM( MAPPING_SEGMENTS[Bridge] ) = 0, 0, IF( OR( ISBLANK( CALCULATE( SUM('CROP QUANTITIES F21 B22'[2021] ), MAPPING_HARVEST[Harvest] = "Harvest year 2021" ) ), ISBLANK( CALCULATE( SUM('CROP QUANTITIES F21 B22'[2021] ), MAPPING_HARVEST[Harvest] = "Harvest year 2022" ) ) ), 0, CALCULATE( SUM('CROP QUANTITIES F21 B22'[Production] ), MAPPING_HARVEST[Harvest] = "Harvest year 2021" ) * ([Price B22] - [Price Curr Fcst] ) / 1000 ) ) RETURN IF(HASONEVALUE(MAPPING_SEGMENTS[Level4]), XYZ, SUMX(SUMMARIZE(MAPPING_SEGMENTS,MAPPING_SEGMENTS[Level4],"TOTAL",XYZ),XYZ))

The idea being in the RETURN clause:

- If the segment-level4 is filtered, just return the value for that particular segment,

- If the entity level is filtered, do the SUMX ...., which was tested in the "Measure"

The trouble is that this adjustment of the Var Price CurrF NewB measure doesn't work, the individual values for level4 are okay, but the subtotals are now completely crazy.

See below:

What am I missing?

Re: Matrix Subtotals - Making them work

Greg_Deckler — Thu, 11 Nov 2021 09:10:34 GMT

jure_rak I think your last line needs to be:

RETURN IF(HASONEVALUE(MAPPING_SEGMENTS[Level4]), XYZ, SUMX(SUMMARIZE(MAPPING_SEGMENTS,MAPPING_SEGMENTS[Level4],"TOTAL",XYZ),[TOTAL]))

Re: Matrix Subtotals - Making them work

jure_rak — Thu, 11 Nov 2021 11:00:38 GMT

Sorry, it didn't work.

Re: Matrix Subtotals - Making them work

Greg_Deckler — Thu, 11 Nov 2021 11:18:51 GMT

jure_rak So, taking a closer look at this, it is your VAR that is the problem. A VAR is not a variable, it is a constant and is only evaluated once. This is why I keep things separate in my article on measure totals because otherwise you end up duplicating a bunch of code.

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Re: Matrix Subtotals - Making them work

jure_rak — Thu, 11 Nov 2021 12:11:01 GMT

Thanks, I will read it and will come back to you if I still struggle.

Re: Matrix Subtotals - Making them work

jure_rak — Thu, 11 Nov 2021 12:19:46 GMT

Okay, it's this bit:

"Is it annoying to have to create multiple measures and specifically tailor them to each individual visual? Yes, yes it is."

I see now there is no othe way then multiply measure ...