Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PaulCo
Helper II
Helper II

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
v-ljerr-msft
Employee
Employee

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

View solution in original post

4 REPLIES 4
v-ljerr-msft
Employee
Employee

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

Worked Perfectly, Thanks!

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.