Percentile calculation ranks the results returned from a database in percentile form, allowing users to observe the position of a data point within the entire dataset. This topic describes how to configure percentiles.
Prerequisites
You have created a dashboard. For more information, see Create a Dashboard.
A dataset is selected and chart fields are configured. For more information, see Configure Chart Fields.
Background Information
Percentile is another way to represent ranking. First, a set of data needs to be sorted, and then calculated according to the following formula:
Where 
represents the calculated percentile, 
represents the total number of data points, and 
represents the rank of the data point.
For example, the percentile calculation results for a set of data are as follows:
Raw data | 20 | 10 | 30 | 40 | 50 | 20 |
Ascending rank | 3 | 1 | 4 | 5 | 6 | 3 |
Ascending percentile | 40% | 0% | 60% | 80% | 100% | 40% |
Descending rank | 5 | 6 | 3 | 2 | 1 | 5 |
Descending percentile | 80% | 100% | 40% | 20% | 0% | 80% |
When identical data exists, sparse ranking is used: 1, 3, 3, 4, not continuous ranking: 1, 2, 2, 4.
Usage Notes
Totals and subtotals do not participate in cumulative calculations and cannot coexist with cumulative calculations. If cumulative calculation is selected, totals and subtotals will automatically be disabled (displayed as "-").
Null values are not included in cumulative results and do not affect other data results. Their own results remain null (displayed as "-").
This analysis method is based on the returned result set calculation. The current limit on the public cloud is 10,000. If the total data returned exceeds 10,000, the calculation results may not meet expectations.
Charts that support percentiles are shown below.
Percentile ranking method
Description
Supported chart types
Overall percentile
Sort the overall data first, then calculate the percentile.
Line/area charts: line chart, area chart, stacked area chart, 100% stacked area chart, combination chart.
Column/bar charts: column chart, stacked bar chart, 100% stacked bar chart, circular column chart, ranking board, bar chart, stacked bar chart, 100% stacked bar chart, waterfall chart, bullet chart, box plot, histogram.
Bubble/scatter charts: bubble chart, scatter chart, facet scatter chart.
Table charts: cross table, detail table, heat map.
Pie/ring charts: pie chart, rose chart, radar chart, treemap chart.
Geographic charts: color map, bubble map, heat map, flow map, symbol map.
Metric charts: metric card, metric trend chart.
Funnel/conversion charts: funnel chart, comparison funnel chart.
Other charts: word cloud.
In-group percentile
Sort the data within its group first, then calculate the percentile.
Table charts: cross table.
Configuration Entry
In the Fields panel of the chart, find the measure field that needs to use cumulative calculation, click the 
icon on its right, and select Advanced Calculation > Percentile from the dropdown list. Then set the percentile calculation method according to your business scenario.
The percentile calculation methods available for cross tables differ from those for other charts. The specific descriptions are as follows.
Chart type | Percentile calculation method | Limitation | Calculation logic | |
Cross table | Ascending | / | Ranks the overall data from smallest to largest based on the metric value, then calculates the percentile. | |
Descending | / | Ranks the overall data from largest to smallest based on the metric value, then calculates the percentile. | ||
In-group ascending | / | Groups by the finest granularity, ranks data points from smallest to largest within the group, then calculates the percentile. For example: with region, province, and city dimensions, cities are grouped by their respective province and region, then ranked in ascending order before calculating the percentile. | ||
In-group descending | / | Groups by the finest granularity, ranks data points from largest to smallest within the group, then calculates the percentile. | ||
Custom | By column percentile | Dimension fields in the Row area | Treats each column as an independent analysis unit, sorts data points within the column total, then calculates the percentile. | |
By column in-group percentile | Two or more dimension fields in the Row area | Sorts data points within their column group, then calculates the percentile. The grouping logic is: starting from the first dimension in the row area up to the selected grouping dimension, all dimensions in between are treated as a whole for grouping operations. For example, in the figure below, the Region and Province fields will be matched for grouping operations, and sorted by the Province dimension before calculating the percentile. | ||
By row percentile | Dimension fields in the Column area | Treats each row as an independent analysis unit, sorts data points within the row total, then calculates the percentile. | ||
By row in-group percentile | Two or more dimension fields in the Column area | Sorts data points within their row group, then calculates the percentile. The grouping logic is: starting from the first dimension in the column area up to the selected grouping dimension, all dimensions in between are treated as a whole for grouping operations. For example, in the figure below, the Product Type field will be matched for grouping operations, and data will be sorted within this dimension before calculating the percentile. | ||
Other charts | Ascending | / | Ranks the overall data from smallest to largest based on the metric value, then calculates the percentile. | |
Descending | / | Ranks the overall data from largest to smallest based on the metric value, then calculates the percentile. | ||
Examples
The following examples help you better understand percentile calculations.
Example 1: Metric card
This example demonstrates percentile calculation in a metric card.
Log on to the Quick BI console.
Follow the steps shown in the figure below to access the dashboard editing page.
In the top menu bar, click Add Chart, find Metric Card, and click or drag the metric card to the dashboard area.
In the Data Panel's Fields tab, configure the metric card data.
Select the required dimension fields and measure fields, and double-click or drag them to the target area.
In the Dimensions list, find Region, and double-click or drag it to the Row area.
In the measures list, find Order amount, and double-click or drag it twice to the column area.
NoteThe two Order amount fields in the column area, one displays the raw data and the other is set for cumulative calculation.
When there are two duplicate fields placed in the measure area, you will see a "Duplicate measure exists" prompt. You can ignore this for now and update the data after the configuration is complete.
Click the
icon to the right of the second Order amount field, and select Advanced Calculation > Percentile. You can set different percentile calculation methods for the current chart as needed. Specific examples are as follows.
Percentile calculation type
Description
Example effect
Ascending
Overall data is ranked from smallest to largest, then percentile is calculated. In this example, the region with the largest order amount is ranked last. The South China region has the largest order amount, is ranked last, and has a percentile of 100%.
Descending
Overall data is ranked from largest to smallest, then percentile is calculated. In this example, the region with the smallest order amount is ranked last. The Southwest region has the smallest order amount, is ranked last, and has a percentile of 100%.
Example 2: Cross table
The percentile calculation methods available for cross tables differ from those for other charts. Specific examples are as follows.
Percentile calculation type | Configuration description | Example effect | |
Ascending | Overall data is ranked from smallest to largest before calculating the percentile, so the largest order amount is ranked last. For example: Guangdong Province has the largest order amount, is ranked last, and has a percentile of 100%. |
| |
Descending | Overall data is ranked from largest to smallest before calculating the percentile, so the largest order amount is ranked first. For example: Guangdong Province has the largest order amount, is ranked first, and has a percentile of 0%. |
| |
In-group ascending | This example groups provinces by region dimension, and ranks order amounts from smallest to largest within the region dimension before calculating the percentile. For example: In the Northeast region, Heilongjiang Province has the smallest order amount, is ranked first, and has a percentile of 0%. |
| |
In-group descending | This example groups provinces by region dimension, and ranks order amounts from largest to smallest within the region dimension before calculating the percentile. For example: In the Northeast region, Heilongjiang Province has the smallest order amount, is ranked last, and has a percentile of 100%. |
| |
Custom In this example, the order is descending | By column ranking | This example ranks the overall data by single column in descending order before calculating the percentile, so provinces with larger order amounts are ranked higher and have smaller percentiles. For example: In the truck transportation method, Liaoning Province has a larger order amount than Jilin Province, so Liaoning Province has a smaller percentile than Jilin Province. |
|
By column in-group ranking | In this example, the first dimension field in the Row area is Region, and the grouping dimension is Province, so grouping is done by matching the Region and Province fields. Data is sorted in descending order before calculating the percentile. For example: In Jilin Province, Changchun City's truck transportation has the largest order amount, is ranked first, and has a percentile of 0%. |
| |
By row ranking | This example ranks the overall data by single row in descending order before calculating the percentile, so transportation methods with larger order amounts are ranked higher and have smaller percentiles. For example: In Jilin Province, the train transportation method has the largest order amount, is ranked first, and has a percentile of 0%. |
| |
By row in-group ranking | In this example, both the first dimension field and the grouping dimension in the Column area are Product Type, so grouping is done by the Product Type dimension. Data is sorted in descending order before calculating the percentile. For example: In Jilin Province's office supplies order amount, the train transportation method has the highest order amount, is ranked first, and has a percentile of 0%. |
| |
