Distinct Count in a pivot table IF greater than a specific value

[dayday123]

Hello All -

using the data model i can easily use the distinct count but I am looking for a way to not only do a distinct count but a distinct count when a certain other value is met. For example based on the below table:

If i were to pivot the data and do a count of "Product" it would show 5
If I did a distinct count of "Product" it would show 3
How can I do a distinct count of "Product" only when the total quantity exceeds 100 - which should be a count of 2

Is there any way to do this in a pivot table?

Thanks so much in advance!

Product	Month	Quantity
Product 1	Jan	105
Product 2	Jan	90
Product 2	Feb	20
Product 3	Jan	50
Product 3	Feb	30

 

[sandy666]

maybe

Product	Sum of Quantity	Distinct Count of Product
Product 1	105	1
Product 2	110	1
Grand Total	215	2

Value filter - Sum of Quantity greater than 100

 

[teylyn]

You can put a value filter on the pivot table, filter to show only products where the sum of quantity is greater 100, then distinct count on that.

 

[sandy666]

filter for post#2

pivot table

I forgot to say these two words

 

[sandy666]

or you can do the same but with months

Product	Month	Sum of Quantity	Distinct Count of Product
Product 1	Jan	105	1
Product 2	Jan,Feb	110	1
Grand Total		215	2

with filter from post #4

but in this case you'll need Power Query

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Quantity", Int64.Type}}),
    Group = Table.Group(Type, {"Product"}, {{"Quantity", each List.Sum([Quantity]), type number}, {"Count", each _, type table}}),
    Month = Table.AddColumn(Group, "Month", each Table.Column([Count],"Month")),
    Extract = Table.TransformColumns(Month, {"Month", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Result = Table.ReorderColumns(Extract,{"Product", "Month", "Quantity", "Count"})
in
    Result

 

[Fazza]

Any pivot table can do this. (Being such a specific requirement it is more suited to a query.)

To explain a manual way to set it up. Give the data a simple defined name, such as "YourData". Save the file.
From a new file (CTRL-N), start the pivot table wizard ALT-D-P & choose external data source at the first screen. OK. Then 'Get Data', Excel files, OK, browse for the file, choose it, then select the data (YourData), pick some/any fields and continue to the end of the wizard & choose the option to edit in MS Query, Finish. Then the SQL button & replace the text you see by

SELECT COUNT(*) AS [YourDistinctCount]
FROM (SELECT Product
FROM YourData
GROUP BY Product
HAVING (Sum(Quantity)>100))

OK to enter that, OK to acknowledge a message about query can't be represented graphically. See the resultset. (This could ultimately be left like this as a query on the worksheet. Refreshable just like a pivot table. But we'll stay with the requested pivot table.) Via the open door icon exit MS Query and complete the pivot table (or query table). The whole worksheet containing the pivot table can if you wish be moved into the source data file.

cheers

 

[Fazza]

post script. some further information/ideas that may interest you. Excel Pivot Tables Count Unique Items

 

[dayday123]

maybe

Product	Sum of Quantity	Distinct Count of Product
Product 1	105	1
Product 2	110	1
Grand Total	215	2

Value filter - Sum of Quantity greater than 100

Thank you so much for your response! I appreciate it. I think however I was unclear. I don't think i can use value filters because i don't want to bring the 'quantity' metric into my pivot able. So for example if I brought month in then the pivot table would look like this:

Row Labels​	Distinct Count of Product​
Jan	1​

because in January only one product has a quantity greater than 100 and in february no products are greater than 100. And if I didn't bring in the month but still wanted my quantity filter to apply the pivot would just look like this:

Distinct Count of Product​

2​

​

 

[sandy666]

post expected result from you example from post#1

 

[dayday123]

post expected result from you example from post#1

If i did a regualr data model pivot i would expect to see images 1 and 2. But what i want to do is add a quantity filter to the pivot table as a whole so that instead I would get images 3 and 4.

I hope that makes sense!