Forum Discussion
Replacing NIL values in rows with zero - matrix table
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])
)
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.
4 Replies
@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, 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.
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.
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)
