Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
Solved! Go to Solution.
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
219 | |
89 | |
73 | |
66 | |
60 |