Forum Discussion
RANKX DAX column help
Hi,
I'm trying to create a column that will rank Invoices across Suppliers.
This needs to be a column so that I can then use this Rank column to create a cumulative total and then (hopefully a measure to flag/show the Suppliers which make up the top 80% of our spend.
Having googled and browsed this forum i'm struggling to come up with something that works for me.
My data looks like this:
| SupplierCode | InvoiceNumber | Invoice Amount |
| S1000000 | :892347892 | £100 |
| S1000001 | Jeff:2343234 | £101 |
| S1000002 | Jeff:239048912 | £10 |
| S1000002 | Susan:93240923 | £5 |
| S1000002 | :9239239 | £50 |
| S1000003 | :2930499023 | £25 |
| S1000001 | Greg:8293489 | £5 |
I want to create either of the two results:
1. A table with the extra rank column, where rank is appled based on the total across the SupplierCode
| SupplierCode | InvoiceNumber | Invoice Amount | Rank |
| S1000000 | :892347892 | £100 | 2 |
| S1000001 | Jeff:2343234 | £101 | 1 |
| S1000002 | Jeff:239048912 | £10 | 3 |
| S1000002 | Susan:93240923 | £5 | 3 |
| S1000002 | :9239239 | £50 | 3 |
| S1000003 | :2930499023 | £25 | 4 |
| S1000001 | Greg:8293489 | £5 | 1 |
2. If the above is not possible, a derived table such as the below:
| SupplierCode | Invoice Amount | Rank |
| S1000000 | £100 | 2 |
| S1000001 | £101 | 1 |
| S1000002 | £10 | 3 |
| S1000003 | £5 | 4 |
Much appreciated,
Alex
As a calc column,,,try
Calc Column = RANKX ( Table1, CALCULATE ( SUM ( [Invoice Amount] ), ALLEXCEPT ( Table1, Table1[SupplierCode] ) ), , DESC, DENSE )
5 Replies
As a calc column,,,try
Calc Column = RANKX ( Table1, CALCULATE ( SUM ( [Invoice Amount] ), ALLEXCEPT ( Table1, Table1[SupplierCode] ) ), , DESC, DENSE )Thanks for your help Zubair_Muhammad
Unfortunately this isn't working for me.
I probably should have mentioned that my dataset includes about 30 other columns (which are not required for this calculation). I guess that might be important when using ALLEXCEPT.
See below for dummy data with the formula you helped with:
Supplier Code
Invoice Amount
* Rank Column
S687255
£ 23,029,923
4
S241863
£ 1,100,000
52
S712616
£ 2,700,210
75
S331533
£ 3,700,210
126
S943796
£ 800,000
126
S14822
£ 200,000
132
S229347
£ 400,000
163
S714313
£ 6,333,333
168
S18322
£ 100,000
214
S69059
£ 52,000
215
S628244
£ 120,000
230
Any further ideas?
Thanks again
Alex
Any number of columns shouldn't make a difference I believe.
Could you share your file?
Is the result below from a Table Visual in Report....Perhaps it is summing the RANKs...may be you need to mark the RANK column as "Dont summarize"
Could you share screen shot from dataview
Hi alexei7,
To create two measures as below.
total = CALCULATE(SUM(Table1[Invoice Amount]),ALLEXCEPT(Table1,Table1[SupplierCode]))
rankx = RANKX(ALLSELECTED(Table1[SupplierCode]),[total],,DESC,Skip)
Also to create a calculated columm worked well.
rankxcc = RANKX(ALLSELECTED(Table1[SupplierCode]),[total],,DESC,Skip)
For more details, please check the pbix as attached.
Regards,
Frank
