How to manage bundles in product dimension?

[iW1DOV]

Hi all, 

I am struggling to solve this problem. Our customer wants a report showing quantities of item sold; some items are sold individually and also in bundles. For example, you can buy a snap-pencil, leads and an eraser, but you can also find them in a single package with a pencil, two cases of leads, and an eraser. 

The product dimension contains the bundle as an individual item, and the sales table always refers to the bundle item ID, rather than idividual items , when the item sold is a bundle. 

The user requirements is to have in a report both the count of item sold, considering bundles as single items (ok, that's trivial), and the count of items, ignoring bundles, but considering the buldles components instead. In other words, they want me to expand bundles into the single components and count them instead.  This is the hard part of the task, and I am not managing to get the desired result. I believe I cannot simply expand bundles into their components in dimension table, because in this way I cannot count bundles as single products anymore. I tried identifying bundles in product dimension and tried using this information to count items in each bundle, to no avail 

Here it is my model schema (A mock, of course) 

Products

Bundles

Sales

Desiderd result: 

The general formula is Quantity Sold = Sum([item quantities]) + Sum([Item quantity in bundle] * [Bundle quantity])
Can anyone give me an hint and point me in the right direction? I can also alter the model if needed. 

Thanks in advance, 

Gianfranco 

[Sergii24]

Hi @iW1DOV, can you share pbix sample? Thanks!

[iW1DOV]

Ciao @Sergii24 

Here it is! https://drive.google.com/file/d/1CQSU5V7roQs9ei9sKJwqiGKG3O_0-gWW/view?usp=drive_link

Thank You! 

Gianfraco