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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi adavid999 ,

 

Create these measures

 

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

 

COALSE  Measure = COALESCE([Measure],0)
 
 
 
Regards,
Harsh Nathani
  • 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!