3 Replies Latest reply on Nov 15, 2018 11:48 AM by Hari Ankem

# Dynamic Value in a Calculated Field

In the data set I'm working with, I have the numbers of children entering care over the past five years. I'm attempting to create a table that displays the reasons associated with a child's removal from the home along with the frequency/percentage of each reason. However, because there are multiple reasons for a removal, I want the percentage to be that of the total number of children entering care (which will add up to over 100%) and not of the total number of reasons (i.e., the quick table calculation "sum of total"). In the file attached, 269,690 children entered care in 2017. Among them, 166,991 had neglect as a reason for entry, so the percentage should be 166,991 / 269,690 = 62%. I'm trying to create a table calculation [value] / sum ([number - enter]). I get the error about mixing aggregated/non-aggregated data, but am having a hard time reconciling this while filtering the denominator to pull the correct number for the year 2017. I consider the quick-and-dirty solution of manually entering what the denominator should be, but because the dashboard has a state filter, the calculation needs to be dynamic.

Unfortunately, because of the way I have the data, I cannot restructure the data source to be more flexible. I've tried searching for solutions regarding dynamic calculations and non-mutually exclusive percentages, but haven't found anything that has worked, so I appreciate any insight!

• ###### 1. Re: Dynamic Value in a Calculated Field

Does this look right to you?

Hope this helps. Updated workbook is attached.

1 of 1 people found this helpful
• ###### 2. Re: Dynamic Value in a Calculated Field

Thanks! That worked like a charm.

• ###### 3. Re: Dynamic Value in a Calculated Field

You are welcome.