cancel
Showing results for
Did you mean:
Regular Visitor

## Group by Customer - DAX Measure

Hi guys,

as I already got great help, I hope that you can quickly help me out regarding following issue.

I have a table with 2 Customers and several purchasements. We have the customerID, the amount and the Number of Purchase (i.e. first purchase = 1, second purchase = 2 etc.).

 Customer Amount Number of Prchase AvgAmountPerPurchase AvgAmountPerPurchasePerCustomer A 200 3 200 1287,5 A 100 2 200 1287,5 A 300 1 200 1287,5 B 500 4 2375 1287,5 B 1000 3 2375 1287,5 B 3000 2 2375 1287,5 B 5000 1 2375 1287,5

I need first to calculate the AvgAmount of Purchase i.e. SUM of the Amount divided by the distinct count of purchases per customer.

Example A: (200+100+300) = 600 / 3 Purchases = 200

Example B: (500+1000+3000+5000) = 9500 / 4 Purchases = 2375

In the end I need to sum the AvgPurchase Amount per customer and divide by number of customers:

= 200 + 2375 = 2575 / 2 Customer (Customer A and B) = 1287,5

The result 1287.5 should be the result of the measure.

I am able to calculate AvgAmount Purchase in case when I put the customer on a column but I am loosing the row context (I hope the wording is correct) once I remove the customer column as I get the SUM of Amount divided by the overall distinctcount of "number of purchase"

`DIVIDE(SUM(A[Amount),DISTINCTCOUNT(A[NumbeOfPurchases))`

I need a way to use somekind of a group by but I can't figure out since hours how to do it.

I hope it's somehow clear. Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: Group by Customer - DAX Measure

Hi @AlexBro

Try this for your measure. You can set it in a Card visual, for instance.

```TotalAverage =
AVERAGEX (
VALUES ( Table1[Customer] );
"AvgPerCustomer"; DIVIDE (
CALCULATE ( SUM ( Table1[Amount] ) );
CALCULATE ( COUNT ( Table1[Amount] ) )
)
);
[AvgPerCustomer]
)```

Code formatted with

2 REPLIES 2
Highlighted
Super User

## Re: Group by Customer - DAX Measure

Hi @AlexBro

Try this for your measure. You can set it in a Card visual, for instance.

```TotalAverage =
AVERAGEX (
VALUES ( Table1[Customer] );
"AvgPerCustomer"; DIVIDE (
CALCULATE ( SUM ( Table1[Amount] ) );
CALCULATE ( COUNT ( Table1[Amount] ) )
)
);
[AvgPerCustomer]
)```

Code formatted with

Regular Visitor

## Re: Group by Customer - DAX Measure

@AlB

Thanks for the solution - a little modification was required but worked. thanks.