8 Replies Latest reply on Mar 31, 2017 6:42 AM by Michael Someck

# Adding Parent and Subsidiary company costs together

Hello,

I have a set of pricing data that has both the parent and subsidiary data included. I would like to be able to combine the parent and subsidiary price to get the total price. So for example, total cost for Subsidiary 1 would be \$15, and Subsidiary 3 would be \$18 etc.

Here is a very simplified sample of the data:

 Name Parent ID Price Parent 1 10 \$10.00 Parent 2 20 \$11.00 Parent 3 30 \$12.00 Subsidiary 1 10 \$5.00 Subsidiary 2 10 \$6.00 Subsidiary 3 20 \$7.00 Subsidiary 4 20 \$8.00 Subsidiary 5 30 \$9.00

Any help would be much appreciated!

Thanks

Paul

• ###### 1. Re: Adding Parent and Subsidiary company costs together

Hi Paul,

This should work for you, assuming you only have one value for each Parent. First, you want to capture the Parent Price for each row. To do this, I used the following LOD calculation called "Parent Value":

{FIXED [Parent ID]: MAX(if CONTAINS([Name],"Parent") then [Price] end)}

Then, I used the following formula to calculate total price (I assumed that the Total Price for parent should be just the Price, while it should be Price + Parent Price for Subsidiary).

if CONTAINS([Name],"Subsidiary") then [Price]+[Parent Price]

else [Price]

END

This is the result:

Is this what you're looking for?

Michael

• ###### 2. Re: Adding Parent and Subsidiary company costs together

Hi Michael Someck - thanks for the answer! This was my first post in the forums here and now I understand why they recommend posting a sample workbook. I over simplified my question not realizing that it would give me a different answer.

I do have multiple values for each parent. I have now attached a sample workbook.

Region Code = Parent Code

Tier - identifier if it is parent or sub. In this instance UT=Parent (UT-Upper-tier) and LT=Sub (LT - lower-tier)

Municipality = "Company"

Service Description - this identifies the "price" for each municipal service.

Let me know if you need anything further clarified.

Thanks again.

Paul

• ###### 3. Re: Adding Parent and Subsidiary company costs together

Hi Paul,

Got it! Can you clarify then what you want the end result to look like?

Michael

On Wed, Mar 29, 2017 at 18:40 Paul Creamer <tableaucommunity@tableau.com>

• ###### 4. Re: Adding Parent and Subsidiary company costs together

I would like to show a table for the LT's but will be excluding the "Service Description".

So for example for Region 10 - Durham R is the UT and I would want this added to the LT's.

Durham R total = \$26,297

Oshawa C total = \$11,384

Therefore the amount shown would be \$37,681.

Hope that makes sense.

• ###### 5. Re: Adding Parent and Subsidiary company costs together

Hi Paul,

Got it! So I came up with a solution that gets the numbers you want, but I don't think it's necessarily the most elegant way to do it. Packaged workbook is attached, and here are the steps I used:

1) Use the following for the calculated field for UT Price:

{FIXED [Region Code]: sum(if [Tier]="UT"  then [Price] END)}

One of the important things to note here is that for this to work, you need to make sure any filters you have applied that change the value of price are "Added to Context". The reason is that the LOD calculation runs across the entire data set, regardless of what filters you have applied. "Adding to to Context" forces the calculation to use only filtered data. In this case, I had to add YEAR(Year) to context (otherwise price would sum across all years).

2) Total Price is the calculated as

sum([Price])+avg([UT Price])

The reason for this is that the value for UT Price is being assigned to every single row that has the given region code. To make sure the UT price is only counted once in our sum (instead of once for each row), we need to use average.

This gives the following result, which I believe has the values you want.

The downside to this approach is that it isn't particularly flexible, as the calculations were designed with this as the end-goal. For example, the way this was done, the calculation for Total Price only works for LT (and not UT). If you want that, you'll need to adjust the second formula, something like:

if CONTAINS(ATTR([Tier]),"LT") then sum([Price])+avg([UT Price])

else sum([Price])

END

Does this get you what you need? Happy to continue to help in any way I can

Michael

• ###### 6. Re: Adding Parent and Subsidiary company costs together

Thanks a lot Michael, really appreciate the time. I won't have a chance to try this until this evening but will let you know if it works!

• ###### 7. Re: Adding Parent and Subsidiary company costs together

Got it working, thanks again!