Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Table Heatmap with Legends

First time Power BI (Jan 2020 version) user. I would like to create a table heatmap (and a legend) of IncidentCount group by Day and TimeOfDay as showed in the picture below.

 

 

1) I downloaded the custom Table Heatmap that also displays legend. But the legend is showing numbers with 16 decimals and there is no option to customize so this would not work for me.

 

2) I saw a youtube video that showed me how I can make a table heatup from using the Matrix visual. Works really well but I need to add to the table heatmap a legend (like what's shown in the picture - legend is a screen shot i manually pasted).

FYI: the picture is only a sample report. The report will source measure from DB table direct and the IncidentCount can range 10's - 1000's.

 

Anyone has any suggestion what i can do to add the legend? Very much appreciate any help.  Thank you.

Need Help

4 Replies

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Anonymous ,

     

    How about using Treemap visual to create a custom lengend?

     

    1. Create a Range Table.

    Range Table = 
VAR t1 =
    SELECTCOLUMNS (
        GENERATESERIES ( 0, MAXX ( 'Table', [Count Measure] ), 5 ),
        "Range1", [Value]
    )
VAR t2 =
    SELECTCOLUMNS (
        GENERATESERIES ( 5, MAXX ( 'Table', [Count Measure] ), 5 ),
        "Range2", [Value]
    )
VAR t3 =
    FILTER ( CROSSJOIN ( t1, t2 ), [Range2] - [Range1] = 5 )
VAR t4 =
    SELECTCOLUMNS (
        t3,
        "Range", [Range1] & " - " & [Range2],
        "Order", RANKX ( t3, VALUE ( [Range1] ),, DESC, DENSE )
    )
RETURN
    t4

     

     

    2. Sort [Range] column by [Order] column.

     

    3. Create a Measure.

    1 = 1

     

    4. Create a Treemap visual.

     

    5. Group the Matrix visual and then Treemap visual.

     

    BTW, .pbix file attached.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello Icey,

      I tried your suggestion and I get a Legend 🙂  Thank you.  

      I am not familiar with DAX coding. 

      The max value in the chart is 34.  But the last legend bucket has a range of 25-30.

      I forgot to include in my original posting i have a slicer as well. 

      Can the Range Table (bucket min-max) dynamically show different ranges depending on slicer selected?  The CountMeasure could range 0 to several thousands if all slicer items are selected.

      Curious to understand how do you determine 6 buckets would represent the mix of colors in the Matrix?

       

       

       

      • Icey's avatar
        Icey
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Sorry to reply late. Try to change your Range Table expression like so:

        Range Table =
VAR CountMeasure_ =
    IF (
        MOD ( MAXX ( 'Table', [Count Measure] ), 5 ) = 0,
        MAXX ( 'Table', [Count Measure] ),
        MAXX ( 'Table', [Count Measure] ) + 5
    )
VAR t1 =
    SELECTCOLUMNS ( GENERATESERIES ( 0, CountMeasure_, 5 ), "Range1", [Value] )
VAR t2 =
    SELECTCOLUMNS ( GENERATESERIES ( 5, CountMeasure_, 5 ), "Range2", [Value] )
VAR t3 =
    FILTER ( CROSSJOIN ( t1, t2 ), [Range2] - [Range1] = 5 )
VAR t4 =
    SELECTCOLUMNS (
        t3,
        "Range", [Range1] & " - " & [Range2],
        "Order", RANKX ( t3, VALUE ( [Range1] ),, DESC, DENSE )
    )
RETURN
    t4

         

        This is a bit opportunistic. The colors in the matrix are not exactly according to the legend.

         

         

        Best Regards,

        Icey

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.