Retrieve the lowest price of each Producttype with the same Product_ID

[SL_1983]

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.

[Anonymous]

[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

[SL_1983]

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

[Anonymous]

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

[SL_1983]

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

[Anonymous]

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

[SL_1983]

I've tried it using a calculated table.

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

[Anonymous]

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

[SL_1983]

Yes, sorry, i mean a measure

[SL_1983]

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

[Anonymous · Accepted 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

[SL_1983]

Thenk you for all your help.

[SL_1983]

Ok, thank you.

I will look into this.

Sarah

[Greg_Deckler]

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

[SL_1983]

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?

[SL_1983]

Thank you Mark for your answer.

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