Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User

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

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, 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  @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

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.
Anonymous
Not applicable

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

AllisonKennedy
Super User
Super User

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

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Yes!

 

This is perfect! 

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

Thank you very much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.