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

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    // 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'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