Forum Discussion

HarishMJ's avatar
HarishMJ
Regular Visitor
1 year ago
Solved

Ranking not working as expected

 

Hi everyone,

I’m facing an issue with the RANKX DAX formula and would greatly appreciate your help!

Here’s the scenario:
I have created the following RANKX measure, which works perfectly in a table visual when I include all four dimensions. However, when I try to rank using only one dimension (CIF), the ranking is not displayed correctly.

Here’s the DAX formula I’m using:

RankX =
IF (
ISFILTERED ( Sheet1[CIF] ) || ISFILTERED ( Sheet1 ),
RANKX (
ALLSELECTED ( Sheet1 ),
CALCULATE ( SUM ( Sheet1[Amount] ) ),
,
DESC
),
RANKX (
ALL ( Sheet1[CIF] ),
CALCULATE ( SUM ( Sheet1[Amount] ) ),
,
DESC
)
)

I have ensured that there are no duplicate rows in the table and that the data model has no explicit filters affecting the calculation. Still, the ranking doesn't seem to behave as expected when filtered by CIF.

You can access a sample PBIX file for reference using the link below:
https://drive.google.com/file/d/1t1O9ZFkkMU6ZAQbHTM8hTmxVMc5I5MN_/view?usp=sharing 

 

 

Could anyone help me identify what might be causing this issue and suggest a fix?

Thanks in advance for your time and support!

Best regards,
Harish M j

Need Help
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    HarishMJ 

    measure:

    RankX =
    IF (
    ISFILTERED ( Sheet1[CIF] ) || ISFILTERED ( Sheet1 ),
    RANKX (
    ALLSELECTED ( Sheet1[CIF] ),
    CALCULATE ( SUM ( Sheet1[Amount] ) ),
    ,
    DESC,
    DENSE
    ),
    RANKX (
    ALL ( Sheet1[CIF] ),
    CALCULATE ( SUM ( Sheet1[Amount] ) ),
    ,
    DESC,
    DENSE
    )
    )

    💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

3 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    Hi HarishMJ ,

    Please update your DAX to something like this:

    RankX = 
IF (
    ISFILTERED(Sheet1[CIF]),
    RANKX(
        ALL(Sheet1[CIF]),
        CALCULATE(SUM(Sheet1[Amount])),
        ,
        DESC
    ),
    RANKX(
        ALLSELECTED(Sheet1),
        CALCULATE(SUM(Sheet1[Amount])),
        ,
        DESC
    )
)


    your final output will look like this:

     

     

  • shashidhar's avatar
    shashidhar
    Frequent Visitor
    1 year ago
     

    Hi HarishMJ ,

    I’ve found a solution to your problem. Please see the measure below. Add this as a new measure to your report and check if it works. I’ve also implemented it in your shared dashboard using the RankX_new measure.

    Let me know if you need further assistance!


    Use this DAX code.

    RankX_new =
    IF (
        ISFILTERED ( Sheet1[CIF] ) || ISFILTERED ( Sheet1 ),
        RANKX (
            ALL ( Sheet1[CIF] ), -- Clear other dimensions but keep CIF for ranking
            CALCULATE ( SUM ( Sheet1[Amount] ) ),
            ,
            DESC
        ),
        RANKX (
            ALL ( Sheet1[CIF] ), -- Explicitly rank across all CIF values
            CALCULATE ( SUM ( Sheet1[Amount] ) ),
            ,
            DESC
        )
    )

     Measure implemented in the report please check below attachment. 

    If your proiblem is solved mark this as a solution and give rating this helps for me 

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    HarishMJ 

    measure:

    RankX =
    IF (
    ISFILTERED ( Sheet1[CIF] ) || ISFILTERED ( Sheet1 ),
    RANKX (
    ALLSELECTED ( Sheet1[CIF] ),
    CALCULATE ( SUM ( Sheet1[Amount] ) ),
    ,
    DESC,
    DENSE
    ),
    RANKX (
    ALL ( Sheet1[CIF] ),
    CALCULATE ( SUM ( Sheet1[Amount] ) ),
    ,
    DESC,
    DENSE
    )
    )

    💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn