Measure for Distinct Count of Product that have a total number of sales lower than a specific amount

[BobbyBiff]

I'm trying to come up with a measure that will count the number of low performing products (less than 10 total sales) each year.

In the example below, the products are separated by another field (gender), so the measure needs to sum the total but also do a distinct count of product.

Product	Year	Sales	Gender
Apple	2019	10	Male
Apple	2019	5	Female
Banana	2019	15	Male
Banana	2019	6	Female
Orange	2019	2	Male
Orange	2019	1	Female
Apple	2020	4	Male
Apple	2020	9	Female
Banana	2020	8	Male
Banana	2020	3	Female
Orange	2020	4	Male
Orange	2020	7	Female
Apple	2021	9	Male
Apple	2021	6	Female
Banana	2021	3	Male
Banana	2021	1	Female
Orange	2021	2	Male
Orange	2021	1	Female

I can already use the existing table to produce this:

Product	2019	2020	2021
Apple	15	13	15
Banana	21	11	4
Orange	3	11	3

The answer I'm looking for would allow me to produce a visualisation that returns something like this:

Year	2019	2020	2021
Number of products with fewer than 10 sales	1	0	2

My current efforts have hit a dead end at:

Measure = Calculate(distinctcount('Table'[Product],
FILTER(('Table'),
SUM('Table'[Sales])<10
))

As you can see i'm a bit of newbie with DAX so any help would be greatly appreciated!

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://dropmefiles.com/S4APz

[BobbyBiff]

Thats worked perfectly, thanks!

[amitchandak]

@BobbyBiff , Try like

Measure = countx(filter( values('Table'[Product]),calculate(SUM('Table'[Sales]))<10))

of you can use dynamic segmentation

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k