Forum Discussion
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! :D
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 ) )
2 Replies
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 ) )jdbuchanan71 Thank you very much! Your approach opens up many other possibilities when working with slicers. I will definitely incorporate it!
