Displaying Durations in PowerBI: A Client Case Study
As a data visualization consultant, I often encounter situations where clients’ expectations clash with what is technically possible or correct. In these cases, I need to find a way to extract what they need from what they say they want and to make sure I’m displaying their data clearly and accurately.
Recently, I faced a challenge when a client asked to see a KPI for the total number of minutes per year that their customers experienced interruptions to service in a duration format: ‘mm:ss’. The client wanted this included in a Power BI dashboard that I was developing.
The Problem
The data I was using had individual interruptions to service logged as rows in a spreadsheet, with their duration in a number of hours. When I looked at legacy reporting and their own measures catalogue, which said the formula is (duration in hours / 24), I was confused. I discovered that the historical reporting was done using Excel, with the (hours / 24) converted to Datetime, so it’s actually a Time where the duration is the difference from 01 Jan 1900 00:00:00! This is a common issue where legacy reporting has not been updated to reflect evolving technologies, or has been carried out by new data analysts who try to manipulate software to work for their data, instead of cleansing data to work for their software.
Had I designed this metric I would have calculated duration as a number of minutes, used that number to perform aggregations at the year level, and only converted to ‘mm:ss’ at the point where I display the data to the end-user. However, I needed to replicate their measure in Power BI, without any manual operations in Excel. They also wanted a bar chart and some other key measures to be displayed alongside this KPI.
The Solution
To solve this problem, I created a calculated field for the duration in minutes. I had a separate table which just had Fiscal Year and Total Customers which was linked to my main data table by Fiscal Year. I converted the Hour Duration to minutes by multiplying by 60, and then divided the Minute Duration by Total Customers. This resulted in a table that showed the duration in minutes per customer.
Now that I had the duration in minutes per customer, I needed a KPI card to display the duration in minutes. I was stumped on how to do this in DAX since I am a Tableau specialist and the client uses only Power BI. After a little back and forth, I arrived at the following formula, which I will share in case it might be useful in your own projects:
MinsPerCustomerFormatted =
VAR DecimalMinutes = [MinsPerCustomer]
VAR TotalSeconds = ROUND(DecimalMinutes * 60, 0)
VAR Minutes = QUOTIENT(TotalSeconds, 60)
VAR Seconds = MOD(TotalSeconds, 60)
RETURN if(Minutes + Seconds > 0, FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00"))This formula converts your minutes to a total number of seconds, and then uses the QUOTIENT() function to get the number of minutes and the MOD() function to get the remainder (the seconds). It then returns the numbers formatted in the ‘mm:ss’ display format. I also modified it with an IF() statement because in this case I’m using a separate calendar table, and my table would otherwise display ‘ : ‘ for years where there was no data.
To make the data more user-friendly, I formatted the Minute Duration with one decimal point to avoid confusing the end user, who might assume the numbers after the decimal were the number of seconds rather than the portion of a minute.
Finally, the client said they wanted a KPI card and a bar chart to visualize this. For the bar chart, they mentioned the importance of seeing which months had the greatest contribution and being able to visualize the running total.
To meet these requirements, I decided on a few metrics to include in the KPI card based on our conversations, along with the headline with the main number. I used a running total line chart with the X-axis set to Month, and the Y-axis set to Duration in Minutes. For the bars, I set them to show what percentage each month contributed to the year total. This allowed the client to see which months had the greatest contribution and visualize the running total as it increased, as the idea would be to check back frequently during the year to identify where spikes occurred. I also added filters that could be used to show different geographic service areas.
The final result was a dashboard that met the client’s needs and provided valuable insights. Although the original request seemed strange, it was what the client wanted and they were happy with the results.
From this experience, I learned several important lessons. Firstly, you have to make your data cleansing work for your software, not the other way around. Secondly, it’s important to update metrics as technology develops, or you risk ending up with legacy reporting which is confusing and not easily replicated — I spoke to several people within the client company who did not understand this measure either! Finally: the client knows what they want but not always what they need — it’s my job to find a balance!
In conclusion, as a data visualization consultant, it’s essential to have the technical skills to handle various data visualization challenges; However, it’s equally important to have the communication skills to understand clients’ requirements and to provide them with the insights they need. By doing so, we can create data visualizations that not only look great but also provide value to our clients. I fed back my recommendations for updating this KPI and the client was grateful that they not only got what they asked for but also got insights into how to improve their internal processes.
