Forum Discussion

cmapijushroy's avatar
cmapijushroy
New Member
6 years ago
Solved

Nested RankX function help needed and price difference formula require, table attached

I need your precious help to solve the below problem. I need to calculate

1. TENDER WISE, ITEM WISE, COMPANY WISE price RANKING.

2. % of the higher price we quote than the tender winner i.e. RANK1

I am working like below

 

But shows the wrong RANK.

 

Please suggest me the code for RANKX and to calculate OUR PRICE DIFFERENCE FORM RANK 1 in %

 

Please help

 

Thanks in Advance

 

Table Attached

Tender NoItemCompanyPrice Quote
A0001ToothpasteCompany A10
A0001ToothpasteCompany B20
A0001ToothpasteMy Company30
A0001ShopCompany B20
A0001ShopMy Company15
A0001DetergentCompany A15
A0001DetergentCompany B10
A0001DetergentMy Company12
B0001ToothpasteCompany XYZ12
B0001ToothpasteCompany ABC15
B0001ToothpasteMy Company25
B0001ToothpasteCompany PQR17
B0001ToothpasteCompany MNO20
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi cmapijushroy ,

     

     

    You can create 2 measures.

     

    Measure_Price = SUM('Table'[Price Quote])
     
    Ranking = RANKX(FILTER(ALL('Table'[Tender No],'Table'[Item],'Table'[Company]),'Table'[Tender No] =MAX('Table'[Tender No]) && 'Table'[Item] = MAX('Table'[Item])),[Measure_Price],,ASC)
     
     

    Regards,
    Harsh Nathani

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi cmapijushroy ,

     

     

    You can create 2 measures.

     

    Measure_Price = SUM('Table'[Price Quote])
     
    Ranking = RANKX(FILTER(ALL('Table'[Tender No],'Table'[Item],'Table'[Company]),'Table'[Tender No] =MAX('Table'[Tender No]) && 'Table'[Item] = MAX('Table'[Item])),[Measure_Price],,ASC)
     
     

    Regards,
    Harsh Nathani

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi cmapijushroy ,

       

       

      Percent Diff =
      For you to calculate the percentage difference.

      var d = IF(MAX('Table'[Company]) = "My Company",
      Minx(FILter(ALL('Table'),'Table'[Tender No] = MAX('Table'[Tender No]) && 'Table'[Item] = MAX('Table'[Item]) ),'Table'[Price Quote]))

      RETURN

      IF(MAX('Table'[Company]) = "My Company", DIVIDE( (MAX('Table'[Price Quote]) -d), MAX('Table'[Price Quote])))
       
       

      Regards,
      Harsh Nathani

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

    • cmapijushroy's avatar
      cmapijushroy
      New Member
      6 years ago

      Hi Anonymous 

      Thanks for your reply and I got solution after little change in syntax.

       

      But I require another calculation to find out our COMPANY PRICE DIFFERENCE FROM RANK1 PRICE IN %.

       

      Please help

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi cmapijushroy ,

         

        Have posted the solution in this same thread for difference in prices.

         

        Pls check.

         

        Regards,

        Harsh Nathani