Forum Discussion

jure_rak's avatar
jure_rak
Helper I
4 years ago
Solved

Matrix Subtotals - Making them work

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?

5 Replies