Duration format MIN:SEC in table

[Anth_NC_QN]

Hi everyone,

English is not my native language but i'll try to be as precise as possible. Sorry if i'm not clear. Please do ask me more details.

I've been trying to reproduce this excel table in power bi desktop :

It's part of a shop performance evaluation based on a X month period. So in 2021 for eg, the average waiting time in minutes before being assisted is 2mn and 45sec which i converted from the decimal number 2,75 by dividing it by 24, and formatting the result.

In power bi, i've loaded only one table which contains the "waiting time" column in decimal numbers :

In order to display the results in a table, i've created this measure for the 2021 column i'm trying to reproduce :

wait time = CALCULATE(AVERAGE(VM_DATA_QUANTI[waiting time]), FILTER(VM_DATA_QUANTI, VM_DATA_QUANTI[Année]="2021"))

Here's the table with the same data i used for the excel table screenshot :

The next step was to try formatting the results in MIN:SEC by completing the above dax formula using "FORMAT" :

wait time =

CALCULATE(

AVERAGE(VM_DATA_QUANTI[waiting time]),

FILTER(VM_DATA_QUANTI, VM_DATA_QUANTI[Année]="2021"),

FORMAT(INT(VM_DATA_QUANTI[waiting time] * 1), "00") & ":" & FORMAT(MOD(VM_DATA_QUANTI[waiting time] * 60, 60), "00")

)

I'm using MOD in the second part of the FORMAT formula to find the remaining seconds once the minutes in whole numbers have been calculated.

As you can imagine, it didn't work lol.

Would you please have a solution on how i can display those decimal numbers in the same format as the excel screenshot ?

Thanks 🙏

[tamerj1 · Accepted solution]

Hi @Anth_NC_QN 
Please try

wait time =
VAR WaitTime =
    CALCULATE (
        AVERAGE ( VM_DATA_QUANTI[waiting time] ),
        FILTER ( VM_DATA_QUANTI, VM_DATA_QUANTI[Année] = "2021" )
    )
VAR TotalSeconds = WaitTime * 60
VAR Seconds =
    MOD ( TotalSeconds, 60 )
VAR Minutes =
    QUOTIENT ( TotalSeconds, 60 )
RETURN
    TIME ( 0, Minutes, Seconds )

You can then choose you preference of the time format type.

[Anth_NC_QN]

@tamerj1 , spot on !

I'm not used to working with variables (VAR) in power bi. I'll get to it.

Thank you