cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PaulCo Member
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.

 

CustomerSurvey ScoreContract ValueAverage Score Desired Column 
A109010900
B104010400
B 4010400
C 1206.7268
C 1206.7268
C71206.7268
C51206.7268
C81206.7268

 

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
v-ljerr-msft Super Contributor
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] )
    )
)

c1.PNG

 

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. Smiley Happy

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

r2.PNG

 

Regards

4 REPLIES 4
tmckenzie Member
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
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.

v-ljerr-msft Super Contributor
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] )
    )
)

c1.PNG

 

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. Smiley Happy

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

r2.PNG

 

Regards

PaulCo Member
Member

Re: Weighted Average Survey Score

Worked Perfectly, Thanks!