Forum Discussion

adavid999's avatar
adavid999
Helper 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 positive] value is based on this measure:

 

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

 

Any help appreciated.

 

Many thanks,

 

A

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

5 Replies

  • 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
      Helper 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!

         

         

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    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

  • amitchandak's avatar
    amitchandak
    Super User
    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