Forum Discussion
HOW DO I DO RANKX with table without group by column in Transforrm data
Hello everyone,
I have a table bellow:
Table 1
| Customer_ID | Customer Name | No.Invoice | In Due date | Debt over 1-30 days | Debt over 31-60 days | Debt over 61 days |
| CS0001 | Customer 1 | INV0001 | 12398841 | 288410 | 12380130 | |
| CS0001 | Customer 1 | INV0002 | 88210430 | 1204 | ||
| CS0002 | Customer 2 | INV0003 | 23858128 | 3892423 | ||
| CS0002 | Customer 2 | INV0004 | 128384 | 839481 | 2389423 | 389284 |
| CS0001 | Customer 1 | INV0005 | 8349238 | 388766 | 73729349 | |
| CS0003 | Customer 3 | INV0006 | 885489 | 8384790 | 5472929 | 88741 |
| CS0003 | Customer 3 | INV0007 | 783729 | 8347239 | 733289 |
I want to visualize the table like below:
Table 1_Result
| Company Name | Total Debt | Rank |
| Cusomter 1 | 195757204 | 1 |
| Customer 3 | 28442778 | 2 |
| Customer 2 | 27750551 | 3 |
I have 1 solution for doing that, is using Group by column for each column then add custom column to calculate total debt in the Transform data then I use RANKX
But I want to improve my knowledge, does anyone have any solution to do this by only using measures?
Hi Anonymous ,
Please try:
Total Debt = CALCULATE ( SUM ( 'Table'[Debt over 1-30 days] ) + SUM ( 'Table'[Debt over 31-60 days] ) + SUM ( 'Table'[Debt over 61 days] ) + SUM ( 'Table'[In Due date] ), ALLEXCEPT ( 'Table', 'Table'[Customer Name] ) )Rank = RANKX(ALLSELECTED('Table'),[Total Debt],,DESC,Dense)Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous ,
Please try:
Total Debt = CALCULATE ( SUM ( 'Table'[Debt over 1-30 days] ) + SUM ( 'Table'[Debt over 31-60 days] ) + SUM ( 'Table'[Debt over 61 days] ) + SUM ( 'Table'[In Due date] ), ALLEXCEPT ( 'Table', 'Table'[Customer Name] ) )Rank = RANKX(ALLSELECTED('Table'),[Total Debt],,DESC,Dense)Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- Ashish_Mathur
Super User