- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Summary Totals of subcategory
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!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @D3K,
Your're welcome! It's glad that we can help!
Best Regards,
Cherry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Here is the link to the .pbix file
https://www.dropbox.com/sh/xqi208lmp21xkno/AAD6glBEiI7Wm8adGoBY9rH7a?dl=0
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi again, @v-piga-msft!
Here is the sample of structure of my report.
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
10-23-2024 10:35 AM | |||
12-18-2024 03:27 PM | |||
09-30-2024 05:48 PM | |||
05-15-2021 12:48 PM | |||
07-31-2024 02:40 AM |
User | Count |
---|---|
113 | |
80 | |
55 | |
54 | |
44 |
User | Count |
---|---|
176 | |
116 | |
77 | |
62 | |
54 |