Getting wrong Ranking on date filter

[vin26]

Hello,

I am unable to get the proper ranking on a simple dataset. I have Date, Country and Projection values, please find below screenshot:

Please help me get the dynamic ranking based on the date filter selection.

[v-xuding-msft · Accepted solution]

Hi @vin26 ,

Sorry for late back.

Please try to create a date table and use its column as slicer. I modified the formula, please try again.

Date = CALENDAR(MIN(country_projection[date]),MAX(country_projection[date]))

Measure 2 =
RANKX (
    ALLSELECTED ( country_projection ),
    CALCULATE (
        SUM ( country_projection[projection] ),
        FILTER (
            ALLEXCEPT ( country_projection, country_projection[country] ),
            country_projection[date] >= MIN ( 'Date'[Date] )
                && country_projection[date] <= MAX ( 'Date'[Date] )
        )
    ),
    ,
    DESC,
    DENSE
)

[v-xuding-msft]

Hi @vin26 ,

Please try this:

Measure 2 =
RANKX (
    ALLSELECTED ( country_projection ),
    CALCULATE (
        SUM ( country_projection[projection] ),
        ALLEXCEPT ( country_projection, country_projection[country] )
    ),
    ,
    DESC,
    DENSE
)

[vin26]

Hi @v-xuding-msft  Thanks for the suggestion, it works for the small data source, when I load the actual data (size 30k rows) and selecting date range, rank is not showing correctly. But works without any date selection.

with date range selection

selecting all the date selection:

[v-xuding-msft · Accepted solution]

Hi @vin26 ,

Sorry for late back.

Please try to create a date table and use its column as slicer. I modified the formula, please try again.

Date = CALENDAR(MIN(country_projection[date]),MAX(country_projection[date]))

Measure 2 =
RANKX (
    ALLSELECTED ( country_projection ),
    CALCULATE (
        SUM ( country_projection[projection] ),
        FILTER (
            ALLEXCEPT ( country_projection, country_projection[country] ),
            country_projection[date] >= MIN ( 'Date'[Date] )
                && country_projection[date] <= MAX ( 'Date'[Date] )
        )
    ),
    ,
    DESC,
    DENSE
)

[vin26 · Accepted solution]

Hi @v-xuding-msft Thanks a lot for the solution, this works fine. Excellent!

I have also got another logic which also works for my requirement:

SumCountry = SUM(country_projection[projection])

RankCountry = IF(
    ISFILTERED(country_projection[date]),
        RANKX(
            ALLSELECTED(country_projection[country]),[SumCountry],,DESC,Skip
            )
    )

[Daviejoe]

If in doubt, check out what Marco & Alberto say

https://www.daxpatterns.com/ranking/

[vin26]

@amitchandak @harshnathani @Daviejoe @Pragati11  thank you everyone for the quick response. Tried all the suggestions but still not working. I have tried below measure with 'ALLEXCEPT, it somewhat works, but ranking is still not correct!!

I have only 3 in source data columns, date, country and projection. Please let me know if you have any suggestions. 

[harshnathani]

Hi @vin26 ,

Share some sample data in text format.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Pragati11]

Hi @vin26 ,

Can you share the screenshot on when you tried our method of using ALLSELECTED and what's the output you get?

Did you try this:

Rank = RANKX(ALLSELECTED(country_projection), CALCULATE(SUM(country_projection[projection])),, DESC, Dense)

Thanks,

Pragati

[vin26]

@Pragati11 Hi Pragati, here it is

[Pragati11]

HI @vin26 ,

The scresnhot just doen't help me with no description on why it is wrong.

Why do you think it is wrong? What you are expecting as output when you filter on date?

Thanks,

Pragati

[vin26]

Hi @Pragati11 in the screenshot attached 'Projections' are sorted in descending order, my expectation is to get ranking  in 1,2,3,4,... order, but if you see the measure 'Rank' shows 1,1,1,1,1... 

[Pragati11]

Hi @vin26 ,

Share some sample data, so that we can try replicating your issue.

Thanks,

Pragati

[vin26]

@Pragati11 Please find the sample file in below link, there are 30K rows of data, I haved reduced it to 1k

https://www.dropbox.com/s/qphhj10lu5qjta7/Ranking.pbix?dl=0

[amitchandak]

@vin26 , Rank is always calculated at level what is lowest .

in this case try

rankx(all(Table[country]),calculate(sum(table[projection])),desc,dense)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[harshnathani]

HI @vin26 ,

Try this measure

Measure = RANKX(ALLSELECTED( Country_projection[Country], Country_projection[projection]), CALCULATE(SUM(Country[projection)),,DESC) 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Daviejoe]

Rank =

RANKX (

    ALLSELECTED (

      CALCULATE  ( SUM (country_projection[country projection])),

         DESC)

[Pragati11]

Hi @vin26 ,

Try using ALLSELECTED in your DAX in place of ALL.

Thanks,

Pragati