cancel
Showing results for
Did you mean:

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

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

 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
1 ACCEPTED SOLUTION
Super User

Hi,

This measure works

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
8 REPLIES 8
Super User

Hi,

Show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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
Super User

Hi,

This measure works

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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

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

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

Thank you!

Super User

Try this measure...

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

Proud to be a Super User!

Helper II

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors