Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello All,
I have a count of units for a selected period. I am trying to count only those who have bought an average of 2 units
i.e. total units bought 9 in 3 months so an average of 3.
I want to count only those customer who have bought an average of 3 units. My DAX works when I bring the customer name. It looks at each customer and calculate the average but when I summarize it, it doesn't work.
I want to have a count of total customers who have bought an average of 3 units. Instead, I get a count of all customer who have bought even single unit.
I would appreciate if someone can assit.
Thank you,
Arif
Solved! Go to Solution.
// Dealers must be a dimension connected
// to the fact table on DealerId.
Active Dealers =
VAR mStartDate = MIN( 'Business Dates'[Date] )
VAR mEndDate = MAX( 'Business Dates'[Date] )
VAR mMonths =
DATEDIFF( mStartDate, mEndDate, MONTH) + 1
var __dealers =
SUMX(
Dealers,
1 * ( [Products CY] >= 2 * mMonths )
)
RETURN
__dealers
Best
D
// Measures should be defined in advance:
// [Total Units]
// [Total Months]
[Monthly Average] =
DIVIDE(
[Total Units],
[Total Months]
)
[# Cust with avg of 3 units] =
SUMX(
Customers,
1 * ( [Monthly Avg] = 3 )
)
Best
D
Hello D,
Thank you for the prompt response!
It partially worked (I should have been more specific). If customer's average is 3 for all 3 months, I get a count of 3 whereas I want to count unique customers.
Thank you,
Arif
Hi D,
Here is the code
// Dealers must be a dimension connected
// to the fact table on DealerId.
Active Dealers =
VAR mStartDate = MIN( 'Business Dates'[Date] )
VAR mEndDate = MAX( 'Business Dates'[Date] )
VAR mMonths =
DATEDIFF( mStartDate, mEndDate, MONTH) + 1
var __dealers =
SUMX(
Dealers,
1 * ( [Products CY] >= 2 * mMonths )
)
RETURN
__dealers
Best
D
Hi D,
You are lifesaver. It worked!
Thank you so much
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
20 | |
7 | |
6 | |
5 | |
5 |
User | Count |
---|---|
26 | |
10 | |
10 | |
9 | |
6 |