cancel
Showing results for
Did you mean:
Helper I

## Count records below, within or above numerical thresholds

Hi all, I'm back with another newbie question!

I'd like to count the no of male and female employees with salary

1) below the entire population (male + female) lower quartile (inclusive)

2) between the entire population LQ and median (inclusive)

3) between the entire population median and UQ (inclusive)

4) above the entire population upper quartile

My dataset consists of 2 tables linked by an active relationship.

Table 1

 Employee ID Gender 100 Male 101 Female 102 Male 103 Female 104 Male 105 Female

Table 2

 Employee ID Salary 100 30,000 101 38,000 102 25,000 103 40,000 104 48,000 105 42,000

I've calculated total population quartiles by adding measures to Table 2, e.g. Q2 = PERCENTILE.INC('Table2'[Salary],0.5).

I've then tried to count the observations using the measure below, which is returns blank values.

Obs btw Q1&Q2 = CALCULATE(COUNTROWS('Table2'),FILTER('Table2','Table2'[Salary]<=[Q2]),FILTER('Table2','Table2'[Salary]>[Q1]))

Had the function worked, I would have placed this new measure under values in a matrix visual with gender under rows. I wonder if there's a better and quicker way to do this (one that works :-)).

Thank you

Giac

6 REPLIES 6
Community Support

Sorry, i'm not clear about the calculation logic of 'Q1' and 'Q2'.

Could you provide your expected output?

Best Regards,

Helper I

First of all, thank you for your reply and for agreeing to help me out. I'm trying to calculate the % of men and women in each statistical quarter.

I started by adding a measure for each statistical quarter of salary. As per your post, the Median (aka Q2) is 39k. Next, I want to count the no of females with a salary lower than Q2 but higher than Q1 (lower quartile). Finally, express that count as a % of all employees (men and females) with a salary lower than Q2 and higher than Q1. Does it make sense?

I thought this would be possible using a combination of COUNTROWS fn + FILTER fn. However, I'm not getting any meaningful results.

Community Support

Could you provide pbix file after removing sensitive information so that i can check it in my environment.

Best Regards,

Helper I

Hi Link, I'd be happy to, but I can't find the option to attach the pbix file to my response. Would you be able to walk me through the steps on how to do it?

Community Support

You can put the pbix file into the netbook and share the public link.

Best Regards,

Helper I

Sorry for the delay in my response - I was on holiday.

Giac

Announcements