Forum Discussion

KirstyMacKinlay's avatar
KirstyMacKinlay
New Member
7 years ago
Solved

Format for reporting time spent

Hi   I have a report pulled from a spreadsheet which lists time spent by users in a decimal format.  i.e. 0.08 = 5mins This means that the time spent report displays in decimal, but this does not ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, KirstyMacKinlay

    You could try this formula as below:

    Measure = var h=INT([SUMTIME]/60) return
h&"h"&([SUMTIME]-h*60)&"minte"

    or

    Measure 2 =
VAR h =
    INT ( [SUMTIME] / 60 )
RETURN
    IF (
        ( [SUMTIME]
            - h * 60 )
            = 0,
        h & "h",
        h & "h"
            & ( [SUMTIME]
            - h * 60 )
            & "minte"
    )

    Note: SUMTIME = CALCULATE(SUM(Table2[time spent]))

    Result:

     

    Best Regards,
    Lin

     

     

Stachu's avatar
Stachu
Community Champion
7 years ago

you can use FORMAT with calculating number if minutes by multiplying the decimal value by 60:

Measure = FORMAT([Value],INT([Value]) & ":" & FORMAT(ROUND([Value]*60,0),"00"))

or something like this

Measure = FORMAT([Value],INT([Value]) & "h " & FORMAT(ROUND([Value]*60,0),"00m"))

 

KirstyMacKinlay's avatar
KirstyMacKinlay
New Member
7 years ago

Thanks Stachu

 

This Measure didn't give me the caluclation that I was looking for.  I just got an error message.

I did manage to get the time converted to a number of minutes. 

 

However, my customer needs the matrix report to show the time as 5hr, not 300min

Or 6hr 56min, not 416min

 

Is there a way of formatting the matrix sum to show the time in this way, rather than in a single unit of time?

 

Thanks

  • Stachu's avatar
    Stachu
    Community Champion
    7 years ago

    can you share the syntax you used in the end? 


    the second measure I posted should work for you, as long as [Value] is the measure where the aggregations is happening and [Measure] is just used to present it differently