Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SL_1983
Helper II
Helper II

Retrieve the lowest price of each Producttype with the same Product_ID

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.

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

15 REPLIES 15
Anonymous
Not applicable

[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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

I've tried it using a calculated table.

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

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

Yes, sorry, i mean a measure

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
Not applicable

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

Thenk you for all your help.

 

Ok, thank you.

I will look into this.

 

Sarah

Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hello Mark,

This is a part of my table:

Recipe_IDRecipe_PersRecipe_QtyRecipeProduct_IDProduct_IDPackage_IDShop_IDBrand_IDPackageShop_IDContentMeasurementPriceUnit
140,2590016269542411109540,5Kg2,98Kg
140,259001626955911109550,5Kg4,18Kg
140,2590016269562411109560,25Kg4,2Kg
140,259001626957911109570,3Kg5,3Kg
140,259001626958911109580,195Kg4,87Kg
140,259001626959911109590,39Kg5Kg
141900412841993NULL1119930,5Kg8,45Kg
1419004128419942411119940,5Kg7,28Kg
             

 

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors