How to ignore blanks when counting total percentage

[nv1950]

Hi,

I'm trying to calculate the overall total percentage it takes for account to be resolved. The measure used to calculate the volume for 6 month is using calculated column, which checks if there is a 6 month since the account went into debt.

Calculated column:

EOM6 = if (eomonth(Query1[DateIntoDebt],6) > EOMONTH(today(),-1),1,0)

Volume measure:

Mth6 Vol = calculate(count(Query1[AccountID]),filter( Query1[Cure_Status_Enhanced] in {"Mth 1", "Mth 0", "Mth 2","Mth 3","Mth 4", "Mth 5","Mth 6"} && Query1[EOM6] = 0)

Percentage measure:

Mth6 %2 = Divide('Measures (2)'[Mth6 Vol],[Total Volume Excluding Zero and Latest],0)

As for some accounts the 6 month is not passed, the percentage column is null and overall total percentage shows incorrectly, 64.13% instead of 80's, see below.

Is there any way of ignoring these blanks, so the overall total will be correct?

Thanks,

[amitchandak · Accepted solution]

@nv1950 , Try measure like

Mth6 %2 = calculate(Divide('Measures (2)'[Mth6 Vol],[Total Volume Excluding Zero and Latest],0), filter(Table,not(isblank([Mth6 Vol]))))

or

Mth6 %2 = calculate(Divide('Measures (2)'[Mth6 Vol],[Total Volume Excluding Zero and Latest],0), filter(values(Table[Month Year]),not(isblank([Mth6 Vol]))))

[nv1950]

Thank you. The second option worked for me. First one gave 100% for all the rows in the column.