Forum Discussion

lherbert501's avatar
lherbert501
Icon for Post Prodigy rankPost Prodigy
11 months ago
Solved

Matrix heatmap with skewed data

Hi,   Could somebody please help with the attached?   I'm stuck on how to conditional format my heatmap matrix, because putting it into percentiles won't necessarily work as in the example the to...
Need Help
  • GeraldGEmerick's avatar
    GeraldGEmerick
    11 months ago

    lherbert501 Perhaps you could create a conditional formatting measure like the following and base your conditional formatting on the field value of this measure:

    Background Conditional Formatting = 
    VAR _T = SELECTCOLUMNS( CROSSJOIN( ALL( 'Time Dim'[Time] ), ALL( 'Date Dim'[DayOfWeekNumber] ) ), "_Time", [Time], "_Day#", [DayOfWeekNumber] )
    VAR _Table = 
        ADDCOLUMNS( 
            _T, 
            "Count", COUNTROWS( FILTER( ALL( 'FACT' ), 'FACT'[CreatedRoundedHour] = [_Time] && WEEKDAY( 'FACT'[CreatedDate], 2 ) = [_Day#] ) )
        )
    VAR _Max = MAXX( _Table, [Count] )
    VAR _Percent = DIVIDE( [Count Measure], _Max )
    VAR _Return = 
        SWITCH( TRUE(),
            _Percent = 0, "#00FF00",
            _Percent > .1, "#FF0000"
        )
RETURN _Return
GeraldGEmerick's avatar
GeraldGEmerick
Icon for Memorable Member rankMemorable Member
11 months ago

lherbert501 You could replace the 0 with the minimum. That would be just like the _Max variable only use MINX. The .1 (10%) is a threshold and can be whatever you want. The calculation normalizes the data to a percent of the highest value that it sees. Thus, the color red is for any number that is at least 10% of the highest number. But you can pick whatever threshold you want.