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 fo...
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
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