DAX expression for average of subtotaled items

# DAX expression for average of subtotaled items

• Tuesday, May 08, 2012 2:56 PM

I have data for two orders, one with two line items and one with  structured as follows. What is the PowerPivot for Excel DAX expression that would give me the order-average quantity (4.5+4.0 / 2 = 4.3) rather than the line-item-average quantity (8+1+1+1+8+9+2+3 / 8 = 4.1)?

 Order Line Item Qty 195436 544841 8.0 195436 544842 1.0 237657 514255 1.0 237657 514260 1.0 237657 521894 8.0 237657 521895 9.0 237657 521896 2.0 237657 521897 3.0

### All Replies

• Tuesday, May 08, 2012 3:34 PM

Create the following measures:

Avg1:=AVERAGE(Table1[Qty])

Avg2:=AVERAGEX(VALUES(Table1[Order]),[Avg1])

Essentially, you want an average of the averages for each order.

Obviously, you could give the measures more meaningful names.

• Marked As Answer by Tuesday, May 08, 2012 6:09 PM
•
• Tuesday, May 08, 2012 6:16 PM

Perfect... this was the answer. Thanks!

Mike Woodmansee