Power Bi measure: Find average total spend for each ID

user_17162217 0
Viewed 163

I want to create a measure in Power Bi 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.

dax powerbi
1 Answers

The quickest way to do it would be to just create a measure that calculate the average of Table[Total_spend], and then use it in a table visual alongside Table[ID].

First, the measure :

Avg_Total_spend =
CALCULATE(
    AVERAGE(Table[Total_spend])
)

Then, you create a new table visual with columns:

  • Table[ID]
  • Table[Avg_Total_spend]

Or, as a table expression :

ADDCOLUMNS(
    SUMMARIZE(
        Table,
        Table[ID]
    ),
    "Average Table_spend",
    Table[Average_Table_spend]
)
user_58872670
Related