Forum Discussion

arif_ali's avatar
arif_ali
Icon for Helper I rankHelper I
6 years ago
Solved

Sub total in filter

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 ...
  • Anonymous's avatar
    Anonymous
    6 years ago
    // 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's avatar
arif_ali
Icon for Helper I rankHelper I
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago
Sorry but I don't follow.

Best
D
  • arif_ali's avatar
    arif_ali
    Icon for Helper I rankHelper I
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago
      // 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's avatar
        arif_ali
        Icon for Helper I rankHelper I
        6 years ago

        Hi D,

         

        You are lifesaver. It worked!

         

        Thank you so much