3 Replies Latest reply on Aug 27, 2013 9:19 PM by Joshua Milligan

# Calculation question, counting independant of filter

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.

Example:

• 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 AF100000 AAA 0 AF100000 BBB 0 AF100000 CCC 1 AF100000 DDD 1 AF100000 EEE 1 AF100001 AAA 1 AF100001 BBB 1 AF100001 CCC 1 AF100001 DDD 1 AF100001 EEE 1 AF100001 FFF 1 AF100002 AAA 1 AF100002 BBB 1 AF100002 CCC 1 AF100002 DDD 1 AF100003 AAA 1 AF100003 BBB 1 AF100004 AAA 1 AF100004 BBB 1 AF100005 AAA 1 AF100005 BBB 1 AF100006 AAA 1 AF100006 BBB 1
• ###### 1. Re: Calculation question, counting independant of filter

Barett,

I've attached a workbook that shows one possible way of approaching the problem.  It has some inline documentation, but I'd be happy to explain or answer any questions you might have!

Regards,

Joshua

• ###### 2. Re: Calculation question, counting independant of filter

Hi Josh,

Thanks so much for your help, this is a good start!

However, when I create a stacked bar graph, the value (for some reason) doesn't seem to populate (the total for NA seems to be 0). Any idea whats going on?

• ###### 3. Re: Calculation question, counting independant of filter

Barrett,

I'll take a look - but I'm actually out of commission for another day so please feel free to ping me on this thread if I don't get back to you on Thursday.

Regards,

Joshua