cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fizzy_Mojito
Helper I
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 IDGender
100Male
101Female
102Male
103Female
104Male
105Female

 

Table 2

Employee IDSalary
10030,000
10138,000
10225,000
10340,000
10448,000
10542,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
v-xulin-mstf
Community Support
Community Support

Hi @Fizzy_Mojito

 

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

v-xulin-mstf_0-1619420785754.png

 

Could you provide your expected output?

 

Best Regards,

Link

Hi Link,

 

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. 

Hi @Fizzy_Mojito

 

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

 

Best Regards,

Link

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?

Hi @Fizzy_Mojito,

 

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

 

Best Regards,

Link

Hi Link,

 

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

 

Please find link to dummy data here: https://drive.google.com/file/d/12o7sSqQ0_IGKBoNe70StwLMjNY4j9vHi/view?usp=sharing

 

Thank you in advance for your help

Giac

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Top Solution Authors
Top Kudoed Authors