Seconds in SQL Query to be converted to HH:MM format in Power Bi and use Average in Card visual

[Anonymous]

Hi  All,

Some where in Community, I found a DAX code and used it to build one for my requirement . For some reason, I am not able to acheive what I need. 

1. Firstly, I have created a SQL Query which have IncidentID(Unique No), Date , Duration in Secs Columns  .
2. Then I have used below formulae to create Average of duration (HH:MM format). 
3. Then I have used this formulae in Card visualization  . I am either getting First or last Average values as the Calculated column i have created is in text format . I tried changing it to Time format , But it has thrown me an error like - Cannot convert value '32:42:27' of type Text to type Date.

AvgTotDurTime_Houle =

VAR Duration = Query1[Time to Resolve sec]

VAR Hours = INT ( Duration / 3600)

VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)

VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)

VAR H = IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ))

VAR M = IF (LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ))

VAR S = IF (LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ))

RETURN

IFERROR(

IF (Query1[Time to Resolve sec]>0, CONCATENATE (H, CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )),BLANK())

,BLANK())

So, What I am looking is - Firstly , I need an average of Duration in secs in HH:MM format and then i want to use it in Card visualization as shown in pbix file image .

Note: I couldnt able to attach pbix file. hence posted an image for reference

[v-juanli-msft]

Hi @Anonymous 

Open Edit queries,

Add custom columns

sum=List.Sum(#"Changed Type"[time to resolve sec])

count of id=List.NonNullCount(List.Distinct(#"Added Custom"[id]))

average=[sum]/[count of id]

hours=Int64.From([average]/3600)

minutes=Int64.From(([average]-3600*[hours])/60)

seconds=Number.Mod([average]-3600*[hours],60)

click on "setting" icon to open the window to see what steps i applied.

Merge columns:

select "hours", "minutes","seconds" at the same time, then select "merge columns"

Change "Merged" to Type "duration"

Close&&apply

In report view, change the data format to "time" for the "Merged" column

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Maggie,

Thanks for your reply. I followed all the steps given by you . But I am facing an issue with Minutes and Seconds Columns as they are appearing in negative values. Could you please check the screen shot i have attached and advise me some thing ?

[v-juanli-msft]

Hi @Anonymous 

Please show me the total seconds you use to create "hours","minutes" and "seconds"

[Anonymous]

Hi Maggie,

Please find the screen shot . Below is the column I used in calculations.

 

[Anonymous]

I have followed same steps which you have given . See the Columns i have created below.

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Time to Resolve sec", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Sum", each List.Sum(#"Changed Type"[Time to Resolve sec])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Count of Incident ID", each List.NonNullCount(List.Distinct(#"Added Custom"[Incident ID]))),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Average", each [Sum]/[Count of Incident ID]),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Hours", each Int64.From([Average]/3600)),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Minutes", each Int64.From(([Average]-3600*[Hours])/60)),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Seconds", each Number.Mod([Average]-3600*[Hours],60)),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom5", {{"Hours", type text}, {"Minutes", type text}, {"Seconds", type text}}, "en-US"),{"Hours", "Minutes", "Seconds"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged")
in

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Change the queries as below

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Time to Resolve sec", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Sum", each List.Sum(#"Changed Type"[Time to Resolve sec])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Count of Incident ID", each List.NonNullCount(List.Distinct(#"Added Custom"[Incident ID]))),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Average", each [Sum]/[Count of Incident ID]),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Hours", each Number.RoundDown([Average]/3600)),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Minutes", each Number.RoundDown(([Average]-3600*[Hours])/60)),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Seconds", each Number.RoundUp(Number.Mod([Average]-3600*[Hours],60))),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom5", {{"Hours", type text}, {"Minutes", type text}, {"Seconds", type text}}, "en-US"),{"Hours", "Minutes", "Seconds"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged")
in

If #"Added Custom5" doesn't use Number.RoundUp, it will show 5.38 for seconds, then after merging columns, it can't change type to duration, it would throw an error.

Best Regards

Maggie

[Anonymous]

Hi Team,

I have tried few more . Please see below. 

Now the last measure "Hours" which is in decimal format should be converted to HH:MM format and the expected value is 05:50 (5 hours 50 mins ) . Please help me .

[Anonymous]

I have tried the below formulae , but it didnt worked .

Hh:mm = FORMAT([Hours],"hh:mm")