Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I am having some problems calculating the average of a column I created.
In my dashboard I have a 'time' column and i want to calculate the average of this. What i have done so far was to create a new column and basically transform the time measure in an integer as below:
This one as i said is a new column that i created and it displays as below:
The sum of 'Avg time on page in seconds' is 11.214. The count is: 224 hence when i try to calculate the average i get the result of 50.06. Unfortunately this is wrong. What i would need is 136.28.
To run another test I exported the values and past them into an excel sheet that then i imported it in Bi. At this point when i calculate the average the number i get is actually 136.28 as you can see below:
What i can't get is why this is happening? Would somebody able to clarify this to me and suggest me a way to fix this?
Thanks in advance,
Alessandro
Solved! Go to Solution.
Hi @Alessandro-laba ,
Please do like this.
1. Add [Hour], [Minute] and [Second] columns in 'Edit Query'(The [Time] column should be 'Time' type.).
2. Calculate the seconds of each time.
// This is a measure
Average of time =
MAX([Hour]) * 3600 + MAX([Minute]) * 60 + MAX([Second])
// This is a calculated column.
Aver of time =
[Hour]*3600 + [Minute]*60 + [Second]
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Alessandro-laba ,
Please do like this.
1. Add [Hour], [Minute] and [Second] columns in 'Edit Query'(The [Time] column should be 'Time' type.).
2. Calculate the seconds of each time.
// This is a measure
Average of time =
MAX([Hour]) * 3600 + MAX([Minute]) * 60 + MAX([Second])
// This is a calculated column.
Aver of time =
[Hour]*3600 + [Minute]*60 + [Second]
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |