7 Replies Latest reply on Apr 8, 2016 3:05 PM by Kristen Lund

# Help Needed - Exclude Child Values/Instances in Calculations

Hello Communities,

I did open a case with Tableau directly, but wanted to try the community aspect as well for some help!

I have a data source that connects multiple tables and at the end, has a double connect - table A inner join table B and table A left join table C. There's a value on table A I am trying to sum, however since an instance of table A can have multiple table C instances, the desired value is being multiplied by the number of instances. How do I basically exclude table C in my calculation without using a new data set? I was thinking something like a sum(if distinct, but I haven't hand any luck. Any suggestions would be greatly appreciated!

Kristen

• ###### 1. Re: Help Needed - Exclude Child Values/Instances in Calculations

Hello Kristen,

We can most likely resolve this problem using LoD (Level of Detail) calculations.

If possible, could you share an example workbook?

Also, here are a few links to help out in the meantime.

Regards,

Rody

• ###### 3. Re: Help Needed - Exclude Child Values/Instances in Calculations

Thanks, Rody. I feel like the 'exclude' would work, but even when I stick in every field that comes through on Table C, the calculation is still exactly the same. I am not able to share a workbook due to the data - I'm hoping I can still get this solved though explanation, trail and error though!

And thanks for that link, Nicholas. Average the data does not work because they do differ on each of the Table A values. Somewhere out there I figure there has to be some solution to this!

• ###### 4. Re: Help Needed - Exclude Child Values/Instances in Calculations

Hi Kristen, if you have a sample workbook for us to look at we can get a much better sense of the solution that works for your needs.

Anonymize your Tableau Package Data for Sharing

Hopefully this helps!

• ###### 5. Re: Help Needed - Exclude Child Values/Instances in Calculations

Hi again - attached is an example database. I'll admit I didn't watch the video but skimming it realized how it's possible to create an example for some help. Note, in Tableau many of these are in different tables and much more information but I put them in one and condensed for ease.

The grand total listed \$46,949.77 does include the duplicate values that should be excluded. If you were to export the data, you'll see the RLI each have unique values, but this is not a one to one relationship with Reservation.Since Reservation is the parent (table A as noted above), the duplicate lines of 10/11, 17/18, 35/45, 41/42 have their Fixed Price being counted more than once. The total in Tableau should be \$46,204.35 if I can remove these duplicates as desired.

Hopefully that helps clarity!

• ###### 6. Re: Help Needed - Exclude Child Values/Instances in Calculations

Hi Kristen!

I am pretty sure I got it. Basically, the rows that are being duplicated are occuring at the level of Reservation. In order to get around this try creating a calc field with the following equation:

{INCLUDE [Reservation]: MIN([Fixed Price])}

Use that instead of Fixed Price.

Another resource to better understand LOD calcs: Top 15 LOD Expressions | Tableau Software

1 of 1 people found this helpful
• ###### 7. Re: Help Needed - Exclude Child Values/Instances in Calculations

You saved my Friday, Nicholas - - that simple formula worked perfectly! I'm still fairly new to Tableau and will definitely dig into those other resources as well. Thank you so much!