Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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.

 

 

  • 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  

2 Replies

  • AlB's avatar
    AlB
    Community Champion

    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's avatar
      Anonymous
      Not applicable

      AlB

       

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