Unique Count Pivot to exclude where<$0

[drose1105]

Hi all.

My Pivot table of Distinct Count of Company Names is not working.

I want the Distinct Count to EXCLUDE any company where the New Revenue amounts are <$0 dollars. So, in the "Type" of the pivot table, I selected "Value Filter", Greater than, "New Revenue is greater than 0". However, the distinct count does NOT properly exclude the $0 or the negative ($6k) amounts - that is, instead of retuning a count of 4, it gives me a count of 6

Here is my data set:

Data​
Solution​	Company Name​	Type​	New Revenue​
Enter	Merdona​	X Sell​	0​
Enter	Donald​	X Sell​	(6,000)​
Enter	Bees​	X Sell​	27,000​
Enter	Horray​	X Sell​	45,000​
Enter	Safe​	X Sell​	54,850​
Enter	Camp​	X Sell​	93,6​

Aside from writing an IF formula within my data, is there any way to do this in the Pivot Table?

Here is the full file in Dropbox.com

Distinct Count Pivot to exclude less than $0.xlsx

Shared with Dropbox

www.dropbox.com

 

[awoohaw]

I don't understand. When I filter by > 0, the data filters appropriately. (It filters row 4 and 6 out). But, I added a subtotal function for column B and D:

Distinct Count Pivot to exclude less than $0.xlsx
	A	B	C	D
1
2	Data
3	Solution	Company Name	Type	New Revenue
6	Enter	Bees	X Sell	27,000
7	Enter	Horray	X Sell	45,000
8	Enter	Safe	X Sell	54,850
9	Enter	Camp	X Sell	93,660
10	Distinct # of Subs	4		220,510
11
CJM data

Cell Formulas
Range	Formula
B10	B10	=SUBTOTAL(3,B4:B9)
D10	D10	=SUBTOTAL(9,D4:D9)

 

[drose1105]

When you applied the filter by >0 in this Pivot table, which row field did you use, Solution or Type? I tried both and still getting a distinct count of 6. My Pivot table is in classic format but also tried in regular format and still no luck.

Also, below is a picture of my pivot table fields using Distinct Count of Company Names. Is this correct?

 

[jdellasala]

Hi all.

My Pivot table of Distinct Count of Company Names is not working.

I want the Distinct Count to EXCLUDE any company where the New Revenue amounts are <$0 dollars. So, in the "Type" of the pivot table, I selected "Value Filter", Greater than, "New Revenue is greater than 0". However, the distinct count does NOT properly exclude the $0 or the negative ($6k) amounts - that is, instead of retuning a count of 4, it gives me a count of 6

Here is my data set:

Data​
Solution​	Company Name​	Type​	New Revenue​
Enter	Merdona​	X Sell​	0​
Enter	Donald​	X Sell​	(6,000)​
Enter	Bees​	X Sell​	27,000​
Enter	Horray​	X Sell​	45,000​
Enter	Safe​	X Sell​	54,850​
Enter	Camp​	X Sell​	93,6​

Aside from writing an IF formula within my data, is there any way to do this in the Pivot Table?

Here is the full file in Dropbox.com

Distinct Count Pivot to exclude less than $0.xlsx

Shared with Dropbox

www.dropbox.com

If you want to filter, put New Revenue in the Filter section. For a small selection like this, it's not a problem, but for a large dataset you should convert the data into a Table, load it into the Data Model and create a Measure for the count.