Forum Discussion
Rank based off Grouped Figures
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 | |
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
6 Replies
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,
AngeliaHi 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