Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Dynamic top N + Others that changes with slicer

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] ) ) ) VA...
Need Help
  • amustafa's avatar
    amustafa
    2 years ago

    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's avatar
amustafa
Icon for Solution Sage rankSolution Sage
2 years ago

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
 
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's avatar
    Anonymous
    Not applicable
    2 years ago

    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