Replacing blank values with zero

[ronaldbalza2023]

Hi everyone, having a hard time trying to figuring out this one and appreciated the help!

Question: How can I replace the blank/empty rows with zero? I highlighted the empty rows with grey for the meantime and wanted to replace it by zero.

This measure removes the columns that has empty values

P&L (No Blanks) = CALCULATE( IF( [P&L] = 0, BLANK(), [P&L]))

 These are the measures of P&L

P&L = 
(
    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)]

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) = 
CALCULATE ( 0 - ( 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])
) 

[AllisonKennedy · Accepted solution]

@ronaldbalza2023 You're converting the 0s to blanks with the P&L (no blanks) measure. Are you saying you want that back to 0? Can you simply use the P&L measure?

The problem is, that you have a matrix so it will replace ALL rows with zero - how do you specify which rows you want to show or not?

You may be able to work with an IF() statement, but we need to know which columns you're using in that matrix visual please and you could use that context to filter out rows where the total row is blank, otherwise put 0 - is that what you mean?

[AllisonKennedy · Accepted solution]

@ronaldbalza2023  Sorry, I was trying with a simplified version of [P&L] but you're right it doesn't work in your measures. 

Try updating the [P&L (No Blanks)] measure and use that in your visual instead:

P&L (No Blanks) =

VAR _ColSubTotal = CALCULATE([P&L], ALL(DimAccount[Account Type), ALL(DimAccount[Name]))

VAR _Result =

IF(_ColSubTotal <> 0, [P&L])

RETURN _Result

I don't know what table the Account Type and Name are coming from, so you may need to update that part of the measure.

[AllisonKennedy · Accepted solution]

@ronaldbalza2023 I thought your [P&L] measure already had the $0 included in it? So the $0 should display if you've got the ColSubtotal part correct....

You could try adding the zero back in: 

P&L (No Blanks) =

VAR _ColSubTotal = CALCULATE([P&L], ALL(DimAccount[Account Type), ALL(DimAccount[Name]))

VAR _Result =

IF(_ColSubTotal <> 0, [P&L]+0)

RETURN _Result

[AllisonKennedy · Accepted solution]

That's because they are blank for that account type. Do you have a DimAccount table?

Can try replacing the ALL filters with just 

ALL(DimAccount)

[ronaldbalza2023]

Hi everyone, I wanted to replace the blank values with zero. How can I achieve that? The old trick + 0 seems not working. Here's my measures. Thanks for the help 🙂

P&L = 
(
    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)] + 0

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])
) + 0

P&L (Journals) = 
CALCULATE (
    0 - ( 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])
) + 0

[TheoC]

@ronaldbalza2023, just found something that may resolve the issue:

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

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

Hope this helps!

[TheoC]

Hi @ronaldbalza2023,

I would write the measure as such:

P&L = 
IF (
	(
		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)] ) = 0 , 0 ,
	(
    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)] )

[TheoC]

Hi @ronaldbalza2023,

The +0 works sometimes but not in all instances where filters are being used in meausres. I'd recommend doing the following:

MeasureName = IF ( [Your Measure] = 0 , 0 , [Your Measure] )

An example of it in use is:

Hope this helps 🙂

[ronaldbalza2023]

Hi @TheoC , thanks for taking the time on this. It works however, all the columns that has zero values appeared and the totals column on the far right disappeared. How can I removed the columns with zero values and bring back the totals column? I have filtered the values that is not 0. 

[TheoC]

Hi @ronaldbalza2023, did you remove the +0 from your measures?

[ronaldbalza2023]

@TheoC when I use blank(), it removes the columns with zeroes and the total columns appears. However, I wanted to fill these blanks here with zero.

[TheoC]

@ronaldbalza2023, try using 

IF ( ISBLANK ( [P&L] ) , 0 , [P&L] )

[ronaldbalza2023]

@TheoC yes Sir, I did. Still the same.

[TheoC]

If the below doesn't work, then there is something else that may be the issue mate. The logic is based on your formula, filters and relationships. All the adjustment is doing is saying return 0 if it's blank, else return the output.  Without having access to the PBIX, it will be difficult for me to be of any assistance and I'd be wasting your time... sorry @ronaldbalza2023 

P&L = 
IF (
	(
		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)] ) = 0 , 0 ,
	(
    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)] )

[TheoC]

Hi @ronaldbalza2023, can you remove the "Calculate" and just leave the IF...

[ronaldbalza2023]

@TheoC still doing the same. Nothing really happened.

[AllisonKennedy]

@ronaldbalza2023  I would maybe try using the [P&L] measure in your matrix values, and put the [P&L (no blanks)] in the Filters on this visual and filter for not blank.

[ronaldbalza2023]

@AllisonKennedy , thank you for taking the time on this. When I  am using the [P&L] measure as you can see below snapshot, empty columns with zero values appears and the Total Column on the far right of the table disappear.

Now, with this case, I wanted to remove the columns that are having with zeros/empty values.

[AllisonKennedy]

@ronaldbalza2023  Now just add the P&L (No Blanks) to your matrix under 'filters on this visual' - you'll need to drag and drop it in manually: 

[ronaldbalza2023]

@AllisonKennedy apologies, forgot to mention I did it as well with no luck. 

[AllisonKennedy · Accepted solution]

@ronaldbalza2023  Sorry, I was trying with a simplified version of [P&L] but you're right it doesn't work in your measures. 

Try updating the [P&L (No Blanks)] measure and use that in your visual instead:

P&L (No Blanks) =

VAR _ColSubTotal = CALCULATE([P&L], ALL(DimAccount[Account Type), ALL(DimAccount[Name]))

VAR _Result =

IF(_ColSubTotal <> 0, [P&L])

RETURN _Result

I don't know what table the Account Type and Name are coming from, so you may need to update that part of the measure.

[ronaldbalza2023]

Hi @AllisonKennedy  really appreciated your time taking on this. It removes the column with zero values thanks. How can I now replace the nil/blank rows with zero? Thanks so much!

[AllisonKennedy · Accepted solution]

@ronaldbalza2023 I thought your [P&L] measure already had the $0 included in it? So the $0 should display if you've got the ColSubtotal part correct....

You could try adding the zero back in: 

P&L (No Blanks) =

VAR _ColSubTotal = CALCULATE([P&L], ALL(DimAccount[Account Type), ALL(DimAccount[Name]))

VAR _Result =

IF(_ColSubTotal <> 0, [P&L]+0)

RETURN _Result

[ronaldbalza2023]

Hi @AllisonKennedy , doing this getting back the rows with zeroes, however, there's some rows were empty which is weird.