Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
adrianonannini
Frequent Visitor

Average Units per Transaction (with product filtering)

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_idsku_numbersales_units
4354325ABC1
4354325XYZ2

 

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?

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

Hi @adrianonannini 

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

Sample data

vxinruzhumsft_0-1689045381443.png

 

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

vxinruzhumsft_1-1689045428893.png

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.

 

 

View solution in original post

1 REPLY 1
v-xinruzhu-msft
Community Support
Community Support

Hi @adrianonannini 

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

Sample data

vxinruzhumsft_0-1689045381443.png

 

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

vxinruzhumsft_1-1689045428893.png

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.

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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