Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
LakiCG
Frequent Visitor

Average of Measure (Time data)

Hi,

 

This is a sample of my Power BI table:

DateFirst CallLast Call
6/25/20189:17 AM5:45 PM
6/22/201810:43 AM10:55 AM
6/21/201810:23 AM2:42 PM
6/20/20189:59 AM3:35 PM
6/19/201810:31 AM8: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.

 

1 ACCEPTED SOLUTION
v-danhe-msft
Microsoft Employee
Microsoft 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.

A.PNG

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.

A3.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-danhe-msft
Microsoft Employee
Microsoft 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.

A.PNG

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.

A3.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-danhe-msft thank you, this works! Great solution, how to use calculated measure to get another one.

 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.