cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable
``````// 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 8
Anonymous
Not applicable
``````// 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

Frequent Visitor

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
Not applicable
Sorry but I don't follow.

Best
D
Frequent Visitor

Hi D,

Here is the code

Active Customers =
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
Not applicable
``````// 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

Frequent Visitor

Hi D,

You are lifesaver. It worked!

Thank you so much

Frequent Visitor
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
Not applicable
Glad it's worked for you. Would you mind giving me a kudo, please?

Best
D

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors