Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
mtfrye14
Frequent Visitor

DistinctCount products with sales > X

I am just starting to learn DAX and I am looking to create a measure that counts the number of products with sales greater than say $100,000.

 

My fact table is at a transaction level like below

 

Date     Product       Total Sales

12/1      OrangeA      50000

12/2     OrangeB       50000

12/2     OrangeA       25000

12/3      AppleA         25000

12/3      AppleA         3000

12/4      OrangeB       10000

 

I have been experimenting with:

SumTotal = SUM([totalsales]

Products>100 = Calculate(Distinctcount('Table'[Product]), [SumTotal]>100000)

 

But I get an error because I am using the [Sumtotal] measure. So I tried:

 

Products>100 = CALCULATE(DISTINCTCOUNT('Table'[Product]), 'Table'[TotalSales]>100000)

 

But this only counts at the line item level where I need the Total Sales aggregated by product if that makes sense..

 

Let me know if you can help or any thoughts.

2 ACCEPTED SOLUTIONS
KGrice
Memorable Member
Memorable Member

You were close. The first one doesn't work because you can't use a measure in a simple comparison without a FILTER, and the second isn't summarizing anything. If you add a FILTER you'll be good. I used 30,000 as a threshold, but only so you can see results based on the sample dataset provided.

 

MinSold.PNG

 

 

ProductsAboveMinSold = CALCULATE(DISTINCTCOUNT('Table'[Product]), FILTER('Table', [SumTotal]>30000))

 

View solution in original post

jahida
Impactful Individual
Impactful Individual

There's a number of ways to do that aggregation, here's one:

 

ProductsAboveMinSold = COUNTROWS(FILTER(SUMMARIZE(Table1, Table1[Product], "Val", SUM(Table1[Total Sales])), [Val] > 50000))

View solution in original post

4 REPLIES 4
KGrice
Memorable Member
Memorable Member

You were close. The first one doesn't work because you can't use a measure in a simple comparison without a FILTER, and the second isn't summarizing anything. If you add a FILTER you'll be good. I used 30,000 as a threshold, but only so you can see results based on the sample dataset provided.

 

MinSold.PNG

 

 

ProductsAboveMinSold = CALCULATE(DISTINCTCOUNT('Table'[Product]), FILTER('Table', [SumTotal]>30000))

 

Hmm. I feel like I am getting closer. The answer you provided looks like it is filtering based on the transaction line instead of aggregating by product first. For the dataset below I need it to aggregate BEFORE it counts

 

Date     Product       Total Sales                 

12/1      OrangeA      50000                                

12/2     OrangeB       50000                                  

12/2     OrangeA       25000                                  

12/3      AppleA         25000

12/3      AppleA         3000

12/4      OrangeB       10000

 

into    

 

Product     Total Sales

OrangeA    75000

OrangeB    60000

AppleA      28000

 

Distincount of products greater than 50,000 = 2

jahida
Impactful Individual
Impactful Individual

There's a number of ways to do that aggregation, here's one:

 

ProductsAboveMinSold = COUNTROWS(FILTER(SUMMARIZE(Table1, Table1[Product], "Val", SUM(Table1[Total Sales])), [Val] > 50000))

Anonymous
Not applicable

Measure = COUNTROWS(FILTER(VALUES(Table2[Product]), CALCULATE( SUM(Table2[Sales]) ) > 50000))

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.