Top Brand Logic in Quicksight

Q&A
, ,
1

Hello everyone,

I’m working on a use case where I want to create a vertical stacked bar chart in Quick Sight that:

  • Shows Top N Brands by either Units or Dollars (based on user toggle)
  • Aggregates all other brands into an “Others” category
  • Allows the user to select Top N dynamically using a parameter slider

Currently, we are able to achieve this by mentioning Number of bar segment displayed under properties , but the requirement is to provide a slider so that users can dynamically select the Top N brands based on their preference.

Current setup :

image
image1697×567 78.6 KB

Date and Brand are dataset-level fields, and Dynamic Totals is a calculated field that toggles between Units and Dollars based on a parameter. The graph displays the sum of this field, depending on the selected metric.

Dynamic Totals

ifelse( ${TotalUnitsDollarsParam} = 'Units', units, ${TotalUnitsDollarsParam} = 'Dollars', dollars, 0 )

Where i am upto :

I’ve implemented a parameter called TopN, which allows users to input an integer value to dynamically control how many top-performing brands are shown. To support this, I created a calculated field called Try, which ranks brands based on the selected metric (Units or Dollars) using the following logic:

rank( [ ifelse( ${TotalUnitsDollarsParam} = 'Units', {Sum of Units}, ${TotalUnitsDollarsParam} = 'Dollars', {Sum of Dollars}, 0 ) DESC ], [] )
Where
Sum of Units : sum({units}) and
Sum of Dollar : sum({dollar})

When I drag this ranking field into a table visual and compare the results with the chart for a specific month (e.g., June 2025), the top values for Units and Dollars match correctly — so the ranking logic is working as expected and values are also matching with the graph shown above.
The next step is to create a calculated field that groups the Top N brands individually and aggregates the rest under “Other”. I tried using the following logic:

ifelse( {Try} <= ${TopN}, {Brand}, 'Other' )

However, this results in a “Mismatched aggregation” error, since Quick Sight doesn’t allow mixing aggregated and non-aggregated fields in a custom calculation.
I’ve also attempted using ** level-aware aggregations**, but it lead to the mismatch error or values doesn’t match (Calculated Field as below) :

I have tried the following calculated field, but it is not giving us the correct result. Additionally, if we replace units with sum(units) and similarly for dollars, we encounter a error : For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated:

ifelse( rank( [ ifelse( ${TotalUnitsDollarsParam} = 'Units', units, ${TotalUnitsDollarsParam} = 'Dollars', dollars, 0 ) DESC ], [], PRE_AGG ) <= ${TopN}, Brand, 'Other' )

At this point, I’m stuck on how to group non-top brands into “Other” dynamically so that graph will show TopN brand and group rest into others dynamically . Any guidance or suggestions on how to overcome this would be greatly appreciated.

Note: We do not want to use the Top and Bottom filter on the Brand field to pass the TopN parameter, because this approach only displays the Top N brands and excludes the rest. Our requirement is to group the remaining brands into an “Others” category rather than filtering them out.

2

Hi @soham

Since Try is already aggregated data you can not use it directly with un-aggregated fields.

Is it possible to upload your case with some sample data to Arena so we can try a few things to see if that resolves the issue?

Regards,
Giri

1 Like
3

Hello @Giridhar.Prabhu
Thank you for the Quick response.

The sample data will look like below :

image
image633×417 6.98 KB

Here the bar of vertical stacked bar chart for specific months should display TopN brands with its sum of units/dollar values and group rest brands into others .

Note :

  1. Here sum of units might be the sum of dollars basically it is parameter which toggles between Units and Dollars value
  2. We do not want to use the Top and Bottom filter on the Brand field to pass the TopN parameter , because this approach only displays the Top N brands and excludes the rest. Our requirement is to group the remaining brands into an “Others” category rather than filtering them out.
  3. We do not want to use the “Number of bar segments displayed” option available under visual properties because it does not allow users to dynamically select the Top N brands. Instead, our requirement is to provide a parameter-driven and calculated field level approach where users can interactively choose the desired Top N value and have the remaining brands aggregated into an “Others” category.

image
image235×456 12.5 KB

I was kind of trying the below logic for example :

rank([sumOver({units}, [{Brand}, truncDate('MM',{Date})], PRE_AGG) DESC], [truncDate('MM',{Date})], PRE_AGG)

Thank you.

5

We Have found similar issue which works for our case :- How to create a dynamic dimensional function that will replace text where ranking is greater than X with "other" text - #7 by Ying_Wang

You may close this topic
Thank you.