Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How to replace blank values by zero in matrix

I have one column amount. and i am adding measure sum(amount) in matrix. but for some row column combination it is displaying blank so i want to replace those blank with zeros.

I tried measure

measure=sum(amount)

final amount=if(isblank(measure),0,measure)

 

but it is unnecessarily displaying columns which has no data. is there any way so that we will have those columns only which has valid data and replace blank with zeros. 

Need Help
Show and Tell

4 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

     

    Try 

     SUM( table[amount] ) + 0

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski



    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      adding zero in measure also leads to same result which i have shared. i want same functionality of power BI matrix visula which don't show unnecessary columns even if they are in range.

      I want to remove those columns has total 0(if i have not replaced blank with zeros i am getting expect result but our clients want zero instead of blanks).

      • Mariusz's avatar
        Mariusz
        Community Champion
        6 years ago

        Hi Anonymous 

         

        Try something like...

        IF(  
    CALCULATE( COUNTROWS( YourTable ), ALLEXCEPT( YourTable, 'DateTable' ) ) > 0,
    [YourMeasure] + 0
)

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        Mariusz Repczynski