Hi everyone, how can I replace the rows in Matrix Table having blank/nil values with zero - which is currently greyed out? The + 0 old trick doesn’t seem to work. Thanks in advance

Hi @Melissa , thanks for taking the time on this. It does replaced the blank with zero, however, there were columns that appeared with zero values in it as you can see on the snapshot below. Thanks again!

Hi @Harsh , thanks for taking the time on this. It does replaced the blank with zeroes, however, columns appears with zero values as shown on the snapshot. Thanks again!

When you use this type of technique which is suggested by me and @Melissa the columns which were blanks earlier in it’s entirety will also come-up or start showing. So now, what you can simply do is drag or adjust the width of the columns and hide them which are un-necessary in your case.

Alternatively, if you don’t want to “hide” columns, you can add another conditional test with an IF statement. For example let’s say in variable1 you determine (calculate) if a blank is returned at ‘column total level’ and in variable2 you perform your original calculation.
Now when the VAR1 returns a blank you want VAR2 else you want VAR2 +0, or inside COALESE or with an IF ISBLANK statement to return a 0 instead.

@Harsh , thanks for taking the time on this. Much appreciated. Yeah you are right, because if I hide the columns then If there’s a value in it (since it is a moving data) then there’s no point in hiding it.

Hi @Melissa , thanks for taking the time on this. Really appreciated it. As per my understanding on your suggestion, it would be look like this? Still doing the same (appears the column with zero)

Not quite, no. The pattern is more like this (used a simple example because you didn’t provide a sample PBIX):

Example (no blanks) =
VAR CurrentCalc =
SUM( [Quantity] )
VAR ColTotalCalc =
SUMX( [Quantity], ALL( table ) )
VAR Result =
IF( ISBLANK( ColTotalCalc ), CurrentCalc, CurrentCalc + 0 )
RETURN
Result

Replace with the following: CurrentCalc is your original measure that also returned blanks ColTotalCalc is a calculation at subtotal- or grandtotal level, whatever makes sense in your case. Note that you can expand this pattern to each subtotal level by creating more variables, if that’s required.

Hi @Harsh , @Melissa - thank you both for helping me out on this one. Really appreciated it. After spending time with it, figured out how to do this by using this measure.

P&L New =
VAR _ColSubTotal =
CALCULATE ( [P&L], ALL ( Accounts ) )
VAR _Result =
IF ( _ColSubTotal <> 0, [P&L] + 0 )
RETURN
_Result