Forum Discussion

PaulCo's avatar
PaulCo
Icon for Helper II rankHelper II
8 years ago
Solved

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

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    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. :smileyhappy:

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

     

    Regards

4 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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. :smileyhappy:

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

     

    Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
    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.
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    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.