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] ) )
    )
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:

StoreTotalRanking
Others110006
Store A20005
Store F30004
Store D50002
Store H40003
Store B60001

 

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?

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"
    )
     

3 Replies

  • 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

  • amustafa's avatar
    amustafa
    Icon for Solution Sage rankSolution Sage
    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"
    )