Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table of creditors and I'm trying to rank them so I can create a chart of the top 10 but the rank formula I have created is giving me duplicate rank values even though the values are different. Can anyone help
The rank formula is:
Ranking = RANKX(ALLSELECTED('Open Creditors'),calculate(sum('Open Creditors'[Nett Amount Due])),,DESC,Dense)
Solved! Go to Solution.
SOLUTION: Create two calculated columns with these names and formulas:
(the table name is Creditors)
SupplierAmount =
VAR thisCreditor = Creditors[Supplier]
RETURN CALCULATE(
SUM( Creditors[Nett Amount Due] )
, ALL(Creditors)
, Creditors[Supplier] = thisCreditor
)
SupplierRank =
RANKX(
Creditors
, Creditors[SupplierAmount]
,
, DESC
, Dense
)
The version of Excel I'm using is 2016.
SOLUTION: Create two calculated columns with these names and formulas:
(the table name is Creditors)
SupplierAmount =
VAR thisCreditor = Creditors[Supplier]
RETURN CALCULATE(
SUM( Creditors[Nett Amount Due] )
, ALL(Creditors)
, Creditors[Supplier] = thisCreditor
)
SupplierRank =
RANKX(
Creditors
, Creditors[SupplierAmount]
,
, DESC
, Dense
)
The version of Excel I'm using is 2016.
Thanks, this works if I use average of SupplierRank to limit my chart to the top 10 suppliers based on Nett Amount Due
Hi @Ian-IKS,
Could you please provide a sample?
Maybe you can try it like this:
Ranking = RANKX ( ALLSELECTED ( 'Open Creditors'[The column you want to rank] ), CALCULATE ( SUM ( 'Open Creditors'[Nett Amount Due] ) ), , DESC, DENSE )
Best Regards,
Dale
HI Dale
I tried your suggestion but then everything got a rank of 1. A sample of my data is shown below:
Supplier | Invoice date | Due date | Job Code | Type | Order/ Contract | Invoice/ Credit/ Claim No | Original Value Incl. GST | GST | Paid To Date | Nett Amount Due |
A1FIRS | 25/10/2017 | 30/11/2017 | 0002 | Inv | 138220 | A006584 | 208.46 | 18.95 | 0.00 | 208.46 |
ACCESS | 30/09/2017 | 0/01/1900 | 1152 | Inv | 137356 | 1538986 | 7,697.00 | 699.73 | 0.00 | 7,697.00 |
ACCESS | 30/09/2017 | 0/01/1900 | 1152 | Inv | 137356 | 1538987 | 4,007.15 | 364.29 | 0.00 | 4,007.15 |
ACCESS | 31/10/2017 | 30/11/2017 | 1152 | Inv | 137356 | 1546072 | 4,140.72 | 376.43 | 0.00 | 4,140.72 |
ACCESS | 31/10/2017 | 30/11/2017 | 1152 | Inv | 137356 | 1546073 | 11,540.51 | ###### | 0.00 | 11,540.51 |
ACCESS | 21/08/2017 | 0/01/1900 | 1165 | Inv | 136244 | 1529704 | 4,240.60 | 385.51 | 0.00 | 4,240.60 |
ACCSER | 31/10/2017 | 0/01/1900 | 0999 | Inv | 137333 | 1545770 | 10,557.97 | 959.82 | 0.00 | 10,557.97 |
ACCSER | 31/10/2017 | 0/01/1900 | 0999 | Inv | 138236 | 1545787 | 475.24 | 43.20 | 0.00 | 475.24 |
ACTROL | 1/10/2017 | 30/11/2017 | 1115 | Inv | 135355 | 413503608 | 76.78 | 6.98 | 0.00 | 76.78 |
ACTROL | 10/10/2017 | 30/11/2017 | 1117 | Inv | 137844 | 269412159 | 53.50 | 4.86 | 0.00 | 53.50 |
ACTROL | 10/10/2017 | 30/11/2017 | 1117 | Inv | 137321 | 269412165 | 1,135.20 | 103.20 | 0.00 | 1,135.20 |
ACTROL | 10/10/2017 | 30/11/2017 | 1117 | Inv | 137851 | 269412173 | 103.14 | 9.38 | 0.00 | 103.14 |
ACTROL | 13/10/2017 | 0/01/1900 | 1117 | Inv | 137844 | 269412220 | 37.75 | 3.43 | 0.00 | 37.75 |
ACTROL | 16/10/2017 | 0/01/1900 | 1117 | Inv | 137949 | 269412240 | 377.59 | 34.33 | 0.00 | 377.59 |
ACTROL | 17/10/2017 | 30/11/2017 | 1117 | Inv | 137961 | 269412246 | 2,376.00 | 216.00 | 61.97 | 2,314.03 |
ACTROL | 18/10/2017 | 30/11/2017 | 1117 | Inv | 137844 | 269412268 | 2,572.50 | 233.86 | 0.00 | 2,572.50 |
ACTROL | 23/10/2017 | 0/01/1900 | 1117 | Inv | 138056 | 269412342 | 427.11 | 38.83 | 0.00 | 427.11 |
ACTROL | 27/10/2017 | 30/11/2017 | 1117 | Inv | 138146 | 269412424 | 5,207.40 | 473.40 | 0.00 | 5,207.40 |
ACTROL | 27/10/2017 | 30/11/2017 | 1117 | Inv | 138147 | 269412425 | 4,593.60 | 417.60 | 0.00 | 4,593.60 |
ACTROL | 27/10/2017 | 0/01/1900 | 1117 | Inv | 138148 | 269412427 | 1,483.90 | 134.90 | 0.00 | 1,483.90 |
ACTROL | 27/10/2017 | 0/01/1900 | 1117 | Inv | 138152 | 269412433 | 2,408.18 | 218.94 | 0.00 | 2,408.18 |
ADAPTI | 29/09/2017 | 31/10/2017 | 0301 | Inv | 137899 | EC003 | 4,460.50 | 405.50 | 0.00 | 4,460.50 |
ADROIT | 26/10/2017 | 30/11/2017 | 0001 | Inv | 138080 | 81759 | 7,002.28 | 636.57 | 0.00 | 7,002.28 |
ADVELE | 27/10/2017 | 30/11/2017 | 1159 | Inv | 138143 | 1037155 | 297.13 | 27.01 | 0.00 | 297.13 |
ADVELE | 17/11/2017 | 31/12/2017 | 1159 | Inv | 138443 | 1043203 | 257.40 | 23.40 | 0.00 | 257.40 |
ADVELE | 17/11/2017 | 31/12/2017 | 1159 | Inv | 138443 | 1043380 | 176.00 | 16.00 | 0.00 | 176.00 |
ADVELE | 28/09/2017 | 31/10/2017 | 1169 | Inv | 137550 | 1027185 | 6,848.16 | 622.56 | 0.00 | 6,848.16 |
ADVELE | 24/10/2017 | 30/11/2017 | 1169 | Inv | 137550 | 1032831 | 958.32 | 87.12 | 0.00 | 958.32 |
ADVELE | 11/10/2017 | 0/01/1900 | 1171 | Inv | 137809 | 1031751 | 1,681.43 | 152.86 | 0.00 | 1,681.43 |
ADVELE | 24/10/2017 | 0/01/1900 | 1171 | Inv | 137809 | 1032759 | 8,894.00 | 808.56 | 0.00 | 8,894.00 |
ADVELE | 8/11/2017 | 31/12/2017 | 1171 | Inv | 137809 | 1039502 | 36,988.88 | ###### | 0.00 | 36,988.88 |
Just to confirm I am trying to rank all creditors based on the Nett Amount Due for that supplier, so I need to sum all of those values for each supplier and then rank them
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |