cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlexBro Regular Visitor
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.).

 

CustomerAmountNumber of PrchaseAvgAmountPerPurchaseAvgAmountPerPurchasePerCustomer
A20032001287,5
A10022001287,5
A30012001287,5
B500423751287,5
B1000323751287,5
B3000223751287,5
B5000123751287,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
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 (
    ADDCOLUMNS (
        VALUES ( Table1[Customer] );
        "AvgPerCustomer"; DIVIDE (
            CALCULATE ( SUM ( Table1[Amount] ) );
            CALCULATE ( COUNT ( Table1[Amount] ) )
        )
    );
    [AvgPerCustomer]
)

 

Code formatted with   www.daxformatter.com

2 REPLIES 2
Highlighted
Super User
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 (
    ADDCOLUMNS (
        VALUES ( Table1[Customer] );
        "AvgPerCustomer"; DIVIDE (
            CALCULATE ( SUM ( Table1[Amount] ) );
            CALCULATE ( COUNT ( Table1[Amount] ) )
        )
    );
    [AvgPerCustomer]
)

 

Code formatted with   www.daxformatter.com

AlexBro Regular Visitor
Regular Visitor

Re: Group by Customer - DAX Measure

@AlB

 

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