Summing the children...but only sometimes

[teddy0bear]

I'm trying to build a pivot table that will allow the user to select one department and it will show all say 3 data fields for that department. In some cases, however, the department they select will have sub departments. There is data at both the department level and the sub department level which is making things difficult. I have a department table that has a relationship already set up (*) with the data table.
My first issue is, if I do the filter on the department level (from the Dpt table) it wont show the sub departments in the filter. If I apply the filter to the Sub department level (Dpt table) it wont sum up the departments correctly. I'm not sure how to handle this?

To make my life more difficult, when the user selects ones of the parent departments, the pivot table needs to show the sum of 2 of the fields but not the last one. I think I have to tell it to ignore the relationship so it wont do that? I haven't figured that part out yet...

Dpt Table
*Sub Department / Department
Red / Red
Pink / Red
Magenta / Red
Blue / Blue
Turquoise / Blue

Data Table
*Department / Value1 / Value2 / Value3
Red / 1 / 4 / 2
Pink / 2 / 4 / 3
Magenta / 2 / 4 / 0

For example, if I were to say Pink I should get
2 / 4 / 3

If I were to say Red, I'm trying to get:
5 / 12 / 2

 

[cyrilbrd]

Hi,
Maybe something like:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G	H	I	J	K	L
1	sub	dept		dpt	v1	v2	v3			v1	v2	v3
2	red	red		red	1	4	2		red	5	12	5
3	pink	red		pink	2	4	3
4	magenta	red		magenta	2	4	0
5	blue	blue
6	turquoise	blue
teddy0bear

Cell Formulas
Range	Formula
J2	{=IF(COUNTIF(dpt,$I2)=1,INDEX(E$2:E$4,MATCH($I2,$D$2:$D$4,0)),SUM(SUMIF(teddy0bear!$D$2:$D$4,IF(teddy0bear!$B$2:$B$6=$I2,teddy0bear!$A$2:$A$6),INDEX(teddy0bear!E$2:E$4,MATCH($I2,teddy0bear!$D$2:$D$4,0),0))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[cyrilbrd]

First range is a table called dpt.
That bit =IF(COUNTIF(dpt,$I2) here refers to range A2:B6
The formula is truncated somehow in the previous post:
CSE =IF(COUNTIF(dpt,$I2)=1,INDEX(E$2:E$4,MATCH($I2,$D$2:$D$4,0)),SUM(SUMIF(teddy0bear!$D$2:$D$4,IF(teddy0bear!$B$2:$B$6=$I2,teddy0bear!$A$2:$A$6),INDEX(teddy0bear!E$2:E$4,MATCH($I2,teddy0bear!$D$2:$D$4,0),0))))

 

[teddy0bear]

That sounds like something I would do if I was doing regular excel tables. The data table is coming from an external source and only exists as a connection in my data model. I could maybe add a calculated column into the dta model like this but I'd worry about adapting for changes on the dpt (as someone will decide that pink is blue, just you watch) and I still have the issue about it filtering properly when changing the slicer.