Forum Discussion

lherbert501's avatar
lherbert501
Icon for Post Prodigy rankPost Prodigy
1 year 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 top quarter would be about 6 which skews the data. I also can't just use basic conditional formatting because it doesn't do it over both dimensions in the matrix. (date table and time table).

 

Is there a method anybody would recomend to get around this, maybe log scaling?

 

I ideally want roughly whats in the image below it in the pbix, but I don't know what logic you would use to specify the highest numbers that you see with your eyes being red and lowest green and somewhere in the middle, yellow and orange when the data is like this?

 

Thanks

 

Dummy Data.pbix

Need Help
  • GeraldGEmerick's avatar
    GeraldGEmerick
    1 year 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

6 Replies

  • GeraldGEmerick's avatar
    GeraldGEmerick
    Icon for Memorable Member rankMemorable Member
    1 year 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
    • lherbert501's avatar
      lherbert501
      Icon for Post Prodigy rankPost Prodigy
      1 year ago

      Hi GeraldGEmerick 

       

      Thankyou for taking the time to reply.

       

      Would this method work if e.g. next year the data went the other way and there happened to be a lot higher entries and not so many zeros?

       

      I see it relies on the .1, would this still be applicable?

       

      Also, If I wanted 3 colours with an amber present, how would this work against the percentage?

       

      Thanks

      • GeraldGEmerick's avatar
        GeraldGEmerick
        Icon for Memorable Member rankMemorable Member
        1 year 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi lherbert501 ,
    Thanks for reaching out to the Microsoft fabric community forum.

    Select conditional formatting by expanding count measure

     


    From there change the Format style to rules and then add the below rules to it

     

     

    You will be able to get this particular output

     


    Attaching the .pbix for reference, I hope the result is as per your needs.

    I hope this information helps. Please do let us know if you have any further queries.
    Thank you

    • lherbert501's avatar
      lherbert501
      Icon for Post Prodigy rankPost Prodigy
      1 year ago

      Hi Anonymous ,

       

      Thanks for this. I don't think this static method will work as if the data changes moving forward it wont move with it. It needs to have a dynamic element but still work with data that isnt ideal (like in the image).

       

      I also need to introduce an amber option. How would you generally expect to view my example color wise?

       

      I put the green and red as rough examples as on the eye these are what you would expect to be at the bottom and top end of the scale.

       

      Thanks