Weighted Average Survey Score

[PaulCo]

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

[v-ljerr-msft · Accepted solution]

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

[PaulCo]

Worked Perfectly, Thanks!

[Ashish_Mathur]

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.

[Anonymous]

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.