How to count sales of assemblies

[Fistachpl]

Hello,

My company sells products. Some products are sold separately but very often are sold in a group (assembly). E.g.:

15 Regular cookies (1 product)
15a Raisins (1 product)
Assembly1 Cookies with raisins (Total of 2 products: 1x regular cookies, 1x Raisins)

In a sales report I have:
Date of sale, Product or assembly sold

Date of sale	Product ID	Amount sold
15.01.2024	15	12
16.01.2024	15a	4
16.01.2024	Assembly1	2
03.02.2024	15	5

I have a table with products and another table of assemblies where I have information which assembly has which products in it.

So In total I sold in January of 2024:
 

Product or assembly ID	Amount
15	17 (15x separate, 2 as part of Assembly1)
15a	6 (4x separate, 2 as part of Assembly1)

How do I calculate it? I do not need a DAX formula of course but at least what I should look for.

Thank You!

[Anonymous · Accepted solution]

Hi @Fistachpl ,

First of all thanks to ryan_mayu  for the quick reply. Please allow me to add some thoughts:

We can create measures.

_soldDirectly = 
    CALCULATE(
        SUM(TSalesData[Amount sold]),
        TSalesData[Item Sold] IN VALUES(TProducts[Product ID])
    )

_salesThroughAssemblies = 
var _sold=CALCULATE(SUM('TSalesData'[Amount sold]),FILTER(ALLSELECTED('TSalesData'),[Product name] in VALUES('TSalesData'[Product name])))
var _cost=CALCULATE(SUM('TProducts'[Production Cost]),FILTER(ALLSELECTED('TProducts'),[Product Name] in VALUES('TSalesData'[Product name])))
RETURN _sold*_cost

Total = [_soldDirectly]+[_salesThroughAssemblies]

Then the result is as follows.

If I have misunderstood your needs please clarify in a follow up reply.

Best Regards,

Neeko Tang

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

[ryan_mayu]

why product 15's amount is 17?  12 for Jan, 5 for Feb and 2 for assemebly1. should that be 19?

[Fistachpl]

I wrote that in January 🙂

[ryan_mayu]

the amount for 15 in Jan is 12. Then should that be 12+2=14?

maybe you can create a new column

Column = if('Table'[Product ID]="Assembly1",'Table'[Amount sold],'Table'[Amount sold]+maxx(FILTER('Table',year('Table'[Date of sale])=year(EARLIER('Table'[Date of sale]))&&month('Table'[Date of sale])=month(EARLIER('Table'[Date of sale]))&&'Table'[Product ID]="Assembly1"),'Table'[Amount sold]))

[Fistachpl]

Ok but the code works only for Assembly1. But this I can work around. Can You explain the code ? Thank You

[ryan_mayu]

then could you pls update the sample data to be closer to your real data? 

Currently the wrokaround for you is to update the assembly 1 to the one in your real data

[Fistachpl]

Sure thing. I attach an excel file with product configurations:

Sales of assemblies data.xlsx

[ryan_mayu]

there are 5 sheets in the excel. Could you pls let me know the expected output?

[Fistachpl]

The endresult is to calculate and present in a table information how many times each product was sold. The product can be sold as standalone item but also as part of the assembly. Basically what You did before but Your code would work only for Assembly 1 and there are much more assemblies.

I need to have a measure, that for Each product calculates:

Amount of product sold as standalone 
+
Goes through each assembly and if the product is part of this assembly then should calculate the amount of times the assembly was sold and multiply it by amount of this product that is in the assembly.

Sales of assemblies data.pbix

I think this is how it should work:

There should be two measures:

_directSales - which calculates the amount of sold products as standalone producst - that is straightforward

_salesThroughAssemblies which should work like this:

It should iterate through the TProducts 
For each product it should iterate through TAssembliesParts (which should be filtered to the assemblies that consist of the selected product. 
Then it should iterate through this filtered table and for each assembly find a name, then find in TSalesData how many times the assembly was sold and then multiply it by the Product amount of the selected product in the selected assembly. 
Then it should sum it all up. 

What then would be left is to add _directSales + _salesThroughAssemblies

[Fistachpl]

Yes You are right. I looked at product number. Sorry about that. But the problem is dtill the same 🙂