---
title: "Use the RANK Function to Return the Rank of Items in a List or Metric"
slug: "rank-function"
description: "Learn to use the RANK function to determine a value’s position in a List or Metric, based on its size relative to other values."
updated: 2026-01-14T11:55:21Z
published: 2026-01-14T11:55:21Z
---

> ## Documentation Index
> Fetch the complete documentation index at: https://kb.pigment.com/llms.txt
> Use this file to discover all available pages before exploring further.

# RANK function

## Description

Returns the rank of a number in a List or Metric. The rank of a number is its size relative to other values.

## Syntax

`RANK(Source Block [, Group] [, Direction] [, Ties])`

- Source Block: this is the List or Metric value that you want to rank. It can have the following data types: number, integer, date, text or Dimension. When working with text, it will rank in alphabetical order.
- Group (optional): Dimension on which ranking will be reset for each item
- Direction (optional): `ASC` for ascending, `DESC` for descending
- Ties (optional): `MINIMUM` (by default), `MAXIMUM`, `SEQUENTIAL`, `AVERAGE`

> [!NOTE]
> If you need to skip one parameter in the function, such as `group` for instance, you can use a constant such as `0` , `false` or `""` instead.

## Return type

`Integer` (compatible with number)

## Resetting across multiple dimensions

When using the group parameter to reset the values for one dimension, you can just say the name of the dimension that you would like to reset. If you want to perform a reset across multiple dimensions, you must include a unique text or integer property and you must use the **&** symbol. The unique text property can be something along the lines of the Name or Code property.

For example, if you want to rank a metric and have values reset each month, your formula would look this.

`rank(MyMetricByMonthAndTeam, Month)`

If you wanted those values to reset for every month and every team, your formula would look like this

`rank(MyMetricByMonthAndTeam, Month.Name & Team.Name)`

## Using a Metric to define the group for resetting

You can also use a metric to define the group for resetting. The metric must be defined by the dimension that you are trying to reset on. For example, if I wanted groups to be reset by Country, my metric defining that group must use Country as a dimension.

Within the Metric defining group, Pigment will group together items that have the same value, any items with different values will create their own groups. You can use any data type to achieve this.

In this example, the Metric Defining Group has a Text data type. France and UK are in one group, Spain, US, Brazil are in a second group and China makes up its own group. Countries are grouped together because the text values are the same.

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/59d3fb33-c07d-42ea-b119-46adf8d6fc07.png)

## Examples

| **Case** | **Results** | **Return Type** |
| --- | --- | --- |
| `RANK(Month)` | returns a numerical value for each month in your Calendar in chronological order. If your fiscal year starts in April, 1 is returned for the first April in your Calendar. | `integer` (compatible with number) |
| `RANK('MyDimension')` | returns the rank of each Item in a Dimension. The ranking is equivalent to the ordering controlled by the [Manage Items](/v1/docs/sort-data-pigment) tool. | `integer` (compatible with number) |
| `RANK('Quantity Sold')` | returns the rank of each cell in the Metric called `Quantity Sold` | `integer` (compatible with number) |
| `RANK('Quantity Sold', month, asc, sequential)` | returns for each month (reset to 1 when switching month), the rank of each cell in the Metric called `Quantity Sold` | `integer` (compatible with number) |
| `RANK('Quantity Sold', 0, desc, sequential)` | returns the rank of each cell in the Metric called `Quantity Sold` in descending order, without grouping. |  |

**Excel equivalent**: [RANK(number,ref,[order])](https://support.microsoft.com/en-us/office/rank-function-6a2fc49d-1831-4a03-9d8c-c279cf99f723)