Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in BI by (17.6k points)
edited by

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) 

1 Answer

0 votes
by (47.2k points)
  •  summarize your Transactions table by grouping CustomerID and then take an average over that table in this way:

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] which is 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.

Related questions

0 votes
1 answer
0 votes
1 answer
asked Jul 13, 2019 in BI by Vaibhav Ameta (17.6k points)
0 votes
1 answer
asked Mar 15, 2023 in BI by neelimakv (32.5k points)
0 votes
1 answer

Browse Categories

...