Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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.
Solved! Go to Solution.
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.
ProductsAboveMinSold = CALCULATE(DISTINCTCOUNT('Table'[Product]), FILTER('Table', [SumTotal]>30000))
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))
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.
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
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))
Measure = COUNTROWS(FILTER(VALUES(Table2[Product]), CALCULATE( SUM(Table2[Sales]) ) > 50000))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
124 | |
79 | |
50 | |
38 | |
38 |
User | Count |
---|---|
196 | |
80 | |
70 | |
51 | |
42 |