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

    Dynamic Value in a Calculated Field

    Steven Lao

      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!