topic Re: Retrieve the lowest price of each Producttype with the same Product_ID in DAX Commands and Tips

Retrieve the lowest price of each Producttype with the same Product_ID

SL_1983 — Tue, 24 Mar 2020 10:53:25 GMT

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.

Re: Retrieve the lowest price of each Producttype with the same Product_ID

Greg_Deckler — Tue, 24 Mar 2020 12:15:35 GMT

Try my latest quick measure, Lookup Min/Max https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814

Re: Retrieve the lowest price of each Producttype with the same Product_ID

SL_1983 — Wed, 25 Mar 2020 08:48:11 GMT

Thank you Mark for your answer.

I can't seem to get the formula to work for me.

Re: Retrieve the lowest price of each Producttype with the same Product_ID

SL_1983 — Wed, 25 Mar 2020 09:00:14 GMT

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?

Re: Retrieve the lowest price of each Producttype with the same Product_ID

Anonymous — Wed, 25 Mar 2020 10:03:03 GMT

[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

Re: Retrieve the lowest price of each Producttype with the same Product_ID

SL_1983 — Wed, 25 Mar 2020 10:41:42 GMT

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

Re: Retrieve the lowest price of each Producttype with the same Product_ID

Anonymous — Wed, 25 Mar 2020 14:17:42 GMT

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

Re: Retrieve the lowest price of each Producttype with the same Product_ID

SL_1983 — Wed, 25 Mar 2020 16:04:03 GMT

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

Re: Retrieve the lowest price of each Producttype with the same Product_ID

Anonymous — Wed, 25 Mar 2020 16:17:27 GMT

You want to create a measure for this. It all depends. You could create a new calculated table with only ReceipeID's and the lowest price for it. I don't know your model.

Best
D

Re: Retrieve the lowest price of each Producttype with the same Product_ID

SL_1983 — Wed, 25 Mar 2020 18:56:58 GMT

Ok, thank you.

I will look into this.

Sarah

Re: Retrieve the lowest price of each Producttype with the same Product_ID

SL_1983 — Wed, 25 Mar 2020 20:42:00 GMT

I've tried it using a calculated table.

I would however prefer to use a calculated measure.
How should I do that?

Re: Retrieve the lowest price of each Producttype with the same Product_ID

Anonymous — Thu, 26 Mar 2020 00:41:50 GMT

What is a calculated measure? There is no such object in the Power BI parlance.

Do you mean a measure or a calculated column?

Best
D

Re: Retrieve the lowest price of each Producttype with the same Product_ID

SL_1983 — Thu, 26 Mar 2020 06:49:33 GMT

Yes, sorry, i mean a measure

Re: Retrieve the lowest price of each Producttype with the same Product_ID

SL_1983 — Thu, 26 Mar 2020 17:51:42 GMT

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

Re: Retrieve the lowest price of each Producttype with the same Product_ID

Anonymous — Fri, 27 Mar 2020 00:34:05 GMT

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

Re: Retrieve the lowest price of each Producttype with the same Product_ID

SL_1983 — Fri, 27 Mar 2020 08:46:58 GMT

Thenk you for all your help.