cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bartvd
Frequent Visitor

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!

1 ACCEPTED SOLUTION
AllisonKennedy
Super User III
Super User III

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


?? Check out my March Madness Report??


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

View solution in original post

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

hi  @bartvd 

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

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

Thank you for answer, it ahs pushed me into the correct solution!

AllisonKennedy
Super User III
Super User III

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


?? Check out my March Madness Report??


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

View solution in original post

Yes!

 

This is perfect! 

I added some extra code for removing empty results from the list.

Thank you very much!

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.