I am looking to create a measure that takes the average of several sums, grouped by an ID. An example would be if you had a database of customers that each purchased a variety of things, and you wanted to find the average amount that customers spent total sum all of their purchases grouped by customer, and then average out the result.
I have considered a group by table, but if I do this then filters will not apply correctly to the results (unless there is a way to create a relationship between the two in Power BI maybe, but I have not found that functionality).
Is there a way to create this measure?
EDIT: A more concise explanation of the table and the goal:
Input table
CustomerID | Total Transaction Amount | Payment Type
-----------|--------------------------|-------------
1 | 10.00 | Card
1 | 5.00 | Cash
2 | 5.00 | Cash
Output values
Average Customer Spend: 10.00
(customer 1 spent 15 total sum of 10 + 5, customer 2 spent 5 total)
User Clicks on Cash filter under Transaction Type:
Average Customer Spend updates to: 5.00
(customer 1 spent 5 total in cash, customer 2 spent 5 total in cash)
Answer
I suggest summarizing your Transactions table grouping by CustomerID and then taking an average over that table as follows:
AverageCustomerSpend =
AVERAGEX(
SUMMARIZE(Transactions,
Transactions[CustomerID],
"Total Spent", SUM(Transactions[Amount])),
[Total Spent])
This syntax says that we are summarizing the Transaction table grouping on CustomerID and defining a new column [Total Spent] defined by the sum of all amounts corresponding to that CustomerID. The table is then put inside an AVERAGEX function which iterates over each row in the table we just created and averages the [Total Spent] column.