Forum Discussion

riguberto's avatar
riguberto
Frequent Visitor
6 years ago
Solved

Get top n value considering duplicate values

Hello,    I'm trying to set 3 different dax metrics to get the 1st,2nd,3rd values of a product price (for diferents urls). product_id url_id LastPrice product_price InvalidPrice 158946 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi riguberto ,

 

Create a Calculated Column

 

rank =

IF('Table'[LastPrice] = 1 && 'Table'[InvalidPrice] = 0,
Rankx(FILTER('Table','Table'[LastPrice] = 1 && 'Table'[InvalidPrice] = 0),'Table'[product_price],,DESC,Dense))
 
 
Then create these measures
Best Price =
MAXX(
SUMMARIZE('Table','Table'[product_price],'Table'[rank],"Best",CALCULATE(MAX('Table'[product_price]),'Table'[rank] =1)),[Best])
 
 
Second Price =
MAXX(
SUMMARIZE('Table','Table'[product_price],'Table'[rank],"Best",CALCULATE(MAX('Table'[product_price]),'Table'[rank] =2)),[Best])
 
 
Third Price =
MAXX(
SUMMARIZE('Table','Table'[product_price],'Table'[rank],"Best",CALCULATE(MAX('Table'[product_price]),'Table'[rank] =3)),[Best])
 
 
 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)