3 Replies Latest reply on Mar 19, 2012 9:45 AM by Martin Olivas

# Percentage of question

Ok here is my data set for my example:

 Type Prod Unit Date Target Value Reason W D 3/1/2012 100.00 W D 3/1/2012 100.00 W D 3/1/2012 100.00 50.00 A W D 3/1/2012 100.00 50.00 A W D 3/1/2012 100.00 100.00 B W D 3/2/2012 100.00 W D 3/2/2012 100.00 W D 3/2/2012 100.00 50.00 A W D 3/2/2012 100.00 50.00 A W D 3/2/2012 100.00 100.00 B

For each day, I want to calculate the % of sum(value) / sum(target) for each reason

So for 3/1, Reason A would be 100/500 = 20% ; B would be 100/500=20%.  I used the following formula:

sum(value)/windows_sum(sum(target)) and compute across reason.

This works except when I filter on reason type then it falls apart.  As you can tell I haven't figured out table calculations yet. I have attached my packaged workbook of the problem. Any help would be appreciated.

• ###### 1. Re: Percentage of question

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.

1 of 1 people found this helpful
• ###### 2. Re: Percentage of question

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: