simple 0 if blank

[adavid999]

Hello I am doing a simple % calculation and would like to replace blanks with zero when blank is returned.

The calculation is 

=[2020 total positive] / [total 2020] * 100

The [2020 total positive] value is based on this measure:

=CALCULATE(count(2020[resp]), (2020[resp] = 10))

Any help appreciated.

Many thanks,

A

[amitchandak · Accepted solution]

@adavid999 , try like

divide([2020 total positive] , [total 2020],0)

You do not need *100, you can mark it as % column in format session.

You can add +0, but that will create a left join. All data will be displayed

[Pragati11]

Hi @adavid999 ,

You can try modifying your calculation in 2 ways:

1. IF( [2020 total positive] = BLANK(), 0, ([2020 total positive] / [total 2020]) * 100)
2. Or try using DIVIDE function -->  DIVIDE([2020 total positive], [total 2020], 0) * 100

Thanks,

Pragati

[harshnathani]

Hi @adavid999 ,

Create these measures

Measure =   DIVIDE ([2020 total positive] /( [total 2020] * 100))

COALSE  Measure = COALESCE([Measure],0)

Regards,

Harsh Nathani

[adavid999]

thanks @harshnathani the coalesce function looks like it would work but i don't appear to have it in excel power pivot

[harshnathani]

Hi @adavid999 ,

Create a custom Column in Power Query

= if [2020 total positive] = null then 0 else [2020 total positive] / ([total 2020] * 100)

or 

= if [2020 total positive] = blank then 0 else [2020 total positive] / ([total 2020] * 100)

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!