topic Re: Many to many relationship calculation for groups of products in DAX Commands and Tips

Many to many relationship calculation for groups of products

Lukas96 — Fri, 16 Jun 2023 14:21:33 GMT

Hello guys,

I have this problem. I would like to calculate the bundle price and this is my model.

Sales table:

Product name Price

Main Device A	10000
Component 1	500
Component 2	600
Main Device B	15000
Component 3	1000
...

and I have another table with Bundles which has Many to Many relationship with the table above on Product name = Product name

Parts Product name Bundle Name

Part 1	Main Device A	Bundle A
Part 2	Component 1	Bundle A
Part 3	Component 2	Bundle A
Part 1	Main Device B	Bundle B
Part 2	Component 1	Bundle B
Part 3	Component 1	Bundle B
Part 4	Component 3	Bundle B

What I want to achieve is to have a Price per Bundle Name. For example Bundle A price is the sum of each components: (Main Device A, Component 1, Component 2) 10000 + 500 + 600 = 11100
for Bundle B it is (Main Device B, Component 1 x2, Component 3) 15000 + 500 + 500 + 1000 = 17 000

I am a bit lost on what should be a DAX code for the price?

Re: Many to many relationship calculation for groups of products

tamerj1 — Fri, 16 Jun 2023 15:05:53 GMT

Hi Lukas96

have tried simple SUM ( Sales[Price] )

Re: Many to many relationship calculation for groups of products

Lukas96 — Fri, 16 Jun 2023 15:41:41 GMT

This is simplified, I have a Price as a measure, what I have in the table sales is Revenue and Qty + I have Customers. Price is then a measure = Divide(Sum(revenue), Sum(Qty)).
The measure itself is not working for Bundles because for Bundle A for example I dont want to aggregate all the componets within Bundle A. If I aggregate it the Price for Bundle A will be 2 900. But the correct price should be the sum of individual components prices (10 000 +500 + 600 = 11 000)

Customer	Product	Revenue	Units
A	Main Device A	100 000	10
A	Component 1	10 000	20
A	Component 2	5 000	10
A	...	..	..
A	...	...	...

Re: Many to many relationship calculation for groups of products

tamerj1 — Fri, 16 Jun 2023 16:21:20 GMT

Lukas96

Try to smend [Price] as follows

Price =
SUMX ( Sales, DIVIDE ( Sales[Revenue], Sales[Qty] ) )