topic Re: Nested RankX function help needed and price difference formula require, table attached in DAX Commands and Tips

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

cmapijushroy — Fri, 05 Jun 2020 18:22:02 GMT

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

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

Anonymous — Fri, 05 Jun 2020 19:13:52 GMT

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)

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

Anonymous — Fri, 05 Jun 2020 19:12:31 GMT

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)

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

cmapijushroy — Sat, 06 Jun 2020 08:04:35 GMT

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

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

Anonymous — Sat, 06 Jun 2020 10:50:38 GMT

Hi cmapijushroy ,

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

Pls check.

Regards,

Harsh Nathani