Average Units per Transaction (with product filtering)

[adrianonannini]

I have a table called Sales Transactions loaded to my report. That is the fact table and there's a few dimension tables (Product Name, Location, Date) pointing to it.

On my fact table, I have some main columns:

order_id - identifies the ID of the transaction. It is not unique, because if there were X products being bought on a transaction, then there will be X lines, one for each product, and they will all have the same order_id value.

sales_units - the number of units sold for a specific product on a specific transaction.

sku_code - the code of the products being bought sales_units - number of times the product was bought.

So let's say I bought 2 units of the same product and 1 unit of a different one. Here's how the transaction would get to my table:

order_id	sku_number	sales_units
4354325	ABC	1
4354325	XYZ	2

Based on that, I would like to understand the average units per transaction. In theory, it's a very simple calculation: SUM of sales_units / DISTINCT COUNT of order_ids.

However, what if I want to analyse the average UPT for product ABC?

I would then need to SUM all the sales_units from the order_ids that contained ABC (not just ABC sales_units), and divide by the distinct count of these order_ids.

Anyone knows how can I do it through DAX?

[v-xinruzhu-msft · Accepted solution]

Hi @adrianonannini 

Based on the information you have offered, you can refer to the folloing measure

Sample data

Then create a mesure

Measure = var a=SUMMARIZE(FILTER(ALLSELECTED('Table'),[sku_number]="ABC"),[order_id])
var b=SUMX(FILTER(ALLSELECTED('Table'),[order_id] in a),[sales_units])
return DIVIDE(b,COUNTROWS(a))

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.