Table Heatmap with Legends

[Anonymous]

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.

[Icey]

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]

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]

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.

[Anonymous]

Hi @Icey

Looks like i can remove the legend if i cannot show the ranges correctly.

My HeatMap is built using Matrix with conditional background and fonts which works.

FactIncident table: 1 row = 1 Incident count with other colums like like IncidentHR and IncidentDay etc..  Hence my HeatMap has 24 rows showing 12am thru 11pm (IncidentHR) and 7 columns for Sun thru Sat (IncidetnDay).

Depending on FY or Month selected, it is possible, some Hours don't have Incidents and then my HeatMap would not show all 24 row of Hours.

Any advise what i can do so the HeatMap will always show 24 rows and 7 columns - even if there is no data for it?  Thank you in advance.