Sum duration column and display in chart

[LinlookZDJ]

Dear Power BI Experts,

I have edited my Time Gap column to duration type but I am unable to sum the duration column in my desktop. There is only count function available, is there anything i have missed out?

I would like to create a chart to display the total sum or average of duration for individual machine. 

Attached my dashboard and raw files for your reference:

https://drive.google.com/drive/folders/1bobrSCuXLHAm1xeJWCndMAscVgvw_mzJ?usp=sharinghttps://drive.go...

Thank you for your time!

Best regards

Lin

[community_pinki]

Hi @LinlookZDJ ,

You cannot take a sum of TimeGap column as it is not a numerical value type column So there's only option to take count and distinct count.

[amitchandak]

@LinlookZDJ , For sum and avg of duration, refer to approach in the blog

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...

[LinlookZDJ]

Hi, 

I wanted to display durations in my charts. However, I haven't figured out how to do it in a proper way.

Currently I'm using a column that represent duration as a decimal number. I have been trying to look at different blogs but doesn't lead me to the correct output. 

I have also tried to create a measure using the following code to convert decimal duration and display in dd:hh:mm:ss but still unable create a visual chart. 

 

Appreciate if you can guide me and give me an example.

Thanks!

Best regards

Lin

[v-shex-msft · Accepted solution]

HI @LinlookZDJ,

AFAIK, power bi data model tables do not support duration types and you cant use these types which are not support summarized on chart value fields. (date/time and durations types cannot be aggregate)

For this scenario, I'd like to suggest you convert them to 'total seconds' in whole number type. Then you can simply use them on visual value fields and they will be auto aggregated.

Regards,
Xiaoxin Sheng