Translating an integer of hours in to text format for a standard card visual

[jnewell]

Hello all, 

Fist time poster here. 

I have been introduced to Power BI as an engineer and I am working through some dashboards which I have been asked to create. 

One of these identifies has a visualisation of a card with the average timje taken to close a ticket for our support desk during a sliding period. 

Unfortunately I am a little stuck.

I have created a column in my data to show the time taken (as duration) in my data set, and the card allows me to average this, however it shows 1.16 as the average. 

I am trying to get this to show as "1 hour 9 Minutes" on the card, but I just cannot seem to do it. (Pulling out what little hair I have left!)

Is there any suggestions you can give for me to do this? Whether it be a different visualisation or a calculation/measure that can be applied to show the average in the card, anything would be much appreciated. 

Many thanks in advacne, 

Jame

[v-robertq-msft]

Hi, @jnewell 

Have parry2k’s DAX formula helped you to find a solution?

You can try to use his formula to create a calculated column and place the column into the card chart and set the value as “Average”, like this:

If you have solved your problem, would you like to mark his reply as a solution so that others can learn from it too?

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k · Accepted solution]

@jnewell sorry add it as a column, not  measure since Time taken is a column

Time Take in Time Format = 
VAR __timeTaken = YourTable[Time Taken]
VAR __hour = TRUNC ( __timeTaken , 0 )
VAR __minutes = ( __timeTaken - __hour ) * 60
RETURN
FORMAT (
    TIME ( 
        __hour,
        __minutes, 
        0
    ),
    "long time"
)

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Shishir22]

Hello @jnewell ,

Replace [Measure] in below dax with your measure giving average value.

Measure in hours min = ROUNDDOWN([Measure],0) &" Hours " & ROUND(MOD([Measure]*60,60),0) &" Minutes"

Please mark it as solution if it fulfills your requirement

Thanks!

[parry2k]

@jnewell convert measure into time and then use format, something like this:

Measure in Time = 
VAR __measure = [Your Measure]
VAR __hour = TRUNC ( __measure, 0 )
VAR __minutes = ( __measure - __hour ) * 60
RETURN
FORMAT (
    TIME ( 
        __hour,
        __minutes, 
        0
    ),
    "long time"
)

and now use this new measure in the card visual.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[jnewell]

Hi @parry2k , 

Thank you for your speedy response. 

I have tried the measure you have suggested,

However I get the following error; 

"The value for 'Time Taken' cannot be determined. Either the column doesn't exist, or there is no current row for this column."

I know the column exists as I created it as part of trying to get the average time taken to close a ticket, however I am currently stumped once more. 

Any suggestions? 

Many thanks in advance. 

James