Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ian-IKS
Regular Visitor

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)

1 ACCEPTED SOLUTION
DAX0110
Resolver V
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.

 

View solution in original post

5 REPLIES 5
DAX0110
Resolver V
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-msft
Employee
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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

HI Dale

 

I tried your suggestion but then everything got a rank of 1. A sample of my data is shown below:

 

SupplierInvoice dateDue dateJob CodeTypeOrder/ ContractInvoice/ Credit/ Claim NoOriginal Value Incl. GSTGSTPaid To DateNett Amount Due
A1FIRS25/10/201730/11/20170002Inv138220A006584208.4618.950.00208.46
ACCESS30/09/20170/01/19001152Inv13735615389867,697.00699.730.007,697.00
ACCESS30/09/20170/01/19001152Inv13735615389874,007.15364.290.004,007.15
ACCESS31/10/201730/11/20171152Inv13735615460724,140.72376.430.004,140.72
ACCESS31/10/201730/11/20171152Inv137356154607311,540.51######0.0011,540.51
ACCESS21/08/20170/01/19001165Inv13624415297044,240.60385.510.004,240.60
ACCSER31/10/20170/01/19000999Inv137333154577010,557.97959.820.0010,557.97
ACCSER31/10/20170/01/19000999Inv1382361545787475.2443.200.00475.24
ACTROL1/10/201730/11/20171115Inv13535541350360876.786.980.0076.78
ACTROL10/10/201730/11/20171117Inv13784426941215953.504.860.0053.50
ACTROL10/10/201730/11/20171117Inv1373212694121651,135.20103.200.001,135.20
ACTROL10/10/201730/11/20171117Inv137851269412173103.149.380.00103.14
ACTROL13/10/20170/01/19001117Inv13784426941222037.753.430.0037.75
ACTROL16/10/20170/01/19001117Inv137949269412240377.5934.330.00377.59
ACTROL17/10/201730/11/20171117Inv1379612694122462,376.00216.0061.972,314.03
ACTROL18/10/201730/11/20171117Inv1378442694122682,572.50233.860.002,572.50
ACTROL23/10/20170/01/19001117Inv138056269412342427.1138.830.00427.11
ACTROL27/10/201730/11/20171117Inv1381462694124245,207.40473.400.005,207.40
ACTROL27/10/201730/11/20171117Inv1381472694124254,593.60417.600.004,593.60
ACTROL27/10/20170/01/19001117Inv1381482694124271,483.90134.900.001,483.90
ACTROL27/10/20170/01/19001117Inv1381522694124332,408.18218.940.002,408.18
ADAPTI29/09/201731/10/20170301Inv137899EC0034,460.50405.500.004,460.50
ADROIT26/10/201730/11/20170001Inv138080817597,002.28636.570.007,002.28
ADVELE27/10/201730/11/20171159Inv1381431037155297.1327.010.00297.13
ADVELE17/11/201731/12/20171159Inv1384431043203257.4023.400.00257.40
ADVELE17/11/201731/12/20171159Inv1384431043380176.0016.000.00176.00
ADVELE28/09/201731/10/20171169Inv13755010271856,848.16622.560.006,848.16
ADVELE24/10/201730/11/20171169Inv1375501032831958.3287.120.00958.32
ADVELE11/10/20170/01/19001171Inv13780910317511,681.43152.860.001,681.43
ADVELE24/10/20170/01/19001171Inv13780910327598,894.00808.560.008,894.00
ADVELE8/11/201731/12/20171171Inv137809103950236,988.88######0.0036,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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.