How to handle nulls without the extra 0's

[Anonymous]

Hello,

I have the following data set

Branch	Budget Category	CAD Amount
Edmonton	Loan	8331
Edmonton	Deposit	8991
Calgary	Loan	8940
Vancouver	Loan	6208
Yellow Knife	Loan	9703
London	Loan	8186
Victoria	Loan	4453
Toronto	Deposit	2271

Expected output

A

Branch	Total Deposits
Edmonton	8991
Toronto	2271

B

Branch	Total Deposits	Total Loans
Edmonton	8991	8331
Calgary	0	8940
Vancouver	0	6208
Yellow Knife	0	9703
London	0	8186
Victoria	0	4453
Toronto	2271	0

I can get the output A with the following code

Total Deposits= CALCULATE(SUM(Sheet1[Value]), FILTER(Sheet1,Sheet1[Budget Category]="Deposit") )

but this does not help me achieve the output b, it returns blanks because the values dont exist.

Edmonton	8991	8331
Calgary		8940
Vancouver		6208
Yellow Knife		9703
London		8186
Victoria		4453
Toronto	2271

but when I try to handle nulls using the below code I get extra 0's as shown below if I only show Total Deposits. I want the output A & B.

Total Deposits =

VAR tot = CALCULATE(SUM(Sheet1[Value]), FILTER(Sheet1,Sheet1[Budget Category]="Deposit") )

RETURN IF(ISBLANK(tot),0,tot)

Branch	Total Deposits
Edmonton	8991
Calgary	0
Vancouver	0
Yellow Knife	0
London	0
Victoria	0
Toronto	2271

 I don't want the 0's to show up. I should only get 0's when I add Total Loans similar to Expected Output B.

[mussaenda]

If I am not mistaken, you can filter the total deposits in filters pane to something like this (refer to picture)

Assumming that the document type is your total deposits column. You can apply it on the visual level filters too if you want

[Anonymous]

Hi @mussaenda , I can but I was looking for 1 formula to be able to do both

[v-danhe-msft]

Hi @Anonymous ,

From your description, I could not understand I want the output A & B. It seemed hard to show the A and B in the same visual, if you want to show with different tbale, you could refer to below steps:

A is your formula:

Create below measues for B:

Total Deposits +0 = CALCULATE(SUM(Table1[CAD Amount]), FILTER('Table1','Table1'[Budget Category]="Deposit") )+0

Total Loans+0 = CALCULATE(SUM(Table1[CAD Amount]), FILTER('Table1','Table1'[Budget Category]="Loan") )+0

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous]

@v-danhe-msft  I want to use the same formula for both A & B.

[tarunsingla]

You may use same formula if you prefer (for whatever reason) and for the visual A, apply an visual level filter (Advanced) on Total Deposits to filter out the records where Total Deposits is blank or Total Deposits is 0.

[v-danhe-msft]

Hi @Anonymous ,

Sorry, I am afraid it could not use the same formula to not show 0 in A and show 0 in B.

Regards,

Daniel He