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

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