# CALCULATE(SUM(...) Problem

#### eliwaite

##### New Member
I am trying to figure out what % of active salespeople have sold a given product that meet a certain threshold. Here is an example of a my data:

 Name Quarter Product Revenue John Q1 Apples 15 Mary Q1 Apples 30 John Q1 Apples 10 Jim Q2 Oranges 25

<tbody>
</tbody>

Lets focus on Q1. To figure out how much someone has sold I have the following formula: Total Won \$ Q1:=CALCULATE(SUM(Revenue),FILTER(Table,[Quarter]="Q1"))

I then have another field to set the threshold, which looks like this: Threshold:=20
In order to get a total count of sales people I have the following formula: # of Salespeople:=DISTINCTCOUNT([Name])

I then have the following formula to figure out the percentage of salespeople that have sales over 20: % of Salespeople with Won in Q1:DIVIDE(CALCULATE(DISTINCTCOUNT([Name]),FILTER(Table,[Total Won \$ Q1]>[Threshold]&&[Quarter]="Q1")),[# of Salespeople])

With those formulas these are the outputs I get in the pivot table:

 Total Won \$ Q1 % of Salespeople with Won in Q1 Apples 55 50% John 25 Mary 30 100%

<tbody>
</tbody>

Now I would have expected John to show as 100% and the total for Apples to also be 100%. As I play around with my Threshold, if I set it to 15 John will show up as 100%, if I set it to 16 John will show up as a blank. It appears that the filter function comes across that first record, sees it is less than 20and ignores the rest of the Apples John has sold in Q1, which if summed would have qualified him to be counted.

Can anyone point out what is wrong with my formulas and how to modify it so I get the expect result? Thank you

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sorry for slow reply, have been a busy lizard.

I suspect the problem is here:
FILTER(Table,[Total Won \$ Q1]>[Threshold]&&[Quarter]="Q1")

Realize you are filtering 1 row at a time here... so Revenue=15,30,10 will each be evaluated, not 25,30.

I would try replacing Table as the first param with VALUES(Table[Name]) and see if that magically works

Replies
5
Views
297
Replies
0
Views
193
Replies
1
Views
323
Replies
1
Views
1K
Replies
0
Views
233

1,196,474
Messages
6,015,432
Members
441,894
Latest member
Zululander

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back