Rank based off Grouped Figures

[Anonymous]

Ok so new to Power BI and trying to work my way through. I have Data that I am grouping and Ranking. Yet i can't get my Rank to work. I have tried every method I can find. Any Help would be greatly Appreciated.

I have it grouped by Loan Officer and want to Rank by Total Volume . I was using: 

Rank2 = RANKX(All('MTD'[Loan Originator]),'MTD'[Volume],,DESC) but i am getting all 1s 

Loan Officer	Branch Name	Loan Purpose	Total Loan Amount	Purchase Units	Purchase Volume	Refinance Volume	Refinance Units
A	4265	Cash-Out Refinance	194,000.00	0	0	194000	1
A	4264	NoCash-Out Refinance	210,000.00	0	0	210000	1
A	4265	Purchase	2,970.00	1	2970	0	0
A	4265	Purchase	116,648.00	1	116648	0	0
A	4265	Purchase	181,943.00	1	181943	0	0
B	4277	Purchase	111,000.00	1	111000	0	0
C	4281	Purchase	88,270.00	1	88270	0	0
C	4281	Purchase	112,000.00	1	112000	0	0
C	4281	Purchase	134,830.00	1	134830	0	0
C	4281	Purchase	201,286.00	1	201286	0	0
C	4281	Purchase	279,837.00	1	279837	0	0
C	4281	Purchase	329,985.00	1	329985	0	0
C	4281	Purchase	350,000.00	1	350000	0	0
D	4278	NoCash-Out Refinance	167,500.00	0	0	167500	1
D	4278	Purchase	305,201.00	1	305201	0	0
D	4278	Purchase	471,524.00	1	471524	0	0

[v-huizhn-msft]

Hi @Anonymous,

Your sample table and your formula field name is not consistent, do you want to rank [Total Loan Amount] group by [Loan Officer], right? If it is, please create calculated column using the formula.

Rank = RANKX(FILTER(Table1,Table1[Loan Officer]=EARLIER(Table1[Loan Officer])),Table1[Total Loan Amount],,DESC)

If this is not what you want, please share more details for further analysis.

Thanks,
Angelia

[Anonymous]

That does not work. First I get 1 when I use that formula. 2nd the screen shot you shows doesn't have them ranked properly. 

Each Loan Officer should have the same Ranking : A = 3, C=1 etc. 

[v-huizhn-msft]

Hi @Anonymous,

It's till confusing, you need to discribe rank the value based on which column and group by which column. You'd better list your expected result. Just one setence: "Each Loan Officer should have the same Ranking : A = 3, C=1 etc." I don't know to calculate the A=3,C=1? And please please share more details for further analysis.

Best Regards,
Angelia

[Anonymous]

Sorry i'm not making sense. I want the above data to show like this. Whre the Loan officers are ranked off of their total loan amount. 

Loan Officer	Total Loan Amount	Rank
C	1,496,202	1
D	776,725	2
A	705,561	3
B	111,000	4

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

Please create your rank measure using the formula, then create a table visual, you will get expected result.

Rank = RANKX(ALL(Table1),CALCULATE(SUM(Table1[Total Loan Amount]),ALLEXCEPT(Table1,Table1[Loan Officer])),,DESC,Dense)

Best Regards,
Angelia

[Anonymous]

THANK YOU SOOOOOOOOOOOOOOOOOOO MUCH!!!!!!!!