Forum Discussion
Rank is creating duplicate values
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)
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.
5 Replies
- DAX0110
Resolver V
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
v-jiascu-msftMicrosoft Employee
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
