Duration format MIN:SEC in table

Anth_NC_QN — Tue, 04 Jul 2023 05:58:29 GMT

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 🙏

Re: Duration format MIN:SEC in table

tamerj1 — Tue, 04 Jul 2023 06:22:34 GMT

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.

Re: Duration format MIN:SEC in table

Anth_NC_QN — Tue, 04 Jul 2023 20:28:40 GMT

tamerj1 , spot on !

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

Thank you

topic Re: Duration format MIN:SEC in table in DAX Commands and Tips

Duration format MIN:SEC in table

Re: Duration format MIN:SEC in table

Re: Duration format MIN:SEC in table