Forum Discussion

sophie-burgess's avatar
sophie-burgess
Regular Visitor
7 months ago
Solved

RANKX Formula duplicating ranks when value is the same

Hey team,

 

I have been trying for hours to get my RANKX formula to work but to no avail.

 

What I need is for my RANK measure to rank my group names by my YTD amount measure. I used the below formula to do this and it works EXCEPT when Groups have the same YTD amount i.e. 0. In this case, it just repeats the last known rank rather than continuing. Once the YTD amount hits 0 the rank needs to keep counting but it'll show the groups alphabetically with the continuing rank.

My formula :

Rank =
RANKX(ALLSELECTED('bcs vw_pbi_sales_detail'[Group Name]), [YTD Amount], , DESC,Dense)
 
Visual below : the rank needs to keep going i.e. 141 , 142 , 143 , 144 and so on

 

Please help!



Need Help
  • sophie-burgess's avatar
    sophie-burgess
    7 months ago

    Hey everyone,

     

    Thanks for the help but I've managed to find a solution by reading up on some articles, below was all I needed to do:

    Rank =
    RANK (
        DENSE,
        ALLSELECTED ( 'bcs vw_pbi_sales_detail'[Group Name] ),
        ORDERBY ( [YTD Amount], DESC, 'bcs vw_pbi_sales_detail'[Group Name], ASC )
    )
     
    Thanks

5 Replies

  • sophie-burgess's avatar
    sophie-burgess
    Regular Visitor
    7 months ago

    Hey everyone,

     

    Thanks for the help but I've managed to find a solution by reading up on some articles, below was all I needed to do:

    Rank =
    RANK (
        DENSE,
        ALLSELECTED ( 'bcs vw_pbi_sales_detail'[Group Name] ),
        ORDERBY ( [YTD Amount], DESC, 'bcs vw_pbi_sales_detail'[Group Name], ASC )
    )
     
    Thanks
  • DanieleUgoCopp's avatar
    DanieleUgoCopp
    Icon for Super User rankSuper User
    7 months ago

    Hello,

    try with this formula, I think it should work

    Rank =
    RANKX(
    ALLSELECTED('bcs vw_pbi_sales_detail'[Group Name]),
    [YTD Amount] * 1000000
    + RANKX(
    ALLSELECTED('bcs vw_pbi_sales_detail'[Group Name]),
    'bcs vw_pbi_sales_detail'[Group Name],
    ,
    ASC,
    Dense
    ),
    ,
    DESC,
    Skip
    )
    • sophie-burgess's avatar
      sophie-burgess
      Regular Visitor
      7 months ago

      Hey Daniel,

       

      I tried your formula and got the below erorr:

      A single value for column 'Group Name' in table 'bcs vw_pbi_sales_detail' cannot be determined. This can happen when a measure or function formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

      My sales details table includes invoices as a column so there are multiple instances of Group Name.

       

      Cheers

  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    7 months ago

    sophie-burgess Hi! Try:

    Rank =
    VAR CurrentYTD = [YTD Amount]
    VAR CurrentGroup = SELECTEDVALUE('bcs vw_pbi_sales_detail'[Group Name])
    RETURN
    RANKX(
    ALLSELECTED('bcs vw_pbi_sales_detail'[Group Name]),
    [YTD Amount]
    +
    DIVIDE(
    RANKX(
    ALLSELECTED('bcs vw_pbi_sales_detail'[Group Name]),
    SELECTEDVALUE('bcs vw_pbi_sales_detail'[Group Name]),
    ,
    ASC
    ),
    1000000
    ),
    ,
    DESC,
    Skip
    )

    BBF


    💡 Did I answer your question? Mark my post as a solution!

    👍 Kudos are appreciated

    🔥 Proud to be a Super User!

    • sophie-burgess's avatar
      sophie-burgess
      Regular Visitor
      7 months ago

      Hey BeaBF,

       

      I'm afraid this didn't change anything - I'm still seeing the duplicating rank numbers for group names where the YTS is 0 😞

       

      Cheers