Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Good morning,
I'm am trying to figure out how to retrieve the lowest price of each Producttype with the same Product_ID.
For Product_ID 626, I have Product_Type 954,955, 956, 957 and they eacht have. different price
What I want to do, is to write a function that we return the cheapest ProductType and Price for Product_ID 626.
I've tried the min funtion, but that gives me the lowest price of the whole datamodel.
Thank you for your help on this.
Solved! Go to Solution.
Please find a file attached with a solution that uses a star schema and does not calculate the minimum price as a calculated column.
Best
D
[Lowest Price] = // calculated column
var _prodid = T[Product_ID]
var __lowestPrice =
MINX(
topn(1
filter(
T,
T[Product_ID] = __prodid
),
T[Price],
ASC
),
T[Price]
)
return
__lowestPrice
[Cheapest ProductType] = // calculated column
var __prodid = T[Product_ID]
var __lowestPrice = T[Lowest Price]
var __prodType =
MINX(
topn(1
filter(
T,
T[Product_ID] = __prodid
&&
T[Lowest Price] = __lowestPrice
),
T[ProductType],
ASC
),
T[ProductType]
)
return
__prodType
Best
D
Thank you!
Lowest Price is correct.
For the Producttype it is not always correct.
For example: for product_ID 1284, the cheapest Producttype is 1994, but it returns 1993
A mistake has crept in... Instead of Lowest Price, the filtering should be on Price.
Cheapest ProductType = // calculated column
var __prodid = Data[Product_ID]
var __lowestPrice = Data[Lowest Price]
var __prodType =
MINX(
topn(1,
filter(
Data,
Data[Product_ID] = __prodid
&&
// The mistake was here.
Data[Price] = __lowestPrice
),
Data[Package_ID],
ASC
),
Data[Package_ID]
)
return
__prodType
Best
D
Thank you very much.
Now it is correct.
If now I want to calculate the cost of Recipe 1, using the lowest price ingredients. Should I make a new calculation? Or could I do it with filter settings. If I do it, using filters, I get a price of 39 euro, however, the price should be around 12,50 euro
My excelsheet looks as follows (only a few items are listed as an example):
Recipe_ID | Recipe_Description | Recipe_Pers | RecipeProduct_ID | Product_ID | Product_Description | Recipe_Qty | Brand_ID | Package_ID | Package_Description | Package_Qty | Package_Measure | Price | Measure |
1 | Spaghetti Bolognese | 4 | 9001 | 626 | Champignons Parijs | 0,25 | 241 | 954 | Everyday Champignons Middelgroot | 0,5 | Kg | 2,98 | Kg |
1 | Spaghetti Bolognese | 4 | 9001 | 626 | Champignons Parijs | 0,25 | 91 | 955 | Boni Selection Champignons Extra Fijn | 0,5 | Kg | 4,18 | Kg |
1 | Spaghetti Bolognese | 4 | 9001 | 626 | Champignons Parijs | 0,25 | 241 | 956 | Everyday Champignons Fijn | 0,25 | Kg | 4,2 | Kg |
1 | Spaghetti Bolognese | 4 | 9001 | 626 | Champignons Parijs | 0,25 | 91 | 957 | Boni Selection Champignons Gesneden | 0,3 | Kg | 5,3 | Kg |
1 | Spaghetti Bolognese | 4 | 9001 | 626 | Champignons Parijs | 0,25 | 91 | 958 | Boni Selection Champignons Heel | 0,195 | Kg | 4,87 | Kg |
1 | Spaghetti Bolognese | 4 | 9001 | 626 | Champignons Parijs | 0,25 | 91 | 959 | Boni Selection Champignons Mini | 0,39 | Kg | 5 | Kg |
1 | Spaghetti Bolognese | 4 | 9004 | 1284 | Gehakt Kalf Varken | 1 | NULL | 1993 | Gehakt Kalfs- En Varkensvlees | 0,5 | Kg | 8,45 | Kg |
1 | Spaghetti Bolognese | 4 | 9004 | 1284 | Gehakt Kalf Varken | 1 | 241 | 1994 | Everyday Gehakt Varken-Kalf | 0,5 | Kg | 7,28 | Kg |
For Recipe ID = 1 (Spaghetti) I want to calculate the total cost of the recipe using the Product_ID's with the lowest Price.
I've created two calculated colums:
- Lowest Price
- Lowest Price per Package
I've tried to write a measure using calculate, but that didn't work.
Can you help me on this?
Thank you.
Kind regards,
Sarah
Thenk you for all your help.
Ok, thank you.
I will look into this.
Sarah
Hello Mark,
This is a part of my table:
Recipe_ID | Recipe_Pers | Recipe_Qty | RecipeProduct_ID | Product_ID | Package_ID | Shop_ID | Brand_ID | PackageShop_ID | Content | Measurement | Price | Unit |
1 | 4 | 0,25 | 9001 | 626 | 954 | 241 | 1 | 10954 | 0,5 | Kg | 2,98 | Kg |
1 | 4 | 0,25 | 9001 | 626 | 955 | 91 | 1 | 10955 | 0,5 | Kg | 4,18 | Kg |
1 | 4 | 0,25 | 9001 | 626 | 956 | 241 | 1 | 10956 | 0,25 | Kg | 4,2 | Kg |
1 | 4 | 0,25 | 9001 | 626 | 957 | 91 | 1 | 10957 | 0,3 | Kg | 5,3 | Kg |
1 | 4 | 0,25 | 9001 | 626 | 958 | 91 | 1 | 10958 | 0,195 | Kg | 4,87 | Kg |
1 | 4 | 0,25 | 9001 | 626 | 959 | 91 | 1 | 10959 | 0,39 | Kg | 5 | Kg |
1 | 4 | 1 | 9004 | 1284 | 1993 | NULL | 1 | 11993 | 0,5 | Kg | 8,45 | Kg |
1 | 4 | 1 | 9004 | 1284 | 1994 | 241 | 1 | 11994 | 0,5 | Kg | 7,28 | Kg |
So for Product_ID 626, I would need to retrieve the price of 2,98.
Can you help me on this?
Thank you Mark for your answer.
I can't seem to get the formula to work for me.
User | Count |
---|---|
53 | |
35 | |
20 | |
15 | |
14 |
User | Count |
---|---|
94 | |
76 | |
36 | |
22 | |
18 |