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 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

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) ?

5 Replies

  • danextian's avatar
    danextian
    Super User
    5 months ago

    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.

  • 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

  • F_Reh's avatar
    F_Reh
    Post Patron
    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) ?

  • v-tejrama's avatar
    v-tejrama
    Community Support
    5 months ago

    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's avatar
      v-tejrama
      Community Support
      5 months ago

      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.