What are the steps to calculate quartiles in Power BI?

By /

Quartiles are a statistical measure that divides a dataset into four equal parts, with each part representing 25% of the total data. In Power BI, there are specific steps to follow in order to calculate quartiles accurately.

The first step is to sort the data in ascending order. This can be done by creating a new column and using the ‘Sort by Column’ function.

Next, determine the total number of data points in the dataset. This can be done by using the ‘COUNT’ function in Power BI.

Then, calculate the first quartile (Q1) by taking the median of the first half of the data points. This can be achieved by using the ‘MEDIAN’ function and specifying the range of data points from the first data point to the median.

Similarly, calculate the third quartile (Q3) by taking the median of the second half of the data points.

Finally, to calculate the second quartile (Q2), simply take the median of the entire dataset.

By following these steps, one can easily calculate quartiles in Power BI and gain valuable insights into the distribution of their data.

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.

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 do you calculate quartiles in Power BI, with an example?
  2. How to calculate quartiles in pandas?
  3. How do you Calculate Quartiles in PySpark?
  4. What are the steps to return all rows greater than a specified date in MySQL?
  5. How to Find Quartiles in Even and Odd Length Datasets
  6. How do you calculate YTD (Year to Date) values in Power BI?
  7. How do I calculate the number of days in a month using Power BI?
  8. How Can I Calculate the Sum Using a Filter in Power BI?
  9. How do you calculate the sum in Power BI with an example?
  10. How do you calculate percent change in Power BI?