Forum Discussion
Helper IISummarize problem lowest price
Hello,
I want to calculate the cost of the recipe using the lowest price value. I also want to calculate the price based on the package (because even though I don't need the whole package to make the recipe, i still need to buy it).
I've alreaday created 2 colums in the table Verpakking Winkel : 1 column that has have the lowest value en 1 column that retrieves the Package ID for the lowest price.
I've added these two colums to the table Product using lookupvalue.
Now I want to calculate the cost of the recipe using only the Packages with lowest value. Secondly I want to calculate the cost using the quantity of the package (instead of the recipe qty).
I've tried it using summarize, but that doesn't work.
The link to the file:
Hi SL_1983 ,
Create the following column in the Ingredients table:
Price_Package = CALCULATE ( SELECTEDVALUE ( Verpakking[Verpakking Gewicht] ); FILTER ( Verpakking; Verpakking[Product Id] = 'Ingrediënten'[Product Id] && Verpakking[Verpakking ID] = RELATED ( 'Product'[Goedkoopste Verpakking Id] ) ) )Now add the following measure:
Totale Kostprijs Per Verpakking = sumx('Ingrediënten';'Ingrediënten'[Price_Package] * RELATED('Product'[Goedkoopste Prijs]))Check Result attach.
Hope this helps.
6 Replies
- MFelix
Super User
Hi SL_1983 ,
Can you give an example of a final result you would get? Selecting a recipe, also selecting 2 recipes in order to check what is the final value you need to get.
IF you can place also the details of the calculation would be great, to check the information on the example you have on the PBIX file.
SL_1983Hello Felix,
The results (Total cost using recipe Qty):
Recipe A: 12,55
Recipe 2: 6,013
Recipe 3: 8,65
The results (Total cost using Package Qty):
Recipe A: 46,39
Recipe 2: 26,53
Recipe 3: 30,65
The calculation of the total cost:
- Lowest Price using recipe Qty: the sum of 'Product' Goedkoopste Prijs * 'Ingrediënten'[Recept_Hoeveelheid]
- Lowest price using package Qty: the sum of 'Product' Goedkoopste Prijs * 'Verpakking Winkel'[Verpakking Inhoud]
- SL_1983
Hello MFelix,
I already managed to get the correct price using the lowest price per recipe Quantity.
I still have a problem calculating the price using the Package Quantity.
The results I should get are:
The results (Total cost using Package Qty):
Recipe A: 46,39
Recipe 2: 26,53
Recipe 3: 30,65
The calculation of the total cost:
- Lowest price using package Qty: the sum of 'Product' Goedkoopste Prijs * 'Verpakking '[Verpakking Inhoud]
I've changed the datamodel. See link below.
https://1drv.ms/u/s!AuaqHTxLCZ_h0wB8UX412RXRsm88?e=cYDSU5
Thank you in advance.