Sub total in filter

[arif_ali]

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

[Anonymous]

// 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

[arif_ali]

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

[Anonymous]

Sorry but I don't follow.

Best
D

[arif_ali]

Hi D,

Here is the code

Active Customers =

VAR mStartDate = MIN('Business Dates'[Date])

VAR mEndDate = LASTDATE('Business Dates'[Date])

VAR mMonths = DATEDIFF(mStartDate,mEndDate,MONTH)+1

VAR mProducts = IF(ISBLANK([Products CY]),0,[Products CY])

VAR mAverage = DIVIDE(mProducts,mMonths)

RETURN

SUMX('Calc Active Dealers',1*(mAverage>=2))

The results is 689 whereas I only a total of 350 customers. It is counting each customer for each month as opposed to one time for the period selected).

Thank you,

[Anonymous · Accepted 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

[arif_ali]

Hi D,

You are lifesaver. It worked!

Thank you so much

[arif_ali]

Hi D,

I am new to any community. I will certainly do it because you deserve it. Can you please tell me how to do it?

Thanks,
Arif

[Anonymous]

Glad it's worked for you. Would you mind giving me a kudo, please?

Best
D