I'm trying to building a pie chart that shows the percentage completion for a survey that never closes. Respondents are both added and surveys are completed over time. Some of our surveys have been running for years. Any way you do this is flawed, but we feel the best way to do this is to add up the number of respondents that have been completed in the time period and divide by the number of respondents that were "listed" (added). So in the image below, Respondents 1 and 3 would count for completes, but only 2 and 3 would count for lists, so a completion rate of 50%.
I tried building an ETL job to aggregate these numbers into something like this.
But in the pie chart I need to display complete and not complete and I can't figure out how to calculate non-complete. Here is what I have at the moment, but it should say Complete Count 15 and Not Complete Count 71 (86-15).
So maybe this isn't the ETL data structure I need. Can anyone help?