-
-
Save jongio/5b8dcf13ab957cecfec3932a42db0e58 to your computer and use it in GitHub Desktop.
| // Originally from: https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486 | |
| Duration = | |
| // Duration formatting | |
| // * @konstatinos 1/25/2016 | |
| // * Given a number of seconds, returns a format of "hh:mm:ss" | |
| // | |
| // We start with a duration in number of seconds | |
| VAR Duration = [Change this value to the name of your column that contains your seconds value] | |
| // There are 3,600 seconds in an hour | |
| VAR Hours = | |
| INT ( Duration / 3600) | |
| // There are 60 seconds in a minute | |
| VAR Minutes = | |
| INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60) | |
| // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours | |
| VAR Seconds = | |
| ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number | |
| // These intermediate variables ensure that we have leading zero's concatenated onto single digits | |
| // Hours with leading zeros | |
| VAR H = | |
| IF ( LEN ( Hours ) = 1, | |
| CONCATENATE ( "0", Hours ), | |
| CONCATENATE ( "", Hours ) | |
| ) | |
| // Minutes with leading zeros | |
| VAR M = | |
| IF ( | |
| LEN ( Minutes ) = 1, | |
| CONCATENATE ( "0", Minutes ), | |
| CONCATENATE ( "", Minutes ) | |
| ) | |
| // Seconds with leading zeros | |
| VAR S = | |
| IF ( | |
| LEN ( Seconds ) = 1, | |
| CONCATENATE ( "0", Seconds ), | |
| CONCATENATE ( "", Seconds ) | |
| ) | |
| // Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss" | |
| RETURN | |
| CONCATENATE ( | |
| H, | |
| CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) ) | |
| ) |
@zvyagsta I think the issue might be you've created 'ACD Time' as a column instead of as a measure. When using it as a measure it still shows up as a Text format for me but it still calculates the column when used in a table:
@zvyagsta I think the issue might be you've created 'ACD Time' as a column instead of as a measure. When using it as a measure it still shows up as a Text format for me but it still calculates the column when used in a table:
I tried to create a measure.
A single value for column 'Duration, sec' in table 'data' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
I tried to create conditions for aggregation SUM, SUMX. I have no result. I'm new at this. What am I doing wrong?
how to sum the concatenated value
use calculate(average...) in a VAR and so on
Could you help me to implement this suggestion? I have a table with some activities and related durations in seconds. I need to calculate the avg of durations and display the value formatted hh_mm:ss in a card. If
how to sum the concatenated value
use calculate(average...) in a VAR and so on
Could you help me to implement this suggestion? I have a table with some activities and related durations in seconds. I need to calculate the avg of durations and display the value formatted hh_mm:ss in a card. If
@EnricaTunesi70, You need to sum the values BEFORE you run it through this measure because the original formula is only used to display the final result as text for visual purposes, not to do calculations.
I have a line chart where I'm trying to show the minutes:seconds in the Y-axis.
When I apply the Duration fields that I created, it only allows me to have a count, which is always 1.
Any ideas?
In the RETURN section of the DAX I wrote this to ensure you don't end up with the colons ("::")
RETURN
IF(
CONCATENATE (
D,
CONCATENATE ( " ", CONCATENATE ( H, CONCATENATE( ":", CONCATENATE (M, CONCATENATE ( ":", S ) ) ) ))) = " ::",
BLANK(),
CONCATENATE (
D,
CONCATENATE ( " ", CONCATENATE ( H, CONCATENATE( ":", CONCATENATE (M, CONCATENATE ( ":", S ) ) ) )))
)