Repost from earlier with sample data: PERCENTRANK FUNCTION - Equivalent in DAX

[Imran_Isshack]

Hi Power Bi Friends,

I inherited an MS Excel model and wanted to transition it to Power Pivot so that It becomes dynamic. 

This Excel model has the PERCENTRANK FUNCTION, which I can't seem to find in DAX. 

Here is my Ms Excel Fromula. How can I mirror this in DAX Language?

Position	Companies	Count	MIN VALUE	PERCENTRANK	MAX VALUE	PERCENTRANK	AVERAGE	PERCENTRANK
Partners			$	%	$	%	$	%
	Ace Store	5	$18,450	19%	$28,125	30%	$23,288	23%
	Acme Corporation	12	$14,625	13%	$54,000	94%	$34,313	36%
	Cupcake LLC	2	$13,050	9%	$15,075	14%	$14,063	12%
	Éclair Inc	6	$31,500	31%	$43,875	67%	$37,688	42%
	Grant & Eisenhoffer P.A.	2	$20,250	22%	$33,750	34%	$27,000	28%
	Globex Corporation	4	$10,125	0%	$12,375	5%	$11,250	1%
	Home Furnishing	8	$39,375	52%	$58,500	98%	$48,938	86%
	Hooli	8	$32,625	33%	$46,125	81%	$39,375	52%

Rate             Graded Position    Position       Type               Firm $24,750 Senior Associate Partner Category A Ace Store $18,450 Senior Associate Partner Category A Ace Store $26,100 Senior Associate Partner Category A Ace Store $21,375 Senior Associate Associate Category A Ace Store $28,125 Senior Associate Partner Category A Ace Store $24,975 Financial Analyst Financial Analyst Category A Ace Store $26,775 Investigator Partner Category A Ace Store $26,775 Investigator Investigator Category B Ace Store $18,000 Investigator Investigator Category B Ace Store

=PERCENTRANK(IF(Plaintiff_AllData[Position]="Partner",IF(Plaintiff_AllData[Type]="Category A",Plaintiff_AllData[Rate])),D3)

I) The positions refer to Job tiles

ii) Rates are billing Rates per employee, used to bill clients

iii)Type is a category of employees

Is there a way to simulate this MS Excel formula in DAX?

Imran

[v-yueyunzh-msft · Accepted solution]

Hi ,  @Imran_Isshack 

For your need , you want to convert the "PERCENTRANK FUNCTION" to dax . Right?

As searched , there is no PERCENTRANK function in DAX, you can manually calculate it from the results of RANKX and COUNTROWS.

For more information, you can refer to :

powerbi - DAX equivalent of Excel PERCENTRANK.INC per category - Stack Overflow

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Imran_Isshack]

@v-yueyunzh-msft 

Thanks very much for your response.

Yes, I would like the DAX equivalent of Excel's PERCENTRANK.

You're right, I have been searching google for a few days now and can't seem to find the equivalent. 

I saw this post before "powerbi - DAX equivalent of Excel PERCENTRANK.INC per category - Stack Overflow" but it seems so long and complicated and scared me away 🙂 . I do hope I would be able to use this successfully.

Imran