Need assistance with a ranking function with removefilters

[Imrans123]

Hey All, 

Was wondering if anyone could help. 

I have a store table (which lists all the stores) and a invoice table which lists all the sales along with their respective store. Here is the model for your reference, 

Store Table 

Invoice Table

Each store is defined by the State in which it is located and the type of products it sells (can be either general items or specialized)

 In my pbix file, I have two slicers. Slicer one, which is  is State followed by Store Type from Store table.

Slicer two, which is Store Name 

Basically, I want to select a State and a Type from Slicer one, then select a store from slicer 2 and see how that store ranks against other stores from the same State and Store Type with respect to their total sales.

With regards to what I've got so far..

The "Total Sales" measure will show the total sales (Summation of invoice amount)

The "Store count" measure will show how many stores there are with context to slicer 1 

The "Store Ranking" measure will show the ranking of the store with respect to the "Store Count"

For example, if I select CA. General,  there are two stores, (Store 1 and 2). Store 2 has total sales of 260 which is ranked 1 out of a store count of 2. Store 1 has a total sales of 230 whic is ranked 2 out of a store count of 2.

Here are the measures I got so far 

Total Sales = SUM('Invoice Table'[Invoice Amount])

Store Count = CALCULATE(COUNTROWS('Store Table'), REMOVEFILTERS('Store Table'[Store Name])).

Store Ranking = IF(HASONEVALUE('Store Table'[Store Name]),

RANKX(

AllSELECTED('Store Table'[State],'Store Table'[Store Type]),

Calculate([Total Sales], ALLSELECTED('Store Table'[Store Name]))

,, DESC,SKip

), "select One store!"

)

The total sales and the Store count measure work as they should. However, the store ranking measure shows 1 for both store 1 and 2

I'm pretty sure it's because I'm unable to use REMOVEFILTERS on the RANKX function which isn't allowing me to have the desired result. If I were to use ALLSELECTEd on the store count measure, it would also return the wrong result. Anyone know hwo to incoroporate REMOVEFILTER or come up with an entirely different way of addressing this problem??

[Anonymous]

Hi @Imrans123 ,

I think you can try this code to create [Store Ranking] measure.

Store Ranking = 
IF (
    HASONEVALUE ( 'Store Table'[Store Name] ),
    RANKX (
        SUMMARIZE ( ALL ( 'Store Table' ), 'Store Table'[Store Name] ),
        [Total Sales],
        ,
        DESC,
        SKIP
    ),
    "select One store!"
)

Result is as below.

Best Regards,
Rico Zhou

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

[Barthel · Accepted solution]

Hey, 

This is a way to do it. 

Store Ranking = 
IF (
    HASONEVALUE ( 'Store Table'[Store Name] ),
    VAR SelectedStore =
        SELECTEDVALUE ( 'Store Table'[Store Name] )
    VAR AllStores =
        CALCULATETABLE (
            VALUES ( 'Store Table'[Store Name] ),
            REMOVEFILTERS ( 'Store Table'[Store Name] )
        )
    VAR RankStores =
        ADDCOLUMNS ( AllStores, "@Rank", RANKX ( AllStores, [Total Sales] ) )
    RETURN
        MAXX (
            FILTER ( RankStores, 'Store Table'[Store Name] = SelectedStore ),
            [@Rank]
        ),
    "select One store!"
)

• First get the selected storename with SELECTEDVALUE.
• Then retrieve all storenames based on the selection in slicer 1 with CALCULATETABLE and the right filter context.
• Rank all those storenames by adding an extra column consisting of the RANKX function that is based on the total sales.
• Now you look up the current storename in the table where all the storenames are ranked, using the FILTER function, and take its rank using the MAXX function.