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
TSI
Advocate I
Advocate I

Table with IF function totals to zero

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:

 

Summary table.jpg

 

 

 

 

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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))

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

Meets Criteria = SUMX(Data,IF([% Female]>=0.3,1,0))

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!

 

Anonymous
Not applicable

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 👍

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.

Top Solution Authors