Summarising days/hours/mins

[khaycock]

I have used the below to create a days/hours/mins field but it doesn't summarise because it's a text field. Is there any other calucation i can use which will allow me to summarise when there are multiple lines of days/hours/mins? (it's a ticketing system so ideally i would want to summarise)

Thanks!

[v-jayw-msft · Accepted solution]

Hi @khaycock ,

Will you consider to create a measure instead of a column?

Measure = 
var minu = SUM(tickets[ticket time])
var hours = INT(minu/60)
var days = INT(hours/24)
var remainmin = MOD(minu,60)
var remainhours = MOD(hours,24)
return
days&" days, "&remainhours&" hours & "&remainmin&" minutes"

Best Regards,

Jay

[v-jayw-msft · Accepted solution]

Hi @khaycock ,

Will you consider to create a measure instead of a column?

Measure = 
var minu = SUM(tickets[ticket time])
var hours = INT(minu/60)
var days = INT(hours/24)
var remainmin = MOD(minu,60)
var remainhours = MOD(hours,24)
return
days&" days, "&remainhours&" hours & "&remainmin&" minutes"

Best Regards,

Jay

[khaycock]

If I wanted to use this to show average, could I just change the first variable like the below? Also how do I get it to not have all the decimal places?

[v-jayw-msft · Accepted solution]

Hi @khaycock ,

The only thing you need to do is to replace sum() to average().

And if you want a whole number, you could add int() before mod().

Refer:

Measure 2 = 
var minu = AVERAGE(tickets[ticket time])
var hours = INT(minu/60)
var days = INT(hours/24)
var remainmin = INT(MOD(minu,60))
var remainhours = MOD(hours,24)
return
days&" days, "&remainhours&" hours & "&remainmin&" minutes"

Best Regards,

Jay

[khaycock]

Amazing - thanks so much!! One final question, how can I amend it to make 1 day = 7.25 hours of a work day?

[v-jayw-msft]

Hi @khaycock ,

You could modify '/24' to '/7.25'.

Best Regards,

Jay

[mahoneypat]

This is best done by keeping your column as a decimal (in # days, for example) and using custom format strings to display it like that.

Use custom format strings in Power BI Desktop - Power BI | Microsoft Docs

Pat

[khaycock]

Can you help me with what to put in the custom format options please? I have a decimal field already which shows the days so can use that

[selimovd]

Hey @khaycock ,

what do you mean it doesn't summarize?

The time should summarize depending on your filter context. Then at the end you return a text string with the summarized days, hours and minutes.

Or where are you struggling?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[khaycock]

Sorry that I wasn't clear! I basically want to show a card that summarises all our tickets and shows how many days/hours/minutes a type of ticket has taken within a month. I can get the days/months/years to show on a line-by-line basis but when i summarise to a ticket type level, it doesn't work due to it not being able to sum.

I also have a card which shows the average time a ticket took to close which is currently in a decimal number based on number of days, but would rather this showed as days/hours/mins too