How to remove subtotal in Matrix for specific level

[kala2]

I want to remove subtotal for specific rows in matrix. I have a conditionall rule which change the font color if value is greater than 20 but it highlights the totals in the upper level of skill matrix.

is there any way to remove total from Year and months and keep only the total for the of the person level? With the options off when i toggle the year the total is removed correclty but when i close the level the total is appearing again.

[v-henryk-mstf]

Hi @kala2 ,

I think you can create a measure and use HASONEVALUE to make conditional judgments to achieve the results you expect. The reference is as follows:

M =
IF (
    HASONEVALUE ( 'Table'[Month] ),
    MAX ( 'Table'[Value] ),
    CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Year] ) )
)

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kala2]

hi @v-henryk-mstf , thanks for the info!
I have applied your measure. I made some modifications in your measure. I changed the MAX to SUM since i want the sum of all the values. Below is the changed measure:

MeasureTest = IF (

HASONEVALUE ( 'Auswertung_Person (2)'[Date].[Month] ),

SUM ( 'Auswertung_Person (2)'[Values] ),

CALCULATE ( SUM ( 'Auswertung_Person (2)'[Values] ), ALLEXCEPT ( 'Auswertung_Person (2)', 'Auswertung_Person (2)'[Date].[Year] ) )

)
The problem still persists since it highlights the months and the year as a red color.

[v-henryk-mstf · Accepted solution]

Hi @kala2 ,

After replacing the created measure with the value in the matrix, you need to set the conditional judgment in the conditional formatting to be effective.

just like:

 If there are still problems, please provide a test data model (delete sensitive information), otherwise no further testing can be done if only screenshots are provided.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@kala2 , You are looking at the correct option. if you switch off-year and save visual on that it should work.

[kala2]

@amitchandak I have year switch off but it still showing the subtotal of the year when closed