Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have a table with data in time format as below, I would like to show the totals for this in the table. Can someone please let me know how to achieve?
Totals of the "Time" field is not showing up at the bottom
Thanks
Poojitha
Solved! Go to Solution.
Hi @Anonymous ,
For the time type, Power BI does not support sum aggregation. If you use dax to sum the time column, it will run into wrong value.
As a workaround, you can use the following dax to sum HH:MM SS format:
SumTime =
VAR TotalSeconds =
SUMX (
'Table Name',
HOUR ( 'Table Name'[Column] ) * 3600
+ MINUTE ( 'Table Name'[Column] ) * 60
+ SECOND ( 'Table Name'[Column] )
)
VAR Days =
TRUNC ( TotalSeconds / 3600 / 24 )
VAR Hors =
TRUNC ( ( TotalSeconds - Days * 3600 * 24 ) / 3600 )
VAR Mins =
TRUNC ( MOD ( TotalSeconds, 3600 ) / 60 )
VAR Secs =
MOD ( TotalSeconds, 60 )
RETURN
IF ( DAYS = 0, "", IF ( DAYS > 1, DAYS & "days ", Days & "day" ) )
& IF ( Hors < 10, "0" & Hors, Hors ) & ":"
& IF ( Mins < 10, "0" & Mins, Mins ) & ":"
& IF ( Secs < 10, "0" & Secs, Secs )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @Anonymous ,
For the time type, Power BI does not support sum aggregation. If you use dax to sum the time column, it will run into wrong value.
As a workaround, you can use the following dax to sum HH:MM SS format:
SumTime =
VAR TotalSeconds =
SUMX (
'Table Name',
HOUR ( 'Table Name'[Column] ) * 3600
+ MINUTE ( 'Table Name'[Column] ) * 60
+ SECOND ( 'Table Name'[Column] )
)
VAR Days =
TRUNC ( TotalSeconds / 3600 / 24 )
VAR Hors =
TRUNC ( ( TotalSeconds - Days * 3600 * 24 ) / 3600 )
VAR Mins =
TRUNC ( MOD ( TotalSeconds, 3600 ) / 60 )
VAR Secs =
MOD ( TotalSeconds, 60 )
RETURN
IF ( DAYS = 0, "", IF ( DAYS > 1, DAYS & "days ", Days & "day" ) )
& IF ( Hors < 10, "0" & Hors, Hors ) & ":"
& IF ( Mins < 10, "0" & Mins, Mins ) & ":"
& IF ( Secs < 10, "0" & Secs, Secs )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @Anonymous ,
The system has to know what you are trying to do. There is not a built-in function that I am aware of that will just take the time from one column to the next. You will have to create a new column or use an existing column to determine the time frames that you are trying to achieve. If you have a start time and end time you can subtract those and get the duration between them. Or, you can subtract the second row time from the first row time if you are trying to get seconds between them.
All that said, you already have the seconds column in the first column. Why not just divide that by 60 to get the number minutes and/or by another 60 to get the number of hours?
Proud to be a Datanaut!
Private message me for consulting or training needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.