Bad calculate result

[SnirSh]

hey,

im trying to calculate avg of a time colum (hh:mm:ss) but its not going well,

anyone have an idea?

[MattAllington]

Your visual looks like it is formatted as a decimal number. Try formatting it as time. Your data averages about 1 minute, which is 0.0007. And that rounds to 0.00

[SnirSh]

Sorry i'm new BI desktop user. 

How can i define the visual format?

Thanks a lot!

[KHorseman]

@SnirSh Select your measure and go to the Modeling tab. The options for data type and formatting are there.

[SnirSh]

There is no option to chage the data type at all, and as u see no option to define the format to 'date time'.

Thank a lot!

[KHorseman · Accepted solution]

@SnirSh oh yeah, I forgot that DAX doesn't really support duration data. Are the results of this average always going to be less than 24 hours? The few I can see look pretty short. If so you can change your measure to

AVG TIME IN QUEUE = FORMAT( AVERAGE(CTI[AVG wait time]), "HH:mm:ss")

If it can be more than 24 hours, this will be more complicated. What is the data type of your wait time column?

[SnirSh]

Yes, the AVG result alway will be around a few minutes.

I changed the formula like you said and its looking good now!

u r the best!