Summary Totals of subcategory

[D3K]

Hello everyone!

Hope to find here the solution for my problem. Will appreciate any help

Here is the question. I've got 3 tables:
- sales

- product list

- ingredients

Each product consist of some ingredients with definite quantity of it in the product. And I can't write a measure or create support table so to be able to see the summary sales (quantity and weight) of each ingredient on any date and in any store. 

Summary ingredient sales = Parent product sales (quantity) * Quantity of ingredient in this product * Weight of ingredient

I've tried to solve it via creating support table and got the summary ingredient sales, but it doesn't react for slicers "Date" and "Store".  Or via measure - it reacts for slicer, but shows summary parent products sales for any of it's ingredient.

Will appreciate very much if would get help here

Thanks!

[D3K]

@v-piga-msft

And also pls check this. If there is no filter chosen - values in the result table are also not correct, it's to high: only 33 burgers are sold and 378 pieces of bread are used for it (the same and with the weight). 

As you can see it was not easy 

[v-piga-msft · Accepted solution]

Hi @D3K,

Please do some changes.

1. Change the Relationship like below.

2. Change your measure Sales, qnt like this = CALCULATE(MAX('August Sales'[Qnt]))

3. Change the measure totalweight of ingredient = [totalpiece of ingredient]*CALCULATE(MAX('Ingredients'[Weight,gr]))

More details, please refer to the pbix.

Best Regards,

Cherry

[D3K]

@v-piga-msft

Done! I've just changed MAX to SUM in "Sales,qnt" calculation. And SUMX in totalpiece calculations. And it started to work perfectly.

Cherry, thank you very much!!

[v-piga-msft]

Hi @D3K，

Your're welcome! It's glad that we can help!

Best Regards，

Cherry

[D3K]

@v-piga-msft

Thank you very much, it works almost correctly! 

But  as you can see on the screenshot below, when you choose any burger, which has more than 1 sale, in the result table it counts ingredients for only one dish anyway. But at the same time it shows correct total weight (marked green on the picture).

[D3K]

Here is the link to the .pbix file

https://www.dropbox.com/sh/xqi208lmp21xkno/AAD6glBEiI7Wm8adGoBY9rH7a?dl=0

[v-piga-msft]

Hi @D3K,

Do you want to get the output like below?

If it is, you should change the relationship like below Firstly.

Then you could create two measures with the formula below.

totalpiece of ingredient = CALCULATE(SUM(Ingredients[Quant-in-product]))*[Sales, qnt]

totalweight of ingredient = [totalpiece of ingredient]*CALCULATE(SUM('Ingredients'[Weight,gr]))

More details, please refer to the attachment.

If you still need help, please share your desired output.

Best Regards,

Cherry

[D3K]

@v-piga-msft

Cherry, thank a lot!! 

At first, it started to count correctly only needed ingredients for dishes! 

But, as you can see on the screenshot below, if you need to choose a date/store it starts to show some wrong values in the result table. It's seemed like it makes extra totals: only needed ingredients, but all values from sales table. Please, help to find, where is the mistake?

Thanks again!

[v-piga-msft]

Hi @D3K,

What is the parent product sales(quantity)?

If it is convenient, could you share your data as table so that I can copy and have tests. 

In addition, I would appreciate it if you could share your desired output so that we could help further on it.

Best Regards,

Cherry

[D3K]

Hi again, @v-piga-msft!

Here is the sample of structure of my report.

https://app.powerbi.com/view?r=eyJrIjoiMTVjZDI1YTQtNDI2Yi00NDdjLWFiNDUtMGQ0MGU2NDM2MzBkIiwidCI6ImQwO...

As you can see, we have some quantity of burgers during the month in different stores. Each burger consist of some ingredients.  And we need to get total sales of ingredients also related to the date and store.

For example, Burger 1 contains 2 pieces of tomato 20 gr each. We sold 3 burgers. So 3 (burgers) * 2 (pieces of tomato) * 20 (weight of piece of tomato) = 120 gr and 6 pieces. And data in final table on report sheet is "Tomato / 120 / 6".

In the red circle is needed view of final table: total sales of each ingredient

 Thank you!

[D3K]

Hi, @v-piga-msft!

Thanks! Yes, give me 20 minutes, pls. I'll translate nedded data and share.

Parent product - I meant, for example, Product 1 consists of tomato, chiken and cheese, and for theese ingredients Product 1 is Parent