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

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