Power Pivot - Average SKU per order

[Evans2]

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]

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]

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!