Group by Customer - DAX Measure

[Anonymous]

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.

[AlB · Accepted solution]

Hi @Anonymous

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

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

Code formatted with  

[Anonymous]

@AlB

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