Forum Discussion

F_Reh's avatar
F_Reh
Post Patron
5 months ago
Solved

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

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 ...
Need Help
Show and Tell
Tips and Tricks
  • F_Reh's avatar
    F_Reh
    5 months ago

    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's avatar
cengizhanarslan
Super User
5 months ago

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