BLOG CALCULATING QUARTILES WITH DAX AND POWER BI

12-05-2019  0 Comment(s)

The PERCENTILEX.INC function returns the number at the specified percentile. So for example, if I had numbers 0 and 100 in my data set, the 25th percentile value would be 25. The 50th percentile value would be 50 and the 75th percentile value would be 75, and you can figure out what the 100th percentile value would be.

If I want to break my rushers into quarters so I can understand how they compare to the rest of the rushers in the NFL at their position in a given season, calculating the quartiles could be a good way to do that.

First, I create a calculated column. And I want to write an expression that calculates the 75th percentile value of rushing yards, which is also the upper third quarter boundary.

Quartile Rank by Rushing Yards = PERCENTILEX.INC(‘Stats’,[Rush-Yds],.75)

This provides me with my 3rd quartile upper boundary.

There’s a problem though. My dataset contains rushing stats for multiple years, so I’d like to calculate quartiles within the context of a single season. Also, it doesn’t make sense to compare the rushing stats of running backs to quarter back or wide receivers or tight ends. So I need to edit my calculation to only calculate the quartile for the current players position and season. To accomplish this I need to use a Filter function and the Earlier function as seen here.

Quartile Rank by Rushing Yards = PERCENTILEX.INC(
FILTER(
FILTER(‘Stats’,
[Season] = EARLIER([Season])
),
[Position] = EARLIER([Position])
)
,[Rush-Yds],.75)

The filter function allows me to filter the Stats tables to a subset of records based on a condition and the function allows me to limit the subset of records returned from the Stats table to the current row’s season and position. I’m also filtering out players without any rushing yards

This calculation gives me the third quarter boundary. I could also create another calculation for the second quarter boundary using the following code:

Comment Here       