Forum Discussion

TSI's avatar
TSI
Advocate I
6 years ago
Solved

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:

 

 

 

 

 

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!

if
Summary
Table
total zero
  • Anonymous's avatar
    Anonymous
    6 years ago

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

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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))
    • TSI's avatar
      TSI
      Advocate I
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

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