Ranking Data

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Apply a ranking to show top values

Ranking

Ranking allows you to isolate the top and/or bottom records in a set based on a variety of criteria.

For example, if you have a block showing countries and associated revenues, ranking allows you to rank the data in the block to show the top 3 countries only, based on the revenue they generate.

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?

The following parameters can be configured in the Ranking dialog box:

ParameterDescription
Top / Bottom

Allows you to choose the number of Top values (sorted descending) or Bottom values (sorted ascending).

Based on

The 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.

Note
In SAP BI 4.3, you can rank data in Reading mode.

Log in to track your progress & complete quizzes