Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I need help with a setup related to food recipes and theoretical usage from sales statistics. The setup works as follow: sales counts are listed in tabel "CheckDetails". The food menu consist of both different dishes and menus. Menus consist of same dishes in a combi set (listed in tabel "MenuComponent"). The ingredients for each dish are listed in "IngredientComponent".
I have no problems in finding the total sales for each dish, however I would like to multiply the CheckDetails[SalesCount] with IngredientComponent[Amount] to get the theoretical usage for each ingredients based on the sales mix.
I have tried:
Sumx(IngredientComponent,IngredientComponent[Amount]*related(CheckDetails[SalesCount])), however I get an error with "no relationship between the two tables".
Do any of you have an idea to solve this issue?
Thanks in advance.
Solved! Go to Solution.
Hi @Molin ,
If there is no directly relationship between two tables, you need to use CALCULATE expression to get the corresponding data.
Try replacing the original RELATED call with the CALCULATE expression above:
theoretical product usage = SUMX( IngredientComponent,IngredientComponent[Amount]*
CALCULATE( VALUES( CheckDetails[SalesCount] ),
FILTER( CheckDetails,
CheckDetails[KEY] = IngredientComponent[KEY] ) ) )
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Molin ,
If there is no directly relationship between two tables, you need to use CALCULATE expression to get the corresponding data.
Try replacing the original RELATED call with the CALCULATE expression above:
theoretical product usage = SUMX( IngredientComponent,IngredientComponent[Amount]*
CALCULATE( VALUES( CheckDetails[SalesCount] ),
FILTER( CheckDetails,
CheckDetails[KEY] = IngredientComponent[KEY] ) ) )
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Molin , there is no direct relation between these tables, so relation will not work you can try like
Sumx(values(MenuItem[MenuItemID]),Sum(IngredientComponent[Amount])*sum(CheckDetails[SalesCount]))
You need a common dimension to do that
Thanks alot for the quick response @amitchandak
The proposed measure works to the extent that if I filter down on each dish it gives me the right amount or if the ingredients only appear in one dish, however in a table with all totals for ingredients the value is far to big. It seems like the it sums across the different dishes, which is why I thought Related would be appropiate.
Do you have a solution for this?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |