Find average total spend for each ID

[Anonymous]

I want to create a measure that finds the average of the total_spend for each ID. My table looks like this:

ID	Total_Spend
1234	£             34.00
1234	£             34.00
1234	£             34.00
4325	£             56.00
4325	£             56.00
4325	£             43.00
4325	£             43.00
5678	£             12.00
5678	£             12.00

I've tried:

AvgIDSpend=
AVERAGEX (
SUMMARIZE (
Table,
Table[ID],
"Total Average", SUM( DISTINCT ( Table[Total_spend] )
),
[Total Average]
)

But got this error- The SUM function only accepts a column reference as an argument.

[amitchandak]

@Anonymous , Try like

 AvgIDSpend=
AVERAGEX (
SUMMARIZE (
Table,
Table[ID],
"_1", Sumx(Values(Table[Total_spend]), [Total_spend])
),
[_1]
)

[Anonymous]

Unfortunately, that gives me the average spend across all ID's, not each one. So when I put it on a table I get the same average for every ID

[Anonymous]

Hi @Anonymous ,

I think amitchandak's code could work. Here I have a test by creating a measure by his code in my sample.

If this reply still couldn't help you solve your problem, please show me a screenshot with the result you want. This will make it easier for me to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.