Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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] )
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] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |