KB14700: The percentile metric does not appear to calculate the correct results when the data set has multiple values at the lowest level on a report in MicroStrategy Engine

SYMPTOM:
Consider the data set shown below. This shows an example of a data set where users may sometimes see that the value returned by the percentile metric does not match the value calculated for a given data in MicroStrategy Engine:

Dataset for this example are the scores obtained for a student per test, as shown below:

Student
Test
Score
1
101
20
1
102
50
1
103
47
1
104
78
1
101
83
1
201
62
1
202
92
1
203
79
1
204
88
1
301
57
When this data set is sorted by score, the data set is as follows:

Student
Test
Score
Rank
Percentile Rank
1
101
20
0
0
1
103
47
1
11.11
1
102
50
2
22.22
1
301
57
3
33.33
1
201
62
4
44.44
1
203
78
5
55.56
1
104
79
6
66.67
1
101
83
7
77.78
1
204
88
8
88.89
1
202
92
9
100
Calculating the 70th percentile for this dataset, directly from the data, as per the description of the calculation in the article referenced above, obtains a value as follows:
n = 9
P = 0.70
Rank1 = 6 @ 66.67
Rank2 = 7 @ 77.78
[(0.7 * 9) - 6] / [7 - 6] = [x - 79] / [83 - 79]
x = 80.2
However when this is calculated in a MicroStrategy report as shown below, the value for the 70th percentile score appears calculated to a different value. Note that the Percentile metric is defined from the fact 'Score', not the metric, as shown below:

CAUSE:
The reason for this discrepancy can be seen in the base dataset shown above. The student has taken the Test '101' twice and has two distinct scores for this test.

When the analytical engine sees multiple values for the same dataset, it will pick the first value that its reads from the data set and apply it to the remaining calculations - in this case the rank and percentile calculations. This can be seen if a report is generated in MicroStrategy showing the rank and percentage rank calculated as shown below. Note that there are only eight ranks in the calculated set as opposed to the nine ranked elements in the raw data as shown above:

Since the Percentile metric is very sensitive to changes in the rank and value of the returned dataset, this metric is calculated incorrectly.

NOTE: The values calculated by the analytical engine are correct if the user would like that the fact data be grouped for the percentile metric calculation

ACTION:
To allow the analytical engine to differentiate among values where all the other attribute keys on the template are common, the metric values must be modeled as attributes. This attribute can then be used in the sum and percentile metric and the desired value will be obtained when the report is executed.

The steps to achieve this are explained with the same example described above:

The user should create an attribute based off the same column as that of the fact used for the percentile calculation. In this example, the 'Score' column in the table is used, as shown below:

Now create the 'Score' metric at a dimensionality that includes the 'Score' level, as shown below. This will force the analytical engine to consider the uniqueness of the score data.

The schema will need to be updated to use the new dimensionality. Now simply create a Percentile metric based off this dimensional metric, as shown below

Now using this metric in a report will return the correct results, as shown below

Comment

0 comments

Details

Knowledge Article

Published:

January 31, 2024

Last Updated:

January 21, 2026

Student	Test	Score
1	101	20
1	102	50
1	103	47
1	104	78
1	101	83
1	201	62
1	202	92
1	203	79
1	204	88
1	301	57