Average of Grouped Sum

Question

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)

I'm sure there is a way, but it's difficult to answer without an actual example to work with. Please create an mcve. — Alexis Olson
Im not sure how to create a solid code example for PowerBI since I mostly use the drag and drop tools.... The most I have done with writing code within the tool is modify the existing code in a quick measure, and none of the quick measure has anything near what I need. Ill try and write a more concise example in text. — Drew Major
I don't really need a code example for this. I want a basic example of your input data and what you want your output to be. Write out a simplified table with the columns you mentioned and enough rows to capture the idea you're after and then an output table which demonstrates how that table should be grouped and averaged. Basically, I want something concrete to work with. — Alexis Olson
Updated to include an html snippet that includes an input table and expected outputs based on user interaction — Drew Major

Alexis Olson Alexis Olson · Accepted Answer · 2018-06-20T19:47:22

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.

Average of Grouped Sum

1 Answers