# Power Pivot - Average SKU per order



## Evans2 (Nov 13, 2015)

Hi all, 

Working on creating a measure that will calculate the average number of SKU's (products) per order. 

I have: 

 Total order count =DISTINCTCOUNT([INVOICE_NO])
Total SKU Count =DISTINCTCOUNT(Products[NAME])

then I'm trying to create an average SKU per customer order 

=CALCULATE(AVERAGE([SKU]),([Order Count])) or =CALCULATE(AVERAGE([SKU]),[Order Count])

But this errors out. Is this a context error or lack of brain cells error? 

Any help would be appreciated.


----------



## Ozeroth (Nov 13, 2015)

You may need to redefine your Total SKU Count measure following the pattern here:
http://www.daxpatterns.com/distinct-count/

Then, for average number of SKUs per order, I think you want a measure like this :

```
= AVERAGEX ( VALUES ( [COLOR=#ff0000]TableName[/COLOR][INVOICE_NO] ), [Total SKU Count] )
```

Ideally include the appropriate TableName in front of [INVOICE_NO].

You need the AVERAGEX to iterate over INVOICE_NOs so that the distinct SKUs are counted independently for each invoice, then averaged.

Does this give the expected result?


----------



## Evans2 (Nov 13, 2015)

Hi Ozeroth, 

Thanks so much for the help. 

I Changed the SKU measure to count products in my sales table instead of my products table: Total SKU:=DISTINCTCOUNT(Sales[PRODUCT_NAME])

I then use AVERAGEX to iterate over INVOICE NOs in my sales table: Avg SKU:=AVERAGEX(VALUES(Sales[INVOICE_NO]),[Total SKU])             

This worked for me. Thank you again. Extremely helpful!


----------

