Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Show Duration Based on Time Spent

I am using an Excel table that has a Time Spent column formatted as Custom h:mm:ss.    When I pull this into PowerBI, and I try to show the total duration using the Card visual, I get the following r...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  Anonymous  ,

    You can use measure.

    The calculated column will not change according to the selection of the slicer.

    1. Create measure.

    SumTime_meausre =
VAR TotalSeconds =
    SUMX(
        'IT Productivity Measurements',
        HOUR( 'IT Productivity Measurements'[Time Spent] ) * 3600
            + MINUTE( 'IT Productivity Measurements'[Time Spent] ) * 60
            + SECOND( 'IT Productivity Measurements'[Time Spent] )
    )
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)

    2. Result：

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi  Anonymous ,

Here are the steps you can follow：

1. Create meausre.

datediff = DATEDIFF(0,MAX('Table'[Time Spent]),SECOND)
sum =
SUMX(ALL('Table'),[datediff])

2. Result：

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.