Forum Discussion
Average of Measure (Time data)
Hi,
This is a sample of my Power BI table:
| Date | First Call | Last Call |
| 6/25/2018 | 9:17 AM | 5:45 PM |
| 6/22/2018 | 10:43 AM | 10:55 AM |
| 6/21/2018 | 10:23 AM | 2:42 PM |
| 6/20/2018 | 9:59 AM | 3:35 PM |
| 6/19/2018 | 10:31 AM | 8:16 PM |
First Call and Last Call are calculated Measures and this is how I got them:
First Call = CALCULATE(MIN('Calls'[Local Time]),Calls[direction]="Outbound")
Last Call = CALCULATE(MAX('Calls'[Local Time]),Calls[direction]="Outbound")I would like to calculate Average time of the First Call and average time of the last call but. This is what I tried but it doesn't give the correct result:
Avg First Call = FORMAT(AVERAGEX(Calls,CALCULATE(MIN(Calls[Local Time]), Calls[direction]="Outbound")),"h:mm:ss")
The result is 13:32:26 which is not correct. The correct result should be 10:11AM (avg of the First Call).
How can I calculate an avarege time of the first / last call in this case where First / Last Call are the calculated measures?
Many thanks.
Hi LakiCG,
Based on my test, you can refer to below steps:
1.I have entered some sample data to test your problem, here is the data I entered in below picture.
2.I create two measures you mentioned in your problem and create two new measures to calculate the average time of the First Call and the Last Call.
First Call = CALCULATE(MIN('Calls'[Local Time]),Calls[direction]="Outbound")
Last Call = CALCULATE(MAX('Calls'[Local Time]),Calls[direction]="Outbound")
Avg First call = var a=SUMMARIZE('Calls',[Date],"firstcall",CALCULATE(MIN('Calls'[Local Time]),Calls[direction]="Outbound"),"Lastcall",CALCULATE(MAX('Calls'[Local Time]),Calls[direction]="Outbound"))
return FORMAT(AVERAGEX(a,[firstcall]),"h:mm:ss")
Avg Last call = var a=SUMMARIZE('Calls',[Date],"firstcall",CALCULATE(MIN('Calls'[Local Time]),Calls[direction]="Outbound"),"Lastcall",CALCULATE(MAX('Calls'[Local Time]),Calls[direction]="Outbound"))
return FORMAT(AVERAGEX(a,[Lastcall]),"h:mm:ss")
3.Create a Table visual, add the [Data], [First Call], [Last Call]. Create a Card visual, add the [Avg Last call] and [Avg First Call] fields. Now you can see the result.
You can also download the PBIX file to have a view.
https://www.dropbox.com/s/z7hb9ovfa0zwjwh/Average%20of%20Measure%20%28Time%20data%29%20.pbix?dl=0
Regards,
Daniel He
2 Replies
v-danhe-msftMicrosoft Employee
Hi LakiCG,
Based on my test, you can refer to below steps:
1.I have entered some sample data to test your problem, here is the data I entered in below picture.
2.I create two measures you mentioned in your problem and create two new measures to calculate the average time of the First Call and the Last Call.
First Call = CALCULATE(MIN('Calls'[Local Time]),Calls[direction]="Outbound")
Last Call = CALCULATE(MAX('Calls'[Local Time]),Calls[direction]="Outbound")
Avg First call = var a=SUMMARIZE('Calls',[Date],"firstcall",CALCULATE(MIN('Calls'[Local Time]),Calls[direction]="Outbound"),"Lastcall",CALCULATE(MAX('Calls'[Local Time]),Calls[direction]="Outbound"))
return FORMAT(AVERAGEX(a,[firstcall]),"h:mm:ss")
Avg Last call = var a=SUMMARIZE('Calls',[Date],"firstcall",CALCULATE(MIN('Calls'[Local Time]),Calls[direction]="Outbound"),"Lastcall",CALCULATE(MAX('Calls'[Local Time]),Calls[direction]="Outbound"))
return FORMAT(AVERAGEX(a,[Lastcall]),"h:mm:ss")
3.Create a Table visual, add the [Data], [First Call], [Last Call]. Create a Card visual, add the [Avg Last call] and [Avg First Call] fields. Now you can see the result.
You can also download the PBIX file to have a view.
https://www.dropbox.com/s/z7hb9ovfa0zwjwh/Average%20of%20Measure%20%28Time%20data%29%20.pbix?dl=0
Regards,
Daniel He
v-danhe-msft thank you, this works! Great solution, how to use calculated measure to get another one.
