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]

so simple question: which one is your final result?

 

[dayday123]

so simple question: which one is your final result?

3 and 4 would both be final results depending on whether the month dimension is added. I included both to show that in either scenario i want the filter to be on quantity but the result would vary depending on what dimensions are included. The same way uniquecount works. But now with an additional filter. Is that clear?

 

[sandy666]

add to data model
add field Quantity as the first position in ROWS area, add field Product to the ROWS area and add field Product to the Value area for distinct count
then Quantity value filter greater then 100
right click on Pivot and remove Product

 

[dayday123]

add to data model
add field Quantity as the first position in ROWS area, add field Product to the ROWS area and add field Product to the Value area for distinct count
then Quantity value filter greater then 100
right click on Pivot and remove Product
View attachment 6707

When i try to do a value filter on quantity the only options it gives me are distinct count of product and count of product. There is no option to actually filter on the quantity since it is in ROWs and therefore not a value?

 

[sandy666]

 

[dayday123]

View attachment 6709

yes i tried that (from the quantity row) but in my screen shot i shared once I select "greater than" it pops up a second screen where I need to choose what value i want to filter and the only 2 options are 1)count of product or 2)distinct count of product. I attached the two screens I see. It won't allow me to filter on quantity.

 

[sandy666]

1. Add Quantity to the ROWS area
2. Add Quantity to the VALUES area
3. Filter first (ROWS) Quantity greater than 100
4. change second (VALUES) Quantity to Distinct Count

 

[SpillerBD]

Image 3 you will need to DAX it our build the basic Pivot Table and then use a GETPIVOTDATA function.

 

[sandy666]

without Pivot but with Power Query

Quantity	Count
105	1
Total	1
Quantity	Count
105	1
110	1
Total	2

or

Product	Quantity	Count
Product 1	105	1
Total		1
Product	Quantity	Count
Product 1	105	1
Product 2	110	1
Total		2