Matrix Subtotals - Making them work

[jure_rak]

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?

[Greg_Deckler]

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

[jure_rak]

Sorry, it didn't work.

[Greg_Deckler · Accepted solution]

@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

[jure_rak]

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

[jure_rak]

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