Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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
Hi @Anonymous
Sorry, i'm not clear about the calculation logic of 'Q1' and 'Q2'.
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 @Anonymous
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 @Anonymous,
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |