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

[cmapijushroy]

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 No	Item	Company	Price Quote
A0001	Toothpaste	Company A	10
A0001	Toothpaste	Company B	20
A0001	Toothpaste	My Company	30
A0001	Shop	Company B	20
A0001	Shop	My Company	15
A0001	Detergent	Company A	15
A0001	Detergent	Company B	10
A0001	Detergent	My Company	12
B0001	Toothpaste	Company XYZ	12
B0001	Toothpaste	Company ABC	15
B0001	Toothpaste	My Company	25
B0001	Toothpaste	Company PQR	17
B0001	Toothpaste	Company MNO	20

[harshnathani · Accepted solution]

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)

[cmapijushroy]

Hi @harshnathani 

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

[harshnathani]

Hi @cmapijushroy ,

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

Pls check.

Regards,

Harsh Nathani

[harshnathani]

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)