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

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.