How to Fix Total Row is Displaying 0 Instead of Real Sum

Posted on by Summa Lai

Sample problem:

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. 

Solutions:

Create below two measures. The first one is for female proportion per country and the second one is for the 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))

Ref: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Table-with-IF-function-totals-to-zero/m-p/846114#M6545