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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
YcnanPowerBI
Helper II
Helper II

Using DISTINCTCOUNT measure to determine avg of another field

Hello all,

 

I have this measure to calculate the number of distinct orders that have a beverage.  I need to now also average the field Merge1[subtotal] subtotal of those orders that have a beverage, but nothing I try is working.  Appreciate any help

 

Bev Disctinct Cust = CALCULATE(DISTINCTCOUNT(Merge1[orderId]), FILTER(Merge1,Merge1[POS Data.GroupName] = "Beverage"))

 

Sample Data

 

orderIdPOS Data.GroupNamesubTotal
004BHGPizza14.76
004BHGBeverage14.76
0FZGTBSide Orders4.24
0FZGTBSide Orders4.24
066P6IPizza58.41
066P6IBeverage58.41
066P6IPizza58.41
066P6IPizza58.41
066P6ISide Orders58.41
0FZGTBSide Orders4.24
0APOCVWings35.34
0APOCVPizza35.34
1 ACCEPTED SOLUTION

Hi,

This measure works

Measure = AVERAGEX(SUMMARIZE(FILTER(Data,Data[POS Data.GroupName]="Beverage"),Data[orderId],"A",MIN(Data[subTotal])),[A])

Hope this helps.

Ashish_Mathur_0-1713412853518.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

Show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I apologize, I forgot to include the expected result.  I also added another order to more clearly identify the expected result.  Although there are 4 beverages on the last order, it should only use one of the 1.51 subtotals in the average

 

orderIdPOS Data.GroupNamesubTotal
004BHGPizza14.76
004BHGBeverage14.76
0FZGTBSide Orders4.24
0FZGTBSide Orders4.24
066P6IPizza58.41
066P6IBeverage58.41
066P6IPizza58.41
066P6IPizza58.41
066P6ISide Orders58.41
0FZGTBSide Orders4.24
0APOCVWings35.34
0APOCVPizza35.34
0APOC8Beverage1.51
0APOC8Beverage1.51
0APOC8Beverage1.51
0APOC8Beverage1.51
 Expected Average Result24.89

Hi,

This measure works

Measure = AVERAGEX(SUMMARIZE(FILTER(Data,Data[POS Data.GroupName]="Beverage"),Data[orderId],"A",MIN(Data[subTotal])),[A])

Hope this helps.

Ashish_Mathur_0-1713412853518.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you so much!  It worked.  Would you mind explaining what this part of the measure is doing? 

"A",MIN(Data[subTotal])),[A])

 

You are welcome.  Read up on the SUMMARIZE() function.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you!

jgeddes
Super User
Super User

Try this measure...

Average Subtotal Beverage Orders = 
AVERAGEX(
    FILTER(sampleTable, sampleTable[POS Data.GroupName] = "Beverage"),
    sampleTable[subTotal]
)



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





It does not appear to be taking the distinct order ID into consideration and instead averaging all the lines with beverage within the order.  So if an order has 2 beverages, it is looking at that subtotal as two seperate subtotals to average.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.