Dynamic top N + Others that changes with slicer

[Anonymous]

I have made this table to calculate a top 5 + Others:

Top5 = 
VAR Summary_01 =
    ADDCOLUMNS (
        VALUES ( 'Data'[Store]),
        "Total", CALCULATE ( SUM ( 'Data'[NET_SALES] ) )
    )
VAR Summary_02 =
    ADDCOLUMNS ( Summary_01, "Ranking", RANKX ( Summary_01, [Total] ) )
VAR TablesRankLess_And_Equal_To_5 =
    FILTER ( Summary_02, [Ranking] <= 5 )
VAR TablesRankGreater_Than_5 =
    ROW (
        "Store", "Others",
        "Total", SUMX ( FILTER ( Summary_02, [Ranking] > 2 ), [Total] ),
        "Ranking", 6
    )
VAR UnionAllRecords =
    UNION ( TablesRankGreater_Than_5, TablesRankLess_And_Equal_To_5 )
RETURN
    UnionAllRecords

 This gives me this table:

Store	Total	Ranking
Others	11000	6
Store A	2000	5
Store F	3000	4
Store D	5000	2
Store H	4000	3
Store B	6000	1

I've linked the Store column in the Top5 table to the Store column in the main Data table. When I apply a Location slicer to see which are the Top 5 per each location, the calculation stays the same despite me changing the location. Can anyone help please?

[amustafa · Accepted solution]

See the updated PBIX file. Yo uneed two columns in your base table. No need to create a new summarized table.

PBIX file link: https://1drv.ms/u/s!Aq3n-sopiGyqgokenHLsz-OES2UpIQ?e=3f837b

Store Rank by Location =

RANKX(

    FILTER('SalesTable', 'SalesTable'[Location] = EARLIER('SalesTable'[Location])),

    'SalesTable'[Net Sales],

    ,

    DESC

)

Store Category =

SWITCH(

    TRUE(),

    'SalesTable'[Store Rank by Location] = 1, "1 - " & 'SalesTable'[Store],

    'SalesTable'[Store Rank by Location] = 2, "2 - " & 'SalesTable'[Store],

    'SalesTable'[Store Rank by Location] = 3, "3 - " & 'SalesTable'[Store],

    'SalesTable'[Store Rank by Location] = 4, "4 - " & 'SalesTable'[Store],

    'SalesTable'[Store Rank by Location] = 5, "5 - " & 'SalesTable'[Store],

    "Other Stores"

)

[amustafa]

Hi @Anonymous 

Since you are joining the the two tables on column Store, it will not find the Store = 'Other'. From a business stand point, it's not important to show the 6th ranked store as 'Other'. Here's my soloution to create your calculated table as ...

Top5RankedStores =

VAR SummaryTable =

    SUMMARIZE(

        SalesTable,

        SalesTable[Location],

        SalesTable[Store],

        "Net Sales", SUM(SalesTable[Net Sales])

    )

VAR Ranked =

    ADDCOLUMNS(

        SummaryTable,

        "Rank", RANKX(FILTER(SummaryTable, [Location] = EARLIER([Location])), [Net Sales], , DESC, Dense)

    )

RETURN

    FILTER(

        Ranked,

        [Rank] <= 5

    )

You can download my sample files from my shared folder

Top N

If I answered your question, please mark this thread as accepted and Thums Up!
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/

[Anonymous]

I do need Other actually, Other is the sales of all stores that aren't in top 5, it's not the 6th ranked store renamed as Other