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
rodrigoestrella
Frequent Visitor

Getting Quantity related to Max and Min Price

Hi everyone!

I need to get the quantity sold related to the max and min price for each article code I have on my table. Also I'd like to filter the results by quarter. Can anyone help me? This is how my table looks like. 

Thank you!

Rodrigo

pbi px y cant.jpg

1 ACCEPTED SOLUTION

Hi,

 

You may refer to my solution here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

14 REPLIES 14
Vvelarde
Community Champion
Community Champion

@rodrigoestrella

 

Hi, could you specify a sample of the result ?

 

Regards

 

Victor




Lima - Peru

Hi Victor, 

I'm not sure what you mean. What I want is a measure that gives me the quantity associated with the max price for a product.

This is the scatter chart of a product's quantity sold and price for several months. What I want is to get the the quantity asociated with this maximum price. In this case 101 units for the price of $47 which is the max price for this product.

Thank you !

scatt max price.png

Hi,

 

It will be easier to help you if you show a simple table with your desired result.  Also, share some data which can be pasted in MS Excel.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi! 

What I would like to be able to see in a measure is something like this. My main goal is to have a measure that calculates price-elasticity for each product and quarter as I filter my report my product.

 

ProductCodeMax PriceQ(Max Price)Min PriceQ(Min Price)Quarter
50973$ 45,99103482,88$ 19,00173120,65 

 

 I don´t know how to share the excel file over here, so here´s some of the data I have.

Cod.ArtArtículoVenta TotalPrecioCantidadMes-AñoQuarter
50973POLLO TRES ARROYOS 1 KG                           $ 5.477.360,08$ 31,99171221,05ene-16Q1
50973POLLO TRES ARROYOS 1 KG                           $ 5.256.697,01$ 31,99164323,17feb-16Q1
50973POLLO TRES ARROYOS 1 KG                           $ 4.073.989,10$ 31,49129374,10mar-16Q1
50973POLLO TRES ARROYOS 1 KG                           $ 4.591.942,26$ 31,49145822,28abr-16Q2
50973POLLO TRES ARROYOS 1 KG                           $ 4.313.110,64$ 31,99134826,88may-16Q2
50973POLLO TRES ARROYOS 1 KG                           $ 4.481.653,35$ 31,99140095,49jun-16Q2
50973POLLO TRES ARROYOS 1 KG                           $ 4.521.434,19$ 31,99141339,03jul-16Q3
50973POLLO TRES ARROYOS 1 KG                           $ 3.757.594,36$ 34,99107390,56ago-16Q3
50973POLLO TRES ARROYOS 1 KG                           $ 3.565.921,35$ 33,99104910,93sep-16Q3
50973POLLO TRES ARROYOS 1 KG                           $ 4.246.017,37$ 35,49119639,86oct-16Q4
50973POLLO TRES ARROYOS 1 KG                           $ 3.766.518,78$ 35,49106129,05nov-16Q4
50973POLLO TRES ARROYOS 1 KG                           $ 4.698.970,19$ 31,99146888,76dic-16Q4
50973POLLO TRES ARROYOS 1 KG                           $ 3.289.292,35$ 19,00173120,65ene-17Q1
50973POLLO TRES ARROYOS 1 KG                           $ 4.623.421,71$ 29,99154165,49feb-17Q1
50973POLLO TRES ARROYOS 1 KG                           $ 5.708.464,62$ 36,99154324,58mar-17Q1
50973POLLO TRES ARROYOS 1 KG                           $ 5.778.872,46$ 34,49167552,16abr-17Q2
50973POLLO TRES ARROYOS 1 KG                           $ 5.040.787,82$ 25,99193951,11may-17Q2
50973POLLO TRES ARROYOS 1 KG                           $ 6.242.214,97$ 30,99201426,80jun-17Q2
50973POLLO TRES ARROYOS 1 KG                           $ 5.061.936,40$ 29,99168787,53jul-17Q3
50973POLLO TRES ARROYOS 1 KG                           $ 5.988.602,32$ 34,99171151,87ago-17Q3
50973POLLO TRES ARROYOS 1 KG                           $ 5.084.544,09$ 34,99145314,25sep-17Q3
50973POLLO TRES ARROYOS 1 KG                           $ 5.503.864,58$ 34,99157298,26oct-17Q4
50973POLLO TRES ARROYOS 1 KG                           $ 5.331.481,22$ 29,99177775,35nov-17Q4
50973POLLO TRES ARROYOS 1 KG                           $ 8.667.901,71$ 37,99228162,76dic-17Q4
50973POLLO TRES ARROYOS 1 KG                           $ 4.885.697,44$ 40,99119192,46ene-18Q1
50973POLLO TRES ARROYOS 1 KG                           $ 4.759.176,25$ 45,99103482,88feb-18Q1
       

Hi,

 

You may refer to my solution here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks a Lot!!! It was exactly what I needed. And it also was much simpler than what I was doing.
Thanks again!!

You are welcone.  If my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hello - I am trying to build a price elasticity report in PowerBi. I used your post/ formulas which are great.  However, the qty at min price formula doesn't sum all the rows that have the min price, it just returns one of the rows.  How can I get it to sum all rows that have the minimum price?  Thank yo

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Data Example       
ProductInvoicePrice/GalQtyYearPeriod  
Jet Fuel 10902464.8177020195  
Jet Fuel 10902474.1710020195  
Jet Fuel 10902483.99820020195  
Jet Fuel 10902493.95930020195  
Jet Fuel 10902504.8140020195  
Jet Fuel 10902514.8150020195  
Jet Fuel 10902523.95910020195  
Jet Fuel 10902533.95920020195  
        
        
        
Expected Results      
Max Price4.81      
Min Price3.959      
Qty Max Price1270      
Qty Min Price600      
        

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thank you! Thank you!

Hi,

If my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

I don't see icon or way to mark is as Answer.  Do I just write "Answer"?

If so, then your reply was the Answer.Smiley Happy

I also put it in the tag.....

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.