May 31, 2023

How to Create Ranks and Top N Filters in Sigma Computing

By Quinn Madsen

Sigma Computing allows users to quickly analyze and visualize their data in just a few clicks. One of the key features at the user’s disposal is its filter options. Using filters enables the user to narrow down their data, resulting in more refined and specific insights into what can be an overwhelming amount of data.

Any developer aims to optimize performance and the user experience by offering shortcuts, from huge data sources to actionable decisions. Identifying and analyzing outliers is one such shortcut. Enter Top N Filters.

By filtering your data down to the top or bottom 10 customers, the user can quickly see who affects your outcomes the most.

In this blog, we will focus on Top N Filters and how they can efficiently and effectively analyze data and gain valuable insights.

Top N Filter – How to

Start by adding the main visual (element) to which you want to apply your Top N Filter to.

On the left-hand side, click on the “ + “ button. This will open up the list of data elements, input tables (currently in beta), UI elements, and control elements you can add to your dashboard.

For this example, we will utilize Viz elements (Visual) and control elements (Filters/text boxes and drill down). After creating your element(s), we can add in the Top N Filter.

After selecting the Top N control element, you can move it to where it fits on your dashboard and continue with any editing options. Keep in mind you can adjust this filter. You can keep it as a Top N or change it to Bottom N, Top percentile, or Bottom percentile.

Now we need to set the target for our filter. Click on your newly created filter and click on TARGETS. You will be prompted to choose which element you want the filter to affect. Choose the element(s) you want by hovering over the “ >.”

The transformation can begin now that our target(s) are set. Edit the number of results that show by manually entering the Top or Bottom number you want. This is a great way to look at your top and bottom performers and aid in making tough business decisions.

It’s easy to see how this helps with analyzing your data. Take a look at the side-by-side example below. The left bar chart shows all states, while the right bar chart just shows the top 10 performers. Breaking down your data like this makes understanding your data clearer and less cluttered. You can always adjust the number of states you want to look at.

Rank Calculation – How to

In the same vein as a Top N Filter, Sigma users can also use the Rank function. Similar to the Excel calculation, the syntax looks like this – Rank ( [column], “direction” ) either descending or ascending. Sigma will automatically set the rank to ascending (asc) if you do not select one in your formula.

Starting with a table, add a new column. Click on the down arrow to the right of any existing column and select Add new column.

You will now have a new column titled Calc. Now it’s time for the Rank formula. At the top of the top, enter the following calculation – Rank ( [column], [direction]. In this example, we will use the state name on the x-axis and total sales on the y-axis.

Using our new formula and deciding to sort in descending order, we now have our new column, ranking each state 1-50, using the Rank function. Rank ([Total Sales], “desc”).

Once the column is created, you can rename the column by double-clicking on the current name or by selecting the button to the right of the name and choosing “Rename column.”

We can clearly see our top-selling states and where each state ranks among the others.

Rank Dense Calculation

Along with the Rank calculation in Sigma, we can also use the RankDense calculation. RankDense ( [column], “direction”). The best use case for this calculation is when you want to create dense rankings that include all tied or identical values in a single rank without any gaps between ranks.

In the example above,  the first row shows the highest value of 9, which ranks 1. Since there is only one row with a value of 9, its dense rank is also 1. The value of 7 appears twice in the data set, so both instances are assigned a dense rank of 1 (the first rank), and the next dense rank is 2 (value 5), then 3 (value 3), and so on.

Rank Percentile Calculation

Along with the RankDense calculation, we also have the RankPercentile calculation. RankPercentile ( [column], “direction”). Use this formula to assign percentile ranks to values based on their position within the distribution of values (Total sales by the state in the below example). Scroll up to Step 1 of the previous calculation if you need a reminder of how to add a new column. This is an excellent route to take when you want to analyze how a particular value ranks within the distribution of the column’s value. 

Option 1 image:

Option 2 image:


To wrap it up, Top N Filters and rank calculations help analyze values because they help to identify and prioritize important data points. By setting a filter to display only the top or bottom N values, analysts can quickly focus on the most significant data/values and make informed decisions. In addition, the rank calculation can help with understanding the relative importance of each data point in a set, especially when there are ties/duplicates in the data.

Adding a Top N Filter or a rank calculation into your data tables and dashboards can help when dealing with large data sets, where it may be challenging to identify the most important values. Using these options helps trim down your data to focus on what is truly important and make informed, data-driven decisions.

Interested in learning more about the filter options Sigma offers? Dive into Understanding Sigma Date Filters.

Interested in learning more about the filter options Sigma offers? Reach out to our team of experts.

Frequently Asked Questions

Yes, Top N Filters can be applied to any data type with a measurable metric or dimension.

Top N Filters work by sorting data on a specific measure or dimension and then selecting the top or bottom N items based on the chosen rank.

The filter hierarchy is determined based on the order in which the filters are applied. The topmost filter in the list takes precedence over the subsequent filters.

For example, if you have 3 metrics; A, B, and C. You apply a Top N Filter on A to show the top 10 values, the top 5 on B, and the top 3 on C. In this scenario, C will precede A and B because it is filtering to the lowest amount. Next will be B, filtered to 5, and last will be A because it is filtered to 10.

Yes. There are some things to consider when using rank calculations. First, consider your dataset and determine if it will be helpful to use. Second, a  rank calculation may also not be the best practice if you are dealing with a more granular data set or if some aggregations or filters need to be applied properly. It’s best to use a rank calculation for straightforward data that will benefit from rankings being added.

Data Coach is our premium analytics training program with one-on-one coaching from renowned experts.

Accelerate and automate your data projects with the phData Toolkit