Conditional Formatting Matrix Table by Row by Cell

[Anonymous]

Our customer asked for a daily scorecard view of a dozen targets. Each row represents a unique target.  The screenshot shows the target and the conditional formatting applied.  The ID and description columns are not shown.  This solution is for conditional formatting of a matrix table by row, by cell. 

A SQL Server table was created to store data from the daily batch cycle (INSERT ... SELECT COUNT(1) FROM... statements).

CREATE TABLE dbo.SCORECARD(
SC_DAY DATE,
SC_GOAL_ID INT,
SC_ACTUAL DECIMAL(8,2))

A table was was created in Power BI for ID (1), Description (Widget Tons), Target value (<5000t) for the first three columns of the Matrix table.  This was then joined to the SCORECARD table on SC_GOAL_ID and ID.

DAX was used to hardcode the logic to assign 1, 2 or 3 for low, middle, high (Green, Yellow, Red).

_Format = VAR CondFormat = SELECTEDVALUE('BH_SCORECARD'[SC_GOAL_ID]) RETURN

SWITCH(TRUE(),

CondFormat = 1,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 5000,1,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 10000,2,3)),

CondFormat = 2,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 200,1,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 300,2,3)),

CondFormat = 3,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 200,1,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 300,2,3)),

CondFormat = 4,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 5000,1,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 10000,2,3)),

CondFormat = 5,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 25,1,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 50,2,3)),

CondFormat = 6,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 20,1,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 40,2,3)),

CondFormat = 7,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 20,1,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 40,2,3)),

CondFormat = 8,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 10,1,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 20,2,3)),

CondFormat = 9,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 20,1,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 40,2,3)),

CondFormat = 10,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 20,1,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 40,2,3)),

CondFormat = 11,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 10,1,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 20,2,3)),

CondFormat = 12,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 500,1,If(SUM('BH_SCORECARD'[SC_ACTUAL]) < 1000,2,3))

)

The SC_ACTUAL is placed in the Values card for the Matrix.  Conditional formatting, background is selected.  Format by Rules Based on the DAX formula, _Format.  Add an entry for each number 1, 2 & 3 and assign a color.

Finally, use the Icon "Expand all down one level in the heirarchy" to drill into the report to see the finished matrix table. 

The user also wanted to see a view of the last day of the week.  Added toggle images and actions and filter to show Saturday only view.

HTH, Smitty

[Anonymous]

HI @Anonymous ,

AFAIK, current power bi not contains row/column index. If you want to conditional formatting row/cell color, you need to add condition to check current row contents and compare with if statement conditions.

BTW,  I also try to transform your formula to make it dynamic based on current item and result color string to do color formatting.

_Format =
VAR selected =
    SELECTEDVALUE ( 'BH_SCORECARD'[SC_GOAL_ID] )
VAR _multi =
    IF (
        selected IN { 1, 4 },
        5000,
        IF (
            selected IN { 2, 3 },
            100,
            IF (
                selected IN { 6, 7, 9, 10, 11 },
                20,
                IF ( selected IN { 8, 11 }, 10, IF ( selected = 5, 25 ) )
            )
        )
    )
VAR _offset =
    IF ( selected IN { 2, 3 }, 100, 0 )
RETURN
    IF (
        SUM ( 'BH_SCORECARD'[SC_ACTUAL] ) < 1 * _multi + _offset,
        "Green",
        IF ( SUM ( 'BH_SCORECARD'[SC_ACTUAL] ) < 2 * _multi + _offset, "Yellow", "Red" )
    )

Conditional formatting in tables

Regards,

Xiaoxin Sheng