cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mvelazquez Frequent Visitor
Frequent Visitor

TopN Filter With Changing Totals Issue

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! 😄

 

topn.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: TopN Filter With Changing Totals Issue

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

TopNBroker.jpg

View solution in original post

2 REPLIES 2
Super User
Super User

Re: TopN Filter With Changing Totals Issue

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

TopNBroker.jpg

View solution in original post

mvelazquez Frequent Visitor
Frequent Visitor

Re: TopN Filter With Changing Totals Issue

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)