Forum Discussion
Matrix visual with time field
Hi,
I am stuck with my Matrix.
What works for me now :
I have timestamps.
Those timestamps are for a specific date, and linked to a specific Service Order.
There are different types of timestamps (travel time, waiting time,...)
So for each workday , i want to show each SO.
And for those so's, show in different columns the available timestamps.
and then for each total (day level and total level) the sum.
My timestamps have mintues to show how long they took.
When I have that column as whole number, it works perfect, the totals work and give a clear result.
However, Business want to show the data as Time (hh:mm).
When changing the field to time, I cant have a sum, only earliest , latest and count.
Is there another way or visual too give me the correct result?
Thanks in advance!
- You must have the value as a number to be able to summarize it with SUM. If you want, you can try looking into custom formatting to get it to display how you would like. I don't think you can do custom HH:MM formatting with built in Power BI, but you can create a measure that does this for you:
HourMinFormat =
VAR _Minutes = MOD(SUM(table[Minutes]), 60)
VAR _Hours = QUOTIENT(SUM(table[Minutes]), 60)
VAR _MM = IF(_Minutes < 10, "0" & _Minutes, _Minutes)
RETURN
_Hours & ":" & _MM
4 Replies
- You must have the value as a number to be able to summarize it with SUM. If you want, you can try looking into custom formatting to get it to display how you would like. I don't think you can do custom HH:MM formatting with built in Power BI, but you can create a measure that does this for you:
HourMinFormat =
VAR _Minutes = MOD(SUM(table[Minutes]), 60)
VAR _Hours = QUOTIENT(SUM(table[Minutes]), 60)
VAR _MM = IF(_Minutes < 10, "0" & _Minutes, _Minutes)
RETURN
_Hours & ":" & _MM hi Anonymous
you don't need to change the field to time, just use a measure to format the result as Time (hh:mm).
https://docs.microsoft.com/en-us/dax/format-function-dax
and your could also format the result as custom format.
if you still have the problem, please share a simple sample pbix file and your expected output.
Regards,
Lin
