CODEWITHSUNDEEP
powerbipowerbi-desktop
Filip Popović
Filip Popović

Reputation: 2655

Aggregating time data type in Power BI

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:

bar chart visual

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

Upvotes: 2

Views: 10575

Answers (1)

Karl Anka
Karl Anka

Reputation: 2849

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.

Upvotes: 1

Related Questions