Conditional field in pivot table

[tigerzen]

Trying to calculate the number of sales that exceed a certain threshold, 30 in this example but would like to be able to make it variable by using a cell reference so we can vary the threshold and change it quickly to 40, 50 or any other amount. Anyway the existing pivot table simply returns 1 and clearly in January 2022 there are 2 values over 30. I realise that Pivot table calculated fields can be limited but I thought they could Sum values without the need of a helper column although I'm not sure that will work either. So my question is can I get this pivot table to calculate what's needed here, if not, can someone give me a formula that will solve this.

Book3
	A	B	C	D	E
1	Date	Sales
2	2/01/2022	50		Row Labels	Count of Over_30
3	10/01/2022	60		2022	1
4	3/03/2022	10		Jan	1
5	6/05/2022	40		Mar	0
6	18/05/2022	12		May	1
7	1/06/2022	20		Jun	0
8	9/09/2022	80		Sep	1
9	12/12/2022	90		Dec	1
10	2/02/2023	5		2023	0
11				Feb	0
12				Grand Total	1
Sheet1

 

[James006]

Hi,

You are not showing the formula you are currently using ...
see:

Use Excel Pivot Table Calculated Field or Calculated Item

See difference between Excel pivot table calculated field and calculated item. When and how to use, examples and warnings. Video, written steps, Excel file

www.contextures.com

 

[tigerzen]

Hi James,

The formula is =Sales>30.
Thanks for the PT calculated field link.

 

[James006]

Hi,
With your reference value of 30 located in cell D1, you can test following formula in cell C2

=SUMPRODUCT((MONTH($A$2:$A$10)=MONTH(A2))*(YEAR($A$2:$A$10)=YEAR(A2))*($B$2:$B$10>$D$1))

The Pivot Table performs an automatic date grouping by year, quarter, month which seems to disrupt the calculated field formula ...

 

[tigerzen]

Hi,
With your reference value of 30 located in cell D1, you can test following formula in cell C2

=SUMPRODUCT((MONTH($A$2:$A$10)=MONTH(A2))*(YEAR($A$2:$A$10)=YEAR(A2))*($B$2:$B$10>$D$1))

The Pivot Table performs an automatic date grouping by year, quarter, month which seems to disrupt the calculated field formula ...

Thanks James, your formula works fine on individual cells, can it be modified so that it somehow groups the entries, in other words it returns 2 against January?
And yes it's a pity that the PT formula is disrupted, I use PTs a lot for summary/analysis.

 

[James006]

Hi,
In terms of grouping, the shortcut is to include this new field as a row in your Pivot Table ...