Forum Discussion

manoj_0911's avatar
manoj_0911
Kudo Commander
2 years ago
Solved

Heatmap Formatting - Display Days and Hours (Custom Axis Labels)

Hi everyone,

 

I'm working on a Heatmap in Power BI to visualize total calls offered by day. However, I'm facing an issue with the axis labels.

 

Currently, the rows display sequential numbers (1, 2, 3, ...) representing days, and the columns display numeric hours (0, 1, 2, ..., 23).

 

Instead, I'd like to display the days of the week (Sun, Mon, Tue, ...) in the rows and hours in a 12-hour format (01AM, 02AM, ..., 11PM) in the columns.

 

Here's how I've defined my measures so far:

```
Day_of_Week_Order = SWITCH(
'AGENT_SUBHOUR_V'[Day_of_Week],
"Sunday", 1,
"Monday", 2,
"Tuesday", 3,
"Wednesday", 4,
"Thursday", 5,
"Friday", 6,
"Saturday", 7,
0
)

Hour_of_Day = HOUR('AGENT_SUBHOUR_V'[IXN_SUBHOUR_DATE])
```

Values = Sum of Offered

 

Can anyone advise on how to customize the axis labels to display the desired format (day of week and 12-hour hours)?

 

Thank you in advance for your help!

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi manoj_0911 

    It is the same step as before.

    Create a claulated column

    HourNo = HOUR([IXN_SUBHOUR_DATE])

    Then sort it ascendng, then select the hour column, select sort by HourNo

     

    Best Regards!

    Yolo Zhu

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

8 Replies

  • Zang_Mi's avatar
    Zang_Mi
    Resolver II
    2 years ago

    Hello,

     

    What you need is to use a calculated column or a column from dimension table with the desired format of days and hours in the visual.

     

    Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi manoj_0911 

    As Zang_Mi  mentioned, you need to format the date, you can create two calculated columns

    Weekday = FORMAT([IXN_SUBHOUR_DATE],"DDD")
    Hour = FORMAT([IXN_SUBHOUR_DATE],"hh AM/pm")

    Output

    Best Regards!

    Yolo Zhu

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

     

    • manoj_0911's avatar
      manoj_0911
      Kudo Commander
      2 years ago

      Hi , How to sort the weekdays as like Sun, Mon, Tue, Wed, Thur, Fri and Saturday

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi manoj_0911 

        You can add a calculated column

        WeekNo = WEEKDAY([IXN_SUBHOUR_DATE])

        Then click the weekday column,select sort by 'WeekNo'

         

        Output

        Best Regards!

        Yolo Zhu

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