CALCULATE(SUM(...) Problem

[eliwaite]

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

 

[scottsen]

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