Rankx ignoring just one slicer

[Cyruslcc]

Hello,

I'm trying to create a table as below but encountered a problem that when I add a slicer filter on store, and I filtered a store out, the ranking just become 1, instead of keeping the original ranking, but I also don't want to use ALL cause I want the ranking to be filtered by

Slicer 1: Market and

Slicer 2: Year but

Slicer 3: Store is just intended to let user filter out their store information, this should not change the ranking

Now I'm using a rankx for this: Rank = Rankx(allselect('Table'[Store]),[Sales])

My apologies on the excel showing, as my dataset is confidential. 

Let's say I'm using a dataset like this:

And I'd like to create a table with 3 slicers (Year, Market, Store) that shows:

While now, when I add a store slicer let's say 1, it will become like this which I want the rank to keep at 3 that's based on Market and Year slicer only.

My apologies on the messiness, its my first time asking question here.

Thank you!

[Anonymous · Accepted solution]

Hi, @Cyruslcc 

You can try the following methods.

Rank = 
RANKX(ALLEXCEPT('Table','Table'[Market],'Table'[Year]),CALCULATE(SUM('Table'[Sales])),,DESC)

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kedar_Pande]

@Cyruslcc 

Rank = 
RANKX(
FILTER(
ALL('Table'[Store]),
'Table'[Market] = SELECTEDVALUE('Table'[Market]) && 
'Table'[Year] = SELECTEDVALUE('Table'[Year])
),
[Sales],
,
DESC
)

Ensure to test this setup to confirm it meets your expectations before finalizing your report.

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

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Rank: =
IF (
    NOT ISBLANK ( [Sales:] ),
    RANKX (
        CALCULATETABLE (
            SUMMARIZE (
                sales_fact,
                market_dim[market],
                period_dim[year],
                store_dim[store]
            ),
            ALLSELECTED ( market_dim[market] ),
            ALLSELECTED ( period_dim[year] ),
            REMOVEFILTERS ( store_dim[store] )
        ),
        [Sales:],
        ,
        DESC
    )
)