Using DISTINCTCOUNT measure to determine avg of another field

[YcnanPowerBI]

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

[Ashish_Mathur]

Hi,

Show the expected result.

[YcnanPowerBI]

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

[Ashish_Mathur · 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.

[YcnanPowerBI]

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

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

[Ashish_Mathur]

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

[YcnanPowerBI]

Thank you!

[jgeddes]

Try this measure...

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

[YcnanPowerBI]

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.