How to Calculate Quartiles in Power BI (With Example)

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.

If you’d like to view the values for the quartiles, insert card visualizations into a Report View:

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 here.

Additional Resources

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

Power BI: How to Calculate Percent of Total by Category
Power BI: How to Calculate Percent Change
Power BI: How to Calculate Median

Leave a Reply

Your email address will not be published. Required fields are marked *