Parent Child Matrix Visual - Please help

[Sravika]

Hello,

I have two tables here Table A and Table B and the data is showing up incorrectly when I am using Matrix visual.

Table A

Parent	Child	Dept	Budget
A10	B1	Health Care	$100,000
A10	B2	Health Care	$20,000
A11	B3	Retail	$45,000

Table B

Parent	Dept	Expenses
A10	Health Care	$50,000
A11	Retail	$5,000

Expected Output should be (Using Matrix Visual)

Parent	Child	Budget	Expenses	Remaining
A10	B1	$100,000
	B2	$20,000
Total		$120,000	$50,000	$70,000
A11	B3	$45,000
Total		$45,000	$5,000	$40,000

What I am actually getting

Parent	Child	Budget	Expenses	Remaining
A10	B1	$100,000	$50,000	$50,000
	B2	$20,000	$50,000	-$30,000
Total		$120,000	$100,000	$20,000
A11	B3	$45,000	$5,000	$40,000
Total		$45,000	$5,000	$40,000

We have expenses at Parent level but the ouput I am getting is taking at child level. Example: A10 parent has $50,000 expenses but the ouput I am getting is taking $50,000 twice.  Please help.

Thank you,

Sravika 

[ryan_mayu · Accepted solution]

@Sravika 

you can try this

Expense = if(ISFILTERED('Table A'[Child ]),blank(),sum('Table B'[Expenses]))

remaining = if(ISFILTERED('Table A'[Child ]),blank(),sum('Table A'[Budget])-[Expense])

pls see the attachment below

[lbendlin · Accepted solution]

[ryan_mayu · Accepted solution]

@Sravika 

you can try this

Expense = if(ISFILTERED('Table A'[Child ]),blank(),sum('Table B'[Expenses]))

remaining = if(ISFILTERED('Table A'[Child ]),blank(),sum('Table A'[Budget])-[Expense])

pls see the attachment below