topic Sub total in filter in DAX Commands and Tips

Sub total in filter

arif_ali — Fri, 20 Mar 2020 20:05:54 GMT

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

Re: Sub total in filter

Anonymous — Fri, 20 Mar 2020 20:39:33 GMT

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

Re: Sub total in filter

arif_ali — Fri, 20 Mar 2020 21:35:55 GMT

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

Re: Sub total in filter

Anonymous — Fri, 20 Mar 2020 22:13:08 GMT

Sorry but I don't follow.

Best
D

Re: Sub total in filter

arif_ali — Fri, 20 Mar 2020 22:35:09 GMT

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,

Re: Sub total in filter

Anonymous — Fri, 20 Mar 2020 22:53:15 GMT

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

Re: Sub total in filter

arif_ali — Fri, 20 Mar 2020 23:27:49 GMT

Hi D,

You are lifesaver. It worked!

Thank you so much

Re: Sub total in filter

Anonymous — Fri, 20 Mar 2020 23:35:04 GMT

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

Best
D

Re: Sub total in filter

arif_ali — Sat, 21 Mar 2020 02:00:46 GMT

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