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_idurl_idLastPriceproduct_priceInvalidPrice
1589461123584114,770
1589462541222114,770
158946455333130,540
1589465648124115,231
1589461325444127,90

 

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!

Need Help

6 Replies

    • riguberto's avatar
      riguberto
      Frequent Visitor
      6 years ago

      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

       

       

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      riguberto
      Frequent Visitor
      6 years ago

      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's avatar
        riguberto
        Frequent Visitor
        6 years ago

        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)
  • 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)