Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, folks!
I need to calculate the average of a time column (HH:MM:ss) and then show this in a line graphic in the same format (HH:MM:ss).
So what I did so far was to make my time column become decimal number, for example,
col 1 col2
01:00:00 1
02:00:00 2
That's the DAX that I used to generate column2 ->> col2 = 24, *(col1)
And then I can calculate AVG(col2), but I need to plot on my graphich in the time format (hh:mm:ss)
Can anybody assist me on this, please?
Thank you!
Solved! Go to Solution.
Hi @Anonymous,
Y-axis only supports numeric fields. If you add a text field like (HH:MM:ss) onto it, it will be aggreated by default. It is not available to show time on Y-axis with format HH:MM:ss.
Regards,
Yuliana Gu
I have calculated the Average time and now I wanted to plot that in a visual. Is that possible anywhere in PowerBI? What type of graph have you guys built using time as a measure? My challenge has been trying to plot something using time without transforming it into a sum of seconds. For example, 3 min instead of 180 seconds. Users have trouble making the calculation.
Hi @Anonymous,
Y-axis only supports numeric fields. If you add a text field like (HH:MM:ss) onto it, it will be aggreated by default. It is not available to show time on Y-axis with format HH:MM:ss.
Regards,
Yuliana Gu
Ok, thanks! Is there other way I can plot then? Any tips?
Hey,
maybe you will find this article helpful, it's about handling of time or better durations and how to treat duration in DAX:
After you converted your time to a decimal number that represents a duration (consider a meaningful unit), then you can use all the DAX functions to aggregate numbers
Hope this will gives you some ideas
Regards
Tom
You could create a measure below;
Average = FORMAT(CALCULATE(AVERAGE(Table[col1])),"HH:mm:ss")
works perfectly......thanks a ton.....
Didn't work. When I put the measure into the Value box of the line grapich, it doesn't stay.
But thanks anyways @gooranga1
ah @Anonymous yeah that is a downer, you can't chart times at this point I don't think. shame.
You could convert duration into seconds and use it in the visual and then use the measure noted above in the tooltip and that works ok.
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |