TopN Filter With Changing Totals Issue

[mvelazquez]

Hello!

I created a WhatIf filter in order to have a slicer (TopN) that would display the number of 'Broker Groups' and their respective amounts. The filter is working correctly when I select a number, the right amount of brokers gets displayed with their amounts. However, my table total shows the whole amount for all the brokers. The way that I want it to work is if I selected the top N amount of records, the table's total should display the sum of those N records, not the total amount of all records.

Thank you in advance! 😄

[jdbuchanan71 · Accepted solution]

Hello @mvelazquez 

We can use the TOPN with the amount directly rather than using RANKX and then we just need to bring back the subset of brokers like so.

TopN Value = 
VAR SelectedTop = SELECTEDVALUE ( 'TopN'[TopN],0 )
VAR _FilterContext = VALUES ( 'GROUP_BROKER'[GROUP_BROKER_NM] )

RETURN
SWITCH(
    TRUE(),
    SelectedTop = 0, [TOTAL_PREM],
    CALCULATE(
        [TOTAL_PREM],
        TOPN ( SelectedTop, ALL ( 'GROUP_BROKER'[GROUP_BROKER_NM] ), [TOTAL_PREM] ),
        _FilterContext
    )
)

[mvelazquez]

@jdbuchanan71 Thank you very much! Your approach opens up many other possibilities when working with slicers. I will definitely incorporate it!