Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi there,
I'm new to DAX and am probably making a mistake because I'm applying Excel logic!
I have employee data, which I used to create a table with headcounts and a measure of % Female:
The tricky part is when I want a count of how many countries have % Female >= 30%. I added a measure:
Meets Criteria = IF ((Female HC]/ [Total HC]) >= 0.3, 1, 0)
It works fine in the table, but the total is 0, when it should be 2.
Could someone give me a hand here? Thank you!
Solved! Go to Solution.
Hi @TSI
please create below two measures. first one is for female proportion per country an second one is for count of countries where female proportion is greater than equal to 0.30.
_femaleProportion =
VAR _female = CALCULATE(SUM(Headcount[Female HC]),ALLEXCEPT(Headcount,Headcount[Country]))
VAR _total = CALCULATE(SUM(Headcount[Total HC]),ALLEXCEPT(Headcount,Headcount[Country]))
RETURN DIVIDE(_female,_total,0)
FinalResult = CALCULATE(DISTINCTCOUNT(Headcount[Country]),FILTER(Headcount,[_femaleProportion]>=0.30))
Hi @TSI
In case of total row you are dividing 230 by 1030 which is 0.22 hence you are getting result as 0. In case you want to get sum of rows where criteria is satisfied please use
Hi @Anonymous
Thanks for the reply. I tried the SUMX formula, but ended up with Female headcount instead.
Do you think it's because I am applying 'Meet Criteria' at a summarised level (i.e. it's at Country level), and not employee row level?
Appreciate your help!
Hi @TSI
please create below two measures. first one is for female proportion per country an second one is for count of countries where female proportion is greater than equal to 0.30.
_femaleProportion =
VAR _female = CALCULATE(SUM(Headcount[Female HC]),ALLEXCEPT(Headcount,Headcount[Country]))
VAR _total = CALCULATE(SUM(Headcount[Total HC]),ALLEXCEPT(Headcount,Headcount[Country]))
RETURN DIVIDE(_female,_total,0)
FinalResult = CALCULATE(DISTINCTCOUNT(Headcount[Country]),FILTER(Headcount,[_femaleProportion]>=0.30))
Thank you @Anonymous , this worked perfectly!
Appreciate your expertise 👍
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |