# Excel DAX - Ranking and Comparing Values

If you want to show only the top n number of items in a column or PivotTable, you have the following two options −

• You can select n number of top values in the PivotTable.

• You can create a DAX formula that dynamically ranks values and then uses the ranking values in a Slicer.

## Applying a Filter to Show only the Top Few Items

To select n number of top values for display in the PivotTable, do the following −

• Click the down arrow in the row labels heading in the PivotTable.
• Click the Value Filters in the dropdown list and then click Top 10.

Top 10 Filter (<column name>) dialog box appears.

• Under Show, select the following in the boxes from left to right.
• Top
• 18 (The number of top values that you want to display. The default is 10.)
• Items.
• In the by box, select Medal Count.
• Click OK. The top 18 values will be displayed in the PivotTable.

• It is simple and easy to use.
• Suitable for tables with large number of rows.

• The filter is solely for display purposes.

• If the data underlying the PivotTable changes, you must manually refresh the PivotTable to see the changes.

## Creating a DAX Formula That Dynamically Ranks Values

You can create a calculated column using a DAX formula that contains the ranked values. You can then use a slicer on the resulting calculated column to select the values to be displayed.

You can obtain a rank value for a given value in a row by counting the number of rows in the same table having a value larger than the one that is being compared. This method returns the following −

• A zero value for the highest value in the table.

• Equal values will have the same rank value. If n number of values are equal, the next value after the equal values will have a nonconsecutive rank value adding up the number n.

For example, if you have a table ‘Sales’ with sales data, you can create a calculated column with the ranks of the Sales Amount values as follows −

```= COUNTROWS (FILTER (Sales,
EARLIER (Sales [Sales Amount]) < Sales [Sales Amount])
) + 1
```

Next, you can insert a Slicer on the new calculated column and selectively display the values by ranks.

• The ranking is done in the table and not on a PivotTable. Hence, can be used in any number of PivotTables.

• DAX formulas are calculated dynamically. Hence, you can always be sure that the ranking is correct even if the underlying data has changed.

• Since the DAX formula is used in a calculated column, you can use the ranking in a Slicer.

• Suitable for tables with large number of rows.