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:
CustomerID | Total Transaction Amount | Payment Type
1 | 10.00 | Card
1 | 5.00 | Cash
2 | 5.00 | Cash
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)