Background color for calculation group item that doesn't use SELECTEDMEASURE()

[Anonymous]

Hi,

I made a matrix that uses a calculation group [Name] as a column and 1 measure 'Actuals' in the Values.

I wanted to set the background color of the YTD columns and the bottom (a total via ‘P&L row'[Id]) row gray and for 'Actuals YTD' or overall it works but for 'Budget YTD' and 'Actuals vs Budget' not.

The big difference is that the 'Budget YTD' and 'Actuals vs Budget' items don't use SELECTEDMEASURE() so i think this has an impact on it but i can't figure out why or how to make this work.

The background color measure, used in Format style: Rules (if value = 1 then gray):

CALCULATE(
    IF( 
        SELECTEDVALUE('Calculation Group'[Name]) = "Actuals YTD" || 
        SELECTEDVALUE('Calculation Group'[Name]) = "Budget YTD" ||
        SELECTEDVALUE('P&L row'[Id]) = "111111110",
        1,
        0
    )
)

Inside the calculation group, the item 'Actuals YTD' has the following code:

CALCULATE( TOTALYTD( SELECTEDMEASURE(), Kalender[Datum] ))

 Item 'Budget YTD':

CALCULATE( [Budget YTD] )

 Item 'Actuals vs Budget':

CALCULATE( CALCULATE(SELECTEDMEASURE(), 'Calculation Group'[Name] = "Actuals YTD") - CALCULATE(SELECTEDMEASURE(), 'Calculation Group'[Name] = "Budget YTD") )

Any ideas?

Thanks in advance,

Erwin

[rajendraongole1]

Hi @Anonymous  -  Conditional formatting specifically to the total row, you might need an additional check in your DAX measure. Power BI doesn't always handle total rows in the same way as other rows, so you might need to add a check for the total row explicitly.

Color Formatting =
SWITCH(
TRUE(),
ISINSCOPE('P&L row'[Id]) = FALSE, "#D3D3D3", -- Gray for total row
SELECTEDVALUE('Calculation Group'[Name]) = "Actuals YTD", "#D3D3D3", -- Light Gray
SELECTEDVALUE('Calculation Group'[Name]) = "Budget YTD", "#D3D3D3", -- Light Gray
SELECTEDVALUE('Calculation Group'[Name]) = "Actuals vs Budget", "#D3D3D3", -- Light Gray
"White" -- Default color
)

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

[Anonymous]

Hi, thanks for the tip!
The bottom row is not the matrix row total but calculated on 'P&L row' and closed up by +/-.
The result with your 'Color Formatting':