Ranking Data

Objective

After completing this lesson, you will be able to apply a ranking to show top values.

Ranking

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.

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.

Ranking Parameters

ParameterDescription
Top / BottomAllows you to choose the number of Top values (sorted descending) or Bottom values (sorted ascending).
Based onThe measure on which the ranking is based.
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.

Database Ranking

Database ranking allows you to restrict the records returned from the database based on a variety of criteria.