MicroStrategy ONE

PercentRank

This function is used to display the ranking of values as a percentage. For example you can rank profit for a particular region based on the profit for all regions.

The calculation can restart based on attributes identified in the parameter settings. This is an OLAP function.

You can also rank values in the following ways:

  • You use the Rank function (see Rank ) to rank values as an integer value. For example you can rank profit for five categories from 1 to 5.
  • You use the PercentRankRelative function (see PercentRankRelative) to rank values as a percentage, with the ranking based on another data set. For example you can rank profit for the current year based on last year's profit.

Syntax

PercentRank <inclusive, BreakBy> (Argument)

Where:

  • Argument is a fact or metric representing a list of numbers that are to be ranked.
  • inclusive is a TRUE/FALSE parameter that indicates whether the rank is inclusive or exclusive:
    • TRUE (default): The percent rank is in a range from 0 to 1 inclusive. This means that 0% and 100% are included.
    • FALSE: The percent ranks is in a range from 0 to 1 exclusive. This means that 0% and 100% are excluded.
  • BreakBy is the parameter that designates where the calculation should restart.

Example

The example report shown below displays revenue information across regions for a given quarter. You can use PercentRank to return the rank of revenue for each region during a given quarter.

The definition for the Percent Rank Revenue metric in this example is:

PercentRank<BreakBy={Quarter}>(Revenue)

The Percent Rank Revenue metric in the report shown above provides the rank of each revenue value. You can see that the highest value in the Northeast region is displayed as 100% while the lowest value in the Northwest region is displayed as 0%.

This report also includes a metric that uses the PercentRankRelative function, ranking the revenue based on last quarter's revenue. For information on PercentRankRelative and an explanation of these results, see PercentRankRelative.