Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous

 

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

Anonymous
Not applicable

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 @Anonymous

 

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

 

Best Regards,

Link

Anonymous
Not applicable

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 @Anonymous,

 

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

 

Best Regards,

Link

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.