Top N Filter Inconsistency Across Visuals When Combined with Parameters and Other Filters

Q&A
, ,
1

I am trying to filter ALL visuals on a sheet to show only Top N accounts based on a measure (Sales/Units Growth), where N is driven by a parameter control with a toggle between “All” and “Top N”.

Visuals: KPI cards, pivot table, bar chart, treemap, line chart

What I tried:

Native Top N filter - Works for bar charts but in pivot tables applies Top N within each nested grouping instead of overall Top N. Also unreliable with parameter-driven N value.
denseRank calculated field - Requires the dimension in every visual’s field wells. Fails for KPI cards as they don’t support dimensions.
Pre-calculated rank in SQL - Doesn’t respect dynamic filters applied in QuickSight analysis.

Is this a known limitation or are there any workarounds possible?

1 Like
2

Hello @mahalakshmi_18, we can build calculated fields to manage filtering in combination with dashboard filters and parameters.

I’ll try to start simple and we can build from that based on the other filters/parameters you want to apply. For the basic idea though of having the user switch between all and Top N of a specific grouping, we can build like this:

dynamicFiltering = 

ifelse(${Param} = “Top 20” AND

denseRank(
[sumOver({Sales}, [${DynamicGroupParam}], PRE_AGG) DESC], 
[], PRE_AGG) <= 20,1,
${Param} = "All", 1,
0)

Now, depending on how you are allowing the user to select the top N value, we may need to adjust, but this will check if sum of sales with certain group by (selected by the user) is ranked within the top 20 and return 1, or will return everything if the user has All selected.

We may also need to adjust how we are accounting for All if you are using the “All values” option in the parameter. If that is the case, then you will check if the parameter is NULL instead of equal to all. isNull(${Param}) Also, if the Top N is dynamic, then we can make that dynamic as well.

This calculated field can then be applied to filter all visuals on the sheet. You just want to filter for the result of the field to be equal to 1.

Let me know if you have any questions and we can build from here!

3

@mahalakshmi_18 to try and explain this better, I went ahead and created a quick example in Arena. You can view that here: Dynamic Top N Filter

I also made the Top N filter dynamic here with a few options like Top 5, Top 10, and Top 20.

4

Hi @DylanMorozowski , thank you for the response! I tried a similar approach with denseRank and sumOver as a calculated field, but I’m facing the following specific issues:

  1. KPI Cards - When I add this calculated field as a filter with equals 1, the KPI card value doesn’t change at all - the filter has no effect on KPI visuals.
  2. Pivot Table - Even after applying this as a filter with equals 1, the pivot table shows inconsistent results because it applies the ranking within each nested row grouping rather than the overall Top N accounts.
  3. sumOver + denseRank nesting - When I use sumOver inside denseRank, the filter only works on table visuals. None of the other visuals like KPI cards, treemap and line chart get affected.

My specific setup:

Ranking dimension: accountid
Ranking measure: Sales/Units Growth (which is sum(CY sales) - sum(PY sales))
N value is dynamic via a parameter (max 30)
Toggle between “All” and “Top N” via separate parameter
Visuals: KPI cards, pivot table, bar chart, treemap, line chart

Is there a way to handle the KPI card limitation specifically? And how can we ensure consistent Top N across all visual types?

5

Hello @mahalakshmi_18, I may need a little more information about the KPI visuals and what information you are wanting to display on them related to a Top N grouping.

As for the pivot table, this is going to be a lot more complicated. These visuals don’t work as well because you already have group by values directly inside of the visual. You can potentially adjust the values that are being displayed, but it may be worth considering not impacting this visual or adjusting the calculated field slightly to try and show just the required groups.
Some thoughts:

  1. Include a calculated field in the Row or Column sections to dynamically change the dimension based on user selections.
  2. Utilize LAC-W calculations inside of the table to try and adjust the dimensions that the values are being aggregated/grouped by. This would be things like sumOver, distinctCountOver, etc. with PRE_AGG or PRE_FILTER.

As for the other issue, I think we just need to slightly adjust the calculated field.

ifelse(${Param} <> “All” AND
denseRank(
[(sumOver({CY Sales}, [{account_id}], PRE_AGG) - sumOver({PY Sales}, [{account_id}], PRE_AGG)) DESC],
[], PRE_AGG) <= ${DynamicValueParam},1,
0)

To clean this up, it may be easier to manage the Ranking Measure calculation sumOver({CY Sales}, [{account_id}], PRE_AGG) - sumOver({PY Sales}, [{account_id}], PRE_AGG) in its own calculated field to make testing a little easier.

We want the group by dimension to be within the sumOver calculations and leave the group by for the denseRank empty because that will make sure we are ranking each company. It may also be beneficial to try plugging each of the fields (including the ranking filter calculation) into a table to see if the results are matching your expectation.

6

Hi @DylanMorozowski,
I tried the updated calculated field approach:
ifelse(${Param} <> “All” AND
denseRank(
[(sumOver({CY Sales}, [{account_id}], PRE_AGG) -
sumOver({PY Sales}, [{account_id}], PRE_AGG)) DESC],
, PRE_AGG) <= ${DynamicValueParam},
1,
0)
However, after extensive testing, here is what we found:

  1. KPI Cards - TopN Filter still has absolutely no effect. KPI values don’t change regardless of Top N selection.

  2. Pivot Table - Still applies ranking within nested row groupings instead of overall Top N accounts. LAC-W approach didn’t resolve this.

  3. Bar Chart & Treemap - Works correctly with this formula.

  4. Line Chart - Still inconsistent results.

So the TopN filter works only for bar charts and treemaps but fails for KPI cards, pivot tables and line charts.

Is this a fundamental QuickSight limitation for certain visual types?

7

Hello @mahalakshmi_18, in order to assist with KPI visuals and the pivot table, I really need more information about them. Can you provide screenshots and tell me about the data that is being used in those visuals? We could potentially filter the visuals, but these can be more complicated. KPIs usually have a group by or are displaying a single value, and pivot tables have group by values that already manipulate the results.

8

Hello @DylanMorozowski ,
I’m unable to share screenshots due to data confidentiality, but here’s the detailed setup:

KPI Card Setup:

  • Values displayed: Single aggregated numbers like:

    • SUM(CY Sales)

    • SUM(PY Sales)

    • SUM(Sales/Units Growth) (CY - PY)

    • Sales/Units Growth% calculated field

  • No group by dimensions - just overall totals

  • Expected behavior: When “Top N = 10” is selected, KPI should show totals for only those 10 accounts, not all accounts

Pivot Table Setup:

  • Rows: date, region, country, account_id, product

  • Values: Sales/Units Growth, # of Activities

  • Multiple nested row dimensions creating hierarchical grouping

  • Expected behavior: Should show only rows where account_id is in overall Top N by Sales Growth, but currently it ranks Top N within each nested grouping (date + region + country combination)

Line Chart Setup:

  • X-axis: date

  • Value: SUM(Sales/Units Growth)

  • Expected behavior: Should show lines only for Top N accounts, but filter shows inconsistent results

Current Issue: The calculated filter field works for bar chart and treemap but has zero effect on KPI cards and incorrect behavior on pivot table and line chart.

Is there a specific way to handle KPIs with no group by dimensions, pivot tables with multiple nested dimensions, and line charts with date-based aggregation?