Get top n value considering duplicate values

[riguberto]

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	1123584	1	14,77	0
158946	2541222	1	14,77	0
158946	455333	1	30,54	0
158946	5648124	1	15,23	1
158946	1325444	1	27,9	0

Best Price= 14,77

Second best price=14,77

Third best price=27,90

Browsing the forum I have found this formula and it works when they are not duplicated values.

Second Best Price = 
SUMx (
    TOPN(1,
        TOPN ( 2, FILTER(priceregistrationraw,and(NOT(priceregistrationraw[InvalidPrice]=1),priceregistrationraw[LastPrice]=1)), [price] , ASC ),
        [price] , DESC
    ),
    CALCULATE ( min ( priceregistrationraw[price])))

But as long as Top N shows duplicates, it sums their values. Can you help me?

thanks in advance!

[harshnathani]

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)

[amitchandak]

@riguberto , Try like

rank =	rankx(FILTER(priceregistrationraw,and(NOT(priceregistrationraw[InvalidPrice]=1),priceregistrationraw[LastPrice]=1)),[price],,ASC,Dense)

Second Best Price = 
sumx(filter(priceregistrationraw,[Rank]=2),CALCULATE ( min ( priceregistrationraw[price])))

[riguberto]

Thanks for your response. 

rank... creating this metric pop up the error: can't determine the value of price. Maybe the column doesn't exist or it is no rows (translating myseflt from spanish... :S)

[riguberto]

ok, I didn't understood that rank was a column in the priceregistrationraw table (I was trying as a metric (I'm totally newby)).

My problem now is that the table has more products_id and they are getting ranked all together. I tried to put a product_id filter, but now the filter for lastprice and isvalid are not working (the product_id looks fine)...

What I tried

rank = rankx(FILTER(priceregistrationraw,and(NOT(priceregistrationraw[InvalidPrice]=1),AND(priceregistrationraw[LastPrice]=1,priceregistrationraw[product_id]=EARLIER(priceregistrationraw[product_id])))),[total_price_numeric],,ASC,Dense)

[harshnathani · Accepted solution]

Hi @riguberto ,

Is this what you are looking for.

https://community.powerbi.com/t5/Desktop/Top-3-unique-products-sold-per-product-category-on-which-da...

Harsh Nathani

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

[riguberto]

Thanks! this solution is working.

My only concerne is about performarnce. The priceregistrationraw table is a fact table with more than 10Million rows... is efficient to create a new table with the same info? 

thanks again