All Products
Search
Document Center

Quick BI:TopN

Last Updated:Jun 20, 2025

TopN (quick filtering) filters results returned from the database based on numerical values, making it convenient for users to screen data and quickly select a specific number of maximum or minimum data entries. In data analytics, you can filter through dimensions or measures to segment at different granularities, helping you make better decisions. This topic describes how to set up TopN.

Prerequisites

Usage Notes

  • This analysis method is based on the returned result set calculation, with a current limit of 10,000 in the public cloud. If the total data returned exceeds 10,000, the calculation results may not meet expectations.

  • The charts that support TopN are shown below.

    TopN calculation method

    Description

    Supported chart types

    Overall TopN

    Performs TopN filtering on the overall data.

    • Line/Area charts: Line chart, Area chart, Stacked area chart, 100% stacked area chart, Combination chart.

    • Column/Bar charts: Column chart, Stacked column chart, 100% stacked column chart, Circular column chart, Ranking board, Bar chart, Stacked bar chart, 100% stacked bar chart, Waterfall chart, Bullet chart, Box plot, Histogram.

    • Bubble/Scatter charts: Bubble chart, Scatter chart, Facet scatter chart.

    • Table charts: Cross table, Heat map.

    • Pie/Ring charts: Pie chart, Rose chart, Radar chart, Treemap chart.

    • Geographic charts: Color map, Bubble map, Heat map, Flow map, Symbol map.

    • Metric charts: Metric card, Metric trend chart.

    • Funnel/Conversion charts: Funnel chart, Comparison funnel chart.

    • Other charts: Word cloud.

    Group-based TopN

    Groups data by a specific dimension and performs TopN filtering within each group.

    • Line/Area charts: Line chart, Area chart, Stacked area chart, 100% stacked area chart, Combination chart.

    • Column/Bar charts: Column chart, Stacked column chart, 100% stacked column chart, Bar chart, Stacked bar chart, 100% stacked bar chart, Bullet chart, Box plot, Histogram.

    • Bubble/Scatter charts (X-axis cannot be a measure): Bubble chart, Scatter chart, Facet scatter chart.

    • Table charts: Cross table, Heat map.

Configuration Entry

In the Fields panel of the chart, find the measure field that requires TopN calculation, click the image icon on its right, and select Advanced Calculation > TopN from the dropdown list. Then set the TopN calculation method according to your business scenario.image.png

When the calculation type is set to Custom, the calculation types available for cross tables differ from other charts, as explained below.image

Chart type

Calculation type

Limitation

Calculation logic

Cross table

Column TopN

Dimension fields in the Row area

Treats each column as an independent analysis unit and filters out TopN data in an entire column.

Column group-based TopN

Two or more dimension fields in the Row area

Groups single column data according to the selected dimension and performs TopN filtering within the groups.

The grouping logic is: starting from the first dimension in the Row area up to the selected grouping dimension, all dimensions in between are treated as a whole for grouping operations. For example, in the image below, the Region and Province fields will be used for grouping operations.image

Row TopN

Dimension fields in the Column area

Treats each row as an independent analysis unit and filters out TopN data in an entire row.

Row group-based TopN

Two or more dimension fields in the Column area

Groups single row data according to the selected dimension and performs TopN filtering within the groups.

The grouping logic is: starting from the first dimension in the Column area up to the selected grouping dimension, all dimensions in between are treated as a whole for grouping operations. For example, in the image below, the Region and Province fields will be used for grouping operations.image

Other charts

Overall TopN

/

Performs TopN filtering on the overall data.

Group-based TopN

  • Dimension fields in the Color Legend or Split area.

  • For bubble charts/scatter charts, the X-axis cannot be a measure.

Groups by dimension fields and performs TopN filtering within groups.

The grouping logic is:

  • When there are dimensions in the Color Legend but no dimensions in the Split area, grouping is performed by dimensions in the Category Axis.

  • When there are no dimensions in the Color Legend but dimensions in the Split area, grouping is performed by dimensions in the Split area.

  • When both areas have dimensions, the dimensions in both Category Axis and Split are treated as a whole for grouping operations.

Examples

Both measures and dimensions support TopN settings.

  • Setting TopN for measures

    If you need to view the top three data entries by order amount, you can set up the measure (Order amount) by following these steps.

    1. Log on to the Quick BI console.

    2. Follow the steps shown in the image below to enter the dashboard editing page.image.png

    3. In the top menu bar, click Add Chart, find the image icon, and create a Cross Table.

    4. In the Fields tab of the chart design, configure the cross table data.

    5. Click the image icon on the right and select Advanced Calculation > TopN > Top3.image.png

    6. Click Update.

      The cross table will now sort by order amount and display the top 3 data entries.image.png

  • Setting TopN for dimensions

    If you need to view the top three data entries by order amount for each region, you can set Advanced Calculation > Single Dimension TopN > Top3 on the dimension (Region). This will filter out the top three data entries for each region such as Northeast, North China, South China, etc.111

  • Custom TopN

    TopN retains the top N data entries. For example, selecting Top5 will retain the top 5 data entries. You can also customize the TopN Calculation Type, Order, and N Value. The calculation types available for cross tables differ from other charts. For details, see the comparison table in Configuration Entry.

    Examples of different calculation types in cross tables are shown below.

    Calculation method

    Configuration description

    Example effect

    Column TopN

    Treats each column as an independent analysis unit and filters out the top 3 data entries by order amount in an entire column.

    image

    Column group-based TopN

    In this example, the first dimension field in the Row is Region, and the grouping dimension is Province. Therefore, City is grouped by its Province and Region, and the top 3 cities by order amount are displayed for each province along with their order amounts.

    image

    Row TopN

    Treats each row as an independent analysis unit and filters out the top 5 data entries by order amount in an entire row.

    image

    Row group-based TopN

    In this example, the first dimension field in the Column is Region, and the grouping dimension is Province. Therefore, Product Type is grouped by its Region and Province, and the top 2 product types by order amount are displayed for each province along with their order amounts.

    image

  • Special TopN calculations and processing methods

    • Processing of totals and subtotals

      Totals and subtotals do not participate in quick filtering and cannot coexist with TopN. If TopN is selected, totals and subtotals will automatically be disabled.Untitled.gif

    • Processing of row and column mixed layouts

      In row and column mixed layouts, filtering is performed based on the dimension values in the first column by default. For example, in the example below, filtering is performed based on the order amount in the Northeast region.Untitled.gif

    • Processing of null values

      Null values are considered the smallest values in TopN. For example, if there are 10 data entries and you set Top5 (descending order), null values will be filtered out. If you set Bottom5 (ascending order), null values will be retained.

    • Processing of multiple TopN settings

      Multiple TopN settings cannot take effect simultaneously. The later setting will replace the previous TopN setting.Untitled.gif

    • Processing of multiple advanced calculations

      A measure can only have one advanced calculation set at a time. For example, if you set TopN, any previously set ranking will be replaced.