Conditionnal formatting in a calculation group

[Kev59]

Dear all

I'm just starting to play with the calculation groups and I still have an issue....

I would like to apply a conditionnal formatting directly in the calculation group item.

I have the following code:

M1 = 
VAR _CurrentYear =
CALCULATE(
	SELECTEDMEASURE(),
    'Calendar Fiscal'[TFS Year] = year(today()),
    'Calendar Fiscal'[TFS Month Number ]=1
)
VAR _LastYear =
CALCULATE(
	SELECTEDMEASURE(),
    'Calendar Fiscal'[TFS Year] = year(today())-1,
    'Calendar Fiscal'[TFS Month Number ]=1
)
VAR _Growth = DIVIDE(_CurrentYear - _LastYear,_LastYear)

VAR _Color = IF(_Growth < 0,"#f71111", "#32a852")

VAR _Format = SELECTEDMEASUREFORMATSTRING()

return
    IF (NOT(ISBLANK(_CurrentYear)),
    FORMAT(_CurrentYear, _Format) & UNICHAR(10)
    & FORMAT(_LastYear, _Format) & UNICHAR(10)
    & IF (_Growth <> 0,
        FORMAT(_Growth, "↑0%;↓0%;")
        )
    )
            

I would like to apply the good color to the _currentYear result based on the _color result. Is it possible to do that directly in the code?

Many thanks in advance for your help

[Kev59]

Hi again,

Just tested.... error occured

For information my matrix looks like this:

The value field is based on a segment selection (Revenue or quantity), the rows field is also based on a segment selection.

To try to apply the color change, I have modified the "font color" in the "cell elements" section of the matrix...

What I'm doing wrong?

[Kev59]

Hi @v-heq-msft 

So, if I well understood, I need to create a measure (not in group calculation) like your example above and like it to value filed of my matrix.

The problematic I will have is I have 13 items on my calculation group (1 per month and another for the YTD), so I will need to create 13 differents measures to color each values of each columns (13) of my matrix.... not really optimized 😉

right?

I going to try an keep you posted

Thanks for your help

[v-heq-msft]

Hi @Kev59 ,
Based on your description, you want to apply a color to the _CurrentYear result based on the _Color variable. Unfortunately, DAX does not support conditional formatting directly in the metric itself. However, you can do so by using the conditional formatting options in Power BI.

ColorMeasure = 
VAR _CurrentYear =
    CALCULATE(
        SELECTEDMEASURE(),
        'Calendar Fiscal'[TFS Year] = YEAR(TODAY()),
        'Calendar Fiscal'[TFS Month Number] = 1
    )
VAR _LastYear =
    CALCULATE(
        SELECTEDMEASURE(),
        'Calendar Fiscal'[TFS Year] = YEAR(TODAY()) - 1,
        'Calendar Fiscal'[TFS Month Number] = 1
    )
VAR _Growth = DIVIDE(_CurrentYear - _LastYear, _LastYear)
RETURN
    IF(_Growth < 0, "#f71111", "#32a852")

Go to the Format pane and find the Data Color section.
Click the “fx” button next to the Color option.
Select Field Values and then select the ColorMeasure you created.

Apply conditional table formatting in Power BI - Power BI | Microsoft Learn

Best regards,
Albert He

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