Replacing NIL values in rows with zero - matrix table

[ronaldbalza2023]

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 🙂

Here are the measures.

P&L New = 
(
    CALCULATE (
        SUM ( 'Invoices'[Line Amount FX Calculation] ),
        FILTER (
            'Accounts',
            Accounts[Class] = "Revenue"
                || Accounts[Class] = "Expense"
        ), USERELATIONSHIP( Invoices[Invoice Line ID], 'Tracking Category CONNECTIONS'[ID])
    )
) - [P&L - Credit Notes] + [P&L (Journals) New]

P&L - Credit Notes = 
CALCULATE (
    SUM ( 'Credit Notes'[Line Amount Credit Note Calculation FX] ),
    FILTER (
        'Accounts',
        'Accounts'[Class] = "Revenue"
            || Accounts[Class] = "Expense"
    ), USERELATIONSHIP( 'Credit Notes'[Credit Note Line ID], 'Tracking Category CONNECTIONS'[ID])
)

P&L (Journals) New = 
CALCULATE ( -1* ( SUM ( 'Journals'[Net Amount FX] ) ),
    FILTER ( 'Journals', 'Journals'[Split] = "JOURNALS" ),
    FILTER (
        'Accounts',
        'Accounts'[Class] = "Revenue"
            || Accounts[Class] = "Expense"
    ), USERELATIONSHIP( Journals[ID], 'Tracking Category CONNECTIONS'[ID])
) 

[PaulDBrown]

Try adding + 0 at the end of the measures. As in:

0 for blank = [your measure] + 0

(obviously you can just add the "+ 0" expression to the actual measures; no need to create new ones)

[TheoC]

@ronaldbalza2023, did you try the following?

Measure = IF(ISBLANK([P&L]),BLANK(),IF(ISBLANK([P&L]),0,[P&L]))

The issue that may be causing the blank is that there may be blanks in your outputs that are valid blanks and therefore this measure will test to see if a blank exists, if it does, it will convert the blank to 0, otherwise deliver the output of the measure.  

Hope this helps

[ronaldbalza2023]

Hi @TheoC it works that the blank were replaced by zero. HOWEVER, there's this column appears with zero values in it and won't that to happen as can be seen on the snapshot below. Thanks very much for your time, really appreciated it.

[TheoC · Accepted solution]

@ronaldbalza2023, this will be my last post on this topic - I promise!

Go into Power Query, select the relevant columns that have null. Replace null with 0. Close and apply. It should fix the blank problem and you won't need to modify your measures.

If it doesn't work, best of luck with it all mate.