Forum Discussion
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.
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] )
2 Replies
- AlBCommunity 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] )- AnonymousNot applicable