Line-by-Line conditional formatting for Heatmap (Matrix) possible ?

[F_Reh]

Good Morning,

I have a current heatmap (Matrix) like this:

Is it possible to have each line having its own conditional formatting based on a set period. In other words, colours that indicate unusual activity? i.e. if a Category suddenly has higher/lower values than its own average of the past 1 year ?

Regards

Kind Regards

[v-tejrama]

Hi @F_Reh ,

Thank you @danextian  for the response provided!

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you.

[v-tejrama]

Hi @F_Reh ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.

[F_Reh · Accepted solution]

I appreciate these solutions - Can I confirm whether either calculate the specific averages for each individual Category (X1,X2,X3) which is what I am seeking, or is it the average for the entire dataset (which is not quite what I am seeking)..

Also, just to clarify the Heatmap has raw figures i.e numerics only (Not percentages).....

Or could it be better to use an SPC Chart graph - rather than the present Heat map matrix ? An SPC Chart graph like this:

And create a single-option slicer to determine which of the Categories (X1,X2,X3) is being looked at at each time (in the proposed SPC Chart graph) ?

[cengizhanarslan]

Step 1) Create a 12-Month Average Measure 

Avg 12M =
CALCULATE(
    AVERAGE ( 'Fact'[Value] ),
    DATESINPERIOD ( 'Date'[Date], MAX('Date'[Date]), -12, MONTH )
)

Step 2) Measure for Deviation

Deviation % =
DIVIDE(
    [Value] - [Avg 12M],
    [Avg 12M]
)

Step 3) Create a Color Measure

Heatmap Color =
VAR _dev = [Deviation %]
RETURN
SWITCH(
    TRUE(),
    _dev > 0.3, "#008000",      -- strong positive
    _dev > 0.1, "#90EE90",      -- mild positive
    _dev < -0.3, "#FF0000",     -- strong negative
    _dev < -0.1, "#FFA07A",     -- mild negative
    "#FFFFFF"
)

 

Step 4) Apply Conditional Formatting

1. Select Matrix

2. Conditional formatting → Background color

3. Format by → Field value

4. Based on field → Heatmap Color

[danextian]

Hi @F_Reh 

Create a measure that returns a color value based on conditions. Example:

Fill Color 1 Yr Avg =
VAR _curr = [Total Revenue]
VAR _avg1Y =
    CALCULATE (
        AVERAGEX ( VALUES ( Dates[Month and Year] ), [Total Revenue] ),
        DATESINPERIOD ( Dates[Date], MAX ( Dates[Date] ), -1, YEAR ),
        REMOVEFILTERS ( Dates )
    )
VAR _pctDev =
    DIVIDE ( _curr - _avg1Y, _avg1Y )
VAR _color =
    SWITCH (
        TRUE (),
        ISBLANK ( _avg1Y ), "#D3D3D3",
        -- soft gray (no history)
        _pctDev > 0.1, "#FFB3B3",
        -- pastel red (spike)
        _pctDev < -0.1, "#B3D9FF",
        -- pastel blue (drop)
        "#CFCFCF" -- neutral pastel gray
    )
RETURN
    _color

Please see the attached pbix.