Hi. I'm sure this is a pretty simple question... but I'm trying to essentially perform a calculation that considers the values of the entire table/column in addition to the values that are in the pill for the criterion.
The formula should essentially be a count of all of the unique values (countd) of the first column (Entity ID in my data set below), and then subtract the count of another set of values where the value is 100%, and then subtract the count where the values are not equal to 100%. The final value set (and where I am having trouble with) is the sum of the unique values in the first column less the values in the first and second calculation. I can get everything to work where I input manually the number of unique iterations of entity ID (ie perform the count myself), but it needs to be scalable.
I.e. Complete = sum(if[% Complete] = 1 then 1 else 0 end)
Not Complete = sum(if[% Complete]<1 then 1 else 0 end)
NA = ????
(where NA is the count of unique values in Column A, less the count of complete and not complete values). In this case, the count of entity ID would be 7 (there are 7 unique entity IDs). For each Workstep ID (the pill I slide onto the rows/col shelf), I want to determine the complete, not complete, and NA counts.
- For workstep AAA for example, the complete count is 6, the incomplete count is 1, and the NA count is 0 (7 - 6 - 1) = 0.
- For workstep DDD for example, the complete count is 3 and the incomplete count is 0. Consequently, the NA count is 4 (7 - 3 - 0) = 4.
My challenge (and the crux of the issue) is that I cannot figure out how to calculate the count of the total values (essentially 7 in this case) for the entire dataset as opposed to just the one that shows up in the pill. Does anyone have any guidance in doing this? Again, I can statically set the value to be 7, but I need it to be scalable if I pop in a different dataset.
Consider the following data set:
|Entity ID||Work Step||% Complete|