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)