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

[F_Reh]

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.