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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors