Watch this video to learn about Ranks.
Ranking allows you to answer business questions such as:
- Which 3 regions generate the most revenue?
- Which are the bottom 10% of stores in terms of revenue generation?
- What is the group of the best-performing stores that generates a combined revenue of up to $10,000,000?
You can rank data in many ways to answer these kinds of business questions. You can:
- Rank a number of top and/or bottom records by dimension (for example Country) based on the sum of a related measure (for example Revenue).
- Rank the top and/or bottom records by the percentage of the total number of records by dimension, based on the value of a related measure as a percentage of the total value of the measure.
- Rank a number of top and/or bottom records by dimension based on the cumulative sum of a related measure.
- Rank a number top and/or bottom records by dimension based on the value of a related measure as a cumulative percentage of the total value of the measure.
Add a Ranking
Ranking Parameters
The following parameters can be configured:
| Option | Description |
|---|---|
| Ranked By | The ranking dimension. If you specify a ranking dimension, the aggregated values of the Based on parameter, calculated for the dimension, determine the ranking. If you don't specify this dimension, the values of the Based on parameter calculated for all dimensions in the block determine the ranking. In other words, the ranking returns the top/bottom number of rows in the block, based on the measure. The ranked by dimension doesn't need to be part of the block where the ranking is applied. However, in this case, the ranked data can't be sorted. |
| Calculation mode | When the calculation mode is Count, the ranking returns a number of top/bottom records based on the measure specified as the Based on parameter. For example, the top 3 countries by revenue generated, the bottom 3 year/quarter combinations by revenue generated. When the calculation mode is Percentage, the ranking returns the top / bottom percentage of the total number of records based on the measure specified as the Based on parameter. For example, if there are 100 records and you rank the top 10%, the ranking returns the top 10 records. When the calculation mode is Cumulative Sum, the ranking returns the top/bottom records, for which the cumulative sum of the measure specified as the Based on parameter doesn't exceed the value specified in the top/bottom. When the calculation mode is Cumulative Percentage, the ranking returns the top/bottom records for which the cumulative sum of the measure specified as the Based on parameter doesn't exceed the percentage of the total measure, specified in the top/bottom. |
Procedure: To Create a Ranking
Follow these steps to create a ranking:
- Select the table.
- Choose Build Side Panel → Report Element Data tab → Ranking sub-tab.
- Choose Add a Rank.
- Check Top and/or Bottom to rank the highest and/or lowest records in the block, and select the number of records you want to rank with the - and + signs.
- Open the Based on list, select the measure on which the ranking is based.
- If you want to rank by a specific dimension in the block, select it in the Ranked by list.
- Select a calculation mode.
- Choose OK.
Ranking and Data Order
You cannot rank an object whose values depend on the data order because the ranking changes the data order.
If the data order is changed, the object data is changed, and this results in a recalculation of the ranking. The result is a circular dependency between the ranking and the object that cannot be resolved.
If you create a rank on a measure whose values depend on the data order, for example a measure that uses the Previous function, the #RANK error code appears in all cells in the block.
