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

     

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

 

TSI's avatar
TSI
Advocate I
6 years ago

Thank you Anonymous , this worked perfectly!

Appreciate your expertise 👍