How do you calculate quartiles in Power BI, with an example?

By /

Quartiles are a statistical measure that divides a dataset into four equal parts, with each part representing 25% of the data. In Power BI, quartiles can be calculated using the PERCENTILEX.INC function. For example, if we have a dataset of sales data with 100 values, we can use the PERCENTILEX.INC function to calculate the first quartile (Q1) by inputting the dataset and the value 0.25. This will return the value at the 25th percentile, which is the first quartile. Similarly, we can calculate the second quartile (Q2) by inputting the value 0.5, and the third quartile (Q3) by inputting the value 0.75. These quartiles can then be used to understand the distribution of our data and identify any potential outliers.

In statistics, quartiles are values that split up a dataset into four equal parts.

When analyzing a distribution, we’re typically interested in the following quartiles:

  • First Quartile (Q1): The value located at the 25th percentile
  • Second Quartile (Q2): The value located at the 50th percentile
  • Third Quartile (Q3): The value located at the 75th percentile

You can use the following syntax in DAX to calculate quartiles for specific column in a table:

Q1 = PERCENTILE.INC(table_name[column_name], 0.25)
Q2 = PERCENTILE.INC(table_name[column_name], 0.5) 
Q3 = PERCENTILE.INC(table_name[column_name], 0.75)

The following example shows how to use this syntax in practice.

Example: How to Calculate Quartiles in Power BI

Suppose we have the following table named my_data in Power BI that contains information about various basketball players:

Suppose that we would like to calculate the quartiles for the Points column.

To calculate the first quartile, click the Table tools tab and then click the New measure icon:

Then type the following formula into the formula bar:

Q1 Points = PERCENTILE.INC(my_data[Points], 0.25)

This will create a new measure that contains the value of the 1st quartile of the Points column:

Next, repeat this process to create a measure for the 2nd and 3rd quartiles as well by replacing the 0.25 in the formula with 0.5 and 0.75, respectively.

From the output we can see:

  • The first quartile is located at 14.25.
  • The second quartile is located at 19.
  • The third quartile is located at 22.

By knowing only these three values, we have a good understanding of the distribution of values in the Points column.

Note: You can find the complete documentation for the PERCENTILE.INC function in DAX .

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI:

x

Related terms:

  1. How to calculate quartiles in pandas?
  2. How to Find Quartiles in Even and Odd Length Datasets
  3. How do you calculate YTD (Year to Date) values in Power BI?
  4. How to Perform Power Regression in Excel (Step-by-Step)
  5. How do I perform power regression in R?
  6. How to do power regression?
  7. How do you transpose a table in Power BI? (With example)
  8. How can Fuzzy Matching be performed in Power BI with an example?
  9. Statistical Power
  10. MINIMUM POWER THEORY