Aggregating time data type in Power BI

Question

How can I make Power BI to show sum of column of time data type?

Scenario: table with columns Phase (text) and Duration (SQL Server time data type, also recognized in Power BI like time). When I add bar chart with Phase as axis and Duration as value, I only have option to show Count and Count Distinct for Duration, no SUM.

Additional clarification after adding DurationMeasure:

Table TBL1 in PowerBI has column Duration (Data type Time).
Measure DurationMeasure is added in TBL1 with formula shown below.
PowerBI does not allow me to add this measure to bar chart to Values property (see image below). Cannot drag-n-drop on Values property. If I just check this measure, it goes to Tooltip property of visual instead of Values property like all other measures. Maybe because it is string data, but I don't know how to format it.

Here is DurationMeasure definition (as in link given by Karl Anka):

    DurationMeasure = 
// 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 = SUM(TBL1[DurationSeconds])
// 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 ) ) )
    )

Thanks in advance

We need more information: the Time data type is for the time of day - technet.microsoft.com/en-us/library/bb677243(v=sql.105).aspx but your name is for duration. Duration implies a start and end time. Which do you require? — Taterhead
@Taterhead: column is named Duration and it is of Time data type, meaning that 00:01:02 means that something lasted 1min 2s - I extract this duration from log (text) and convert it to time data type in SQL statement (for readability in result grid in Management Studio). So, yes, you are right that this might not be appropriate use of time data type. It is ok for me to have any other data type, but need to have it aggregated (sum) in powerbi and that aggregated value to be formatted as hh:mm:ss.ms (ms is not mandatory) — Filip Popović

Karl Anka Karl Anka · Accepted Answer · 2018-04-18T11:10:09

If you convert your duration hour to a decimal number it is possible to show the sum. Then the value 1 = 1 day = 24 hour = 1440 minutes = 86400 seconds. So a value of 0.5 means 12 hours. It is a bit of a hassle to display it as hours and minutes in a chart, but this from the Power BI forums shows how its done.

Aggregating time data type in Power BI

1 Answers