1 of 1 people found this helpful
The reason it falls apart is because by applying a filter you remove some rows from your calculation.
In your example, if you filter out Reason Null (rows with blank Reason), this will remove 100 + 100 = 200 from you SUM(Target), making it 500 - 200 = 300. So now you % will be 100/300 instead of 100/500.
This happens because calculations are done after filtering, which means that you can't have filters on the sheet that will affect your data in this way, so you have to think of something else, which depends on what you try to accomplish with filters.
Funny how these questions come in waves, there have been at least three like this in the last day or so. Anyways, one way to do this is to use a filter on a table calculation that looks up the Reason, instead of the Reason directly, look at the workbook in this post:
Thanks Jonathan, I was able to get the results I wanted by using this filter:
So far it looks like it works.