topic Re: Find average total spend for each ID in DAX Commands and Tips

Find average total spend for each ID

Anonymous — Thu, 30 Jun 2022 11:01:41 GMT

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.

Re: Find average total spend for each ID

amitchandak — Thu, 30 Jun 2022 11:15:57 GMT

Anonymous , Try like

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

Re: Find average total spend for each ID

Anonymous — Thu, 30 Jun 2022 12:00:14 GMT

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

Re: Find average total spend for each ID

Anonymous — Wed, 06 Jul 2022 09:07:16 GMT

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.