Forum Discussion

richardbadge's avatar
richardbadge
Icon for Helper I rankHelper I
8 years ago
Solved

Count records in Quartile

Hi,

 

I have a table in Power BI that has a list of employees with their hourly pay rate. What I would like to do is to return a count of employees in each quartile that will dynaimcally change based on any filters applied to the page.

 

 

I have been able to create a measure for each quartile value as follows, and this seems to work out the correct quartile values based on any slicers you choose such as "Job family"

 

Q1 = CALCULATE(PERCENTILE.INC(Sheet1[rate], .25), ALL(Sheet1[rate]))

Q2 = CALCULATE(PERCENTILE.INC(Sheet1[rate], .5), ALL(Sheet1[rate]))

Q3 = CALCULATE(PERCENTILE.INC(Sheet1[rate], .75), ALL(Sheet1[rate]))

Q4 = CALCULATE(PERCENTILE.INC(Sheet1[rate], 1), ALL(Sheet1[rate]))

 

but where I am now struggling is to create a measure that returns the count of employees within that quartile range.

 

I may be going about the completly the wrong way so any suggestions are greatfully received.