cancel
Showing results for
Did you mean:
Member

## Weighted Average Survey Score

I am trying to add a column to a table which is:

Average Score * Contract Value / Count of Survey Scores

I have already added a column to find the average score for each customer but am looking for the desired column below.

 Customer Survey Score Contract Value Average Score Desired Column A 10 90 10 900 B 10 40 10 400 B 40 10 400 C 120 6.7 268 C 120 6.7 268 C 7 120 6.7 268 C 5 120 6.7 268 C 8 120 6.7 268

I tried the below formula but I was getting errors.

```Desired Column =
(CALCULATE(
AVERAGE('Table'[Contract Value]),
ALLEXCEPT('Table','Table'[Customer]))
*
CALCULATE(
AVERAGE('Table'[Contract Value]),
ALLEXCEPT('Table','Table'[Customer])))
/
CALCULATE(
COUNT('Table'[Survey Score]),
ALLEXCEPT('Table','Table'[Customer])```

I then want to show a card which shows the Sum of each unique customers new value in the desired column. So in this example the card would show 1,568 (900+400+268)

Thanks

Paul

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Weighted Average Survey Score

Hi @PaulCo,

Based on my test, you should be able to use the formula below to create the [Desired Column] calculate column in your table.

```Desired Column =
DIVIDE (
CALCULATE (
AVERAGE ( Table1[Survey Score] ),
ALLEXCEPT ( Table1, Table1[Customer] )
)
* Table1[Contract Value],
CALCULATE (
COUNT ( Table1[Survey Score] ),
ALLEXCEPT ( Table1, Table1[Customer] )
)
)
```

Then you can use the formula below to create a measure to calculate the Sum of each unique customers new value in the desired column.

```Measure =
SUMX (
SUMMARIZE (
Table1,
Table1[Customer],
"Desired Column1", MAX ( Table1[Desired Column] )
),
[Desired Column1]
)
```

Regards

4 REPLIES 4
Member

## Re: Weighted Average Survey Score

Make a measure
sumx(table, divide([average score] * [contact value], countrows(values(table[survey scores]))))

Using a measure would be better to use instead of a column in this circumstance.
I have put average score, contact value etc as measures also. If you keep them as columns then wrap it sum(table[average score]) for each column instead.
Super User

## Re: Weighted Average Survey Score

Hi,

Try this calculated field formula

`=SUMX(SUMMARIZE('Table',[Customer],"ABCD",MIN('Table'[Average Score])*MIN('Table'[Contract Value])/COUNT('Table'[Survey Score])),[ABCD])`

Hope this helps.

Super Contributor

## Re: Weighted Average Survey Score

Hi @PaulCo,

Based on my test, you should be able to use the formula below to create the [Desired Column] calculate column in your table.

```Desired Column =
DIVIDE (
CALCULATE (
AVERAGE ( Table1[Survey Score] ),
ALLEXCEPT ( Table1, Table1[Customer] )
)
* Table1[Contract Value],
CALCULATE (
COUNT ( Table1[Survey Score] ),
ALLEXCEPT ( Table1, Table1[Customer] )
)
)
```

Then you can use the formula below to create a measure to calculate the Sum of each unique customers new value in the desired column.

```Measure =
SUMX (
SUMMARIZE (
Table1,
Table1[Customer],
"Desired Column1", MAX ( Table1[Desired Column] )
),
[Desired Column1]
)
```

Regards

Highlighted
Member

## Re: Weighted Average Survey Score

Worked Perfectly, Thanks!