topic Re: How to ignore blanks when counting total percentage in DAX Commands and Tips

How to ignore blanks when counting total percentage

Anonymous — Tue, 19 Apr 2022 14:46:10 GMT

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,

Re: How to ignore blanks when counting total percentage

amitchandak — Tue, 19 Apr 2022 15:12:41 GMT

Anonymous , Try measure like

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

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

Re: How to ignore blanks when counting total percentage

Anonymous — Tue, 19 Apr 2022 15:32:38 GMT

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