Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
orderId | POS Data.GroupName | subTotal |
004BHG | Pizza | 14.76 |
004BHG | Beverage | 14.76 |
0FZGTB | Side Orders | 4.24 |
0FZGTB | Side Orders | 4.24 |
066P6I | Pizza | 58.41 |
066P6I | Beverage | 58.41 |
066P6I | Pizza | 58.41 |
066P6I | Pizza | 58.41 |
066P6I | Side Orders | 58.41 |
0FZGTB | Side Orders | 4.24 |
0APOCV | Wings | 35.34 |
0APOCV | Pizza | 35.34 |
Solved! Go to 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.
Hi,
Show the expected result.
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
orderId | POS Data.GroupName | subTotal |
004BHG | Pizza | 14.76 |
004BHG | Beverage | 14.76 |
0FZGTB | Side Orders | 4.24 |
0FZGTB | Side Orders | 4.24 |
066P6I | Pizza | 58.41 |
066P6I | Beverage | 58.41 |
066P6I | Pizza | 58.41 |
066P6I | Pizza | 58.41 |
066P6I | Side Orders | 58.41 |
0FZGTB | Side Orders | 4.24 |
0APOCV | Wings | 35.34 |
0APOCV | Pizza | 35.34 |
0APOC8 | Beverage | 1.51 |
0APOC8 | Beverage | 1.51 |
0APOC8 | Beverage | 1.51 |
0APOC8 | Beverage | 1.51 |
Expected Average Result | 24.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.
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.
Thank you!
Try this measure...
Average Subtotal Beverage Orders =
AVERAGEX(
FILTER(sampleTable, sampleTable[POS Data.GroupName] = "Beverage"),
sampleTable[subTotal]
)
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.
User | Count |
---|---|
99 | |
86 | |
80 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |