Power BI DAX, how to create a measure to calculate the sum of time in 00:00 format

1
votes

I need to create a dashboard which can show duration in 00:00 based on given month or year. I managed to use DAX to create a calculated column that can show similar thing in the table. But I'm unable to use the value in that column to display due to they are strings.

How can I create a DAX measure instead of a caculated column to display the sum of duration in 00:00 format and present them in the dashboard?

AHT In MM:SS = 
SUMX('Raw Call', 
VAR M = PATHITEM(SUBSTITUTE('Raw Call'[AHT],":","|"),1)
VAR S = PATHITEM(SUBSTITUTE('Raw Call'[AHT],":","|"),2)
REtURN CONCATENATE(M, CONCATENATE(":", S))
)

enter image description here enter image description here

1
powerbidata-visualizationdaxdata-analysispowerbi-desktop

1 Answers

1
votes

When you need to sum up all the value of minutes and seconds, you will need to convert the string format into number then you can start the calculation, however I am unable to provide a single step solution but would need some helper column to do so:

Multiple steps

First, from the "AHT" column you already created, I will obtain the minutes portion by creating a new column:

minutes = VALUE(LEFT(Sheet1[Time],2))

Next, I will create a new column to store the seconds also:

seconds = VALUE(RIGHT(Sheet1[Time],2))

Finally, I will create a new measure to sum the duration of minutes and seconds with the following formula:

duration = 
var sec = MOD(SUM(Sheet1[seconds]),60)
var minute = TRUNC(DIVIDE(SUM(Sheet1[seconds]),60)) + SUM(Sheet1[minutes])
return
FORMAT(minute,"General Number") & "minutes " & FORMAT(sec,"General Number") & "secs"

Here is my original table with two new columns:

enter image description here

By setting the calculated measure as field, I will get the following scorecard, you may choose other format like "00:00" if you prefer :)

enter image description here

Single step to return the same result using dax formula with measure:

Duration = 
var minutes = SUMX(Sheet1,VALUE(LEFT(Sheet1[Time],2))) + SUMX(Sheet1,TRUNC(DIVIDE(Sheet1[Time],60)))
var seconds = MOD(SUMX(Sheet1,VALUE(RIGHT(Sheet1[Time],2))),60)
return
FORMAT(minutes,"General Number") & " minutes " & seconds & " seconds"