Forum Discussion

Ian-IKS's avatar
Ian-IKS
Regular Visitor
8 years ago
Solved

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 differe...
Reports
  • DAX0110's avatar
    DAX0110
    8 years ago

    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.

     

DAX0110's avatar
DAX0110
Resolver V
8 years ago

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.

 

Ian-IKS's avatar
Ian-IKS
Regular Visitor
8 years ago

Thanks, this works if I use average of SupplierRank to limit my chart to the top 10 suppliers based on Nett Amount Due