Forum Discussion

adavid999's avatar
adavid999
Icon for Helper V rankHelper V
6 years ago
Solved

simple 0 if blank

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 pos...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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

adavid999's avatar
adavid999
Icon for Helper V rankHelper V
6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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!