Introduction to Google Data Studio Function: PERCENTILE
Google Data Studio provides a wide variety of functions to delve into data and squeeze every ounce of information from it. Among these multitude of functions, let's demystify the PERCENTILE function today.
PERCENTILE function in Google Data Studio assists in extrapolating the value that falls at a specified percentile in a numerical dataset. This can be a perquisite in various scenarios, especially when you want to ascertain a certain threshold value below which a specified percentage of observations fall.
Syntax and Functionality
Syntax of PERCENTILE is as follows:
markdown
PERCENTILE(X, N)
The function takes two parameters:
- X - A numeric field or an expression
- N - A number (can be a decimal value as well) in the range of 1 through 99
If X is considered as a pool of numerical values, PERCENTILE dives into this pool and fetches the value residing at the 'Nth' percentile.
However, there exists a limitation that X cannot be an aggregated field or the result of an aggregation function.
Examples
Let's comprehend the functionality of this function through an example that revolves around sales metrics.
Assume that your data contains a field labeled 'Daily Sales Revenue'. Now, let's see how PERCENTILE can be used to extract valuable insights from this data.
Example 1: We want to find the value at the 70th percentile.
markdown
PERCENTILE(Daily Sales Revenue, 70)
In this scenario, the function would return the daily sales revenue value below which 70% of daily sales revenue numbers fall.
Example 2: To add a little twist, N can also be a decimal. For instance, let's find the value at the 80.5 percentile of daily sales revenue figures,
markdown
PERCENTILE(Daily Sales Revenue, 80.5)
This would yield the sales revenue number below which 80.5% of the daily sales revenue numbers fall.
Limitations
The PERCENTILE function is powerful, but it holds some limitations. As mentioned earlier, X can neither be an aggregated field nor the result of an aggregation function. Along with this, whatever value you input for N, it must rest between 1 and 99, inclusive.
Tips
- PERCENTILE function can be a powerful tool to gauge and segment data. It can be used to understand the dispersion and skewness in the dataset. For instance, if PERCENTILE(X, 50) (a.k.a median) is significantly less than PERCENTILE(X, 70), your dataset might be right-skewed.
- Make sure to filter the data correctly before applying the PERCENTILE function. Invalid or outlier values can significantly affect the percentile figures.
- Even though PERCENTILE doesn’t work on aggregated fields, pre-aggregated percentiles can be calculated at the data source and can be pulled into Google Data Studio.
Google Data Studio’s PERCENTILE function is a powerful tool in statistics, providing simple, eloquent, yet meaningful insights into the dataset. By understanding its functionality and constraints, one can utilize it to its utmost efficacy.
.png)
