Calculated Field to Conditional Formatting

[Anonymous]

Hey All,

So I am trying to build a calculated field to show this :

For some of the rows I need if the actual is higher then budget the it will show green but also for some of them if actual is higher the  show red. any help would be greatly apprectiated

[v-lid-msft]

Hi @Anonymous ,

First of all, we need to group the rows and define the color you want to show.

Then we create the corol measure and use it in Condition Format.

ColorMeasure =
IF (
    SUM ( 'Table'[Actual] ) > SUM ( 'Table'[Budget] ),
    SWITCH ( MAX ( 'Table'[Color] ), "Green", "#00FF00", "Red", "#FF0000" )
)

Best regards,

[Anonymous]

Hey @v-lid-msft ,  So the reasoning behind why some are green and some are red is this: the first two rows are if actual > budget= green else red and the next two rows if Actual > budget = red else green. Would that be at all possible? Thanks!

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

If the matrix rows are sorted in ascending order by the actual sum, we can change the color measure to following:

ColorMeasure = 
VAR t =
    ADDCOLUMNS (
        GROUPBY ( ALLSELECTED ( 'Table' ), [Row] ),
        "TotalActualSum", CALCULATE ( SUM ( 'Table'[Actual] ) )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Row] )
            IN SELECTCOLUMNS ( TOPN ( 2, t, [TotalActualSum], ASC ), "RowName", [Row] ),
        IF ( SUM ( 'Table'[Actual] ) > SUM ( 'Table'[Budget] ), "#00FF00" ),
        IF ( SUM ( 'Table'[Actual] ) > SUM ( 'Table'[Budget] ), "#FF0000" )
    )

Best regards,

[Nathaniel_C]

@v-lid-msft ,

Awesome! Like Magic...
Nathaniel

[Nathaniel_C]

@Anonymous ,
Why would you want that?
Nathaniel