Formula help

[MichelleR]

Hi everyone! If I am writing to you it is because after two days of searching on the internet for a solution, I finally realized am well beyond my depth with the issue I am about to present to you. Pleeeease help!

Here is the data structure I have in Power Query:

Criteria N1	Criteria N2	Criteria N3	Criteria N4	Criteria N5	Evaluation result	Overall Score
Financial	AAAA	AAA	AA	A	5	2
Financial	AAAA	AAA	AA	B	4	1
Financial	AAAA	AAA	BB	C	5	5
Financial	AAAA	AAA	BB	D	1	5
Financial	AAAA	AAA	BB	E	5	4
Financial	AAAA	AAA	BB	F	5	2
Financial	AAAA	AAA	CC	G	4	1
Financial	AAAA	BBB	CC	H	5	2
Financial	BBBB	BBB	CC	I	4	4
Financial	BBBB	BBB	CC	J	4	1
Performance	BBBB	BBB	DD	K	2	3
Performance	BBBB	BBB	DD	L	5	3

Now I would like to calculate the following:

Weight (%) = Evaluation result / Sum of the evaluation results for each criteria N3 group
example for the first line of the data I gave it would be = 5 (the evaluation result for Criteria N5 A) / 29 ( the sum of all the Criteria N3 AAA entries)

Then, I need to calculate

Weight = Weight (%) * Evaluation Result

And finally

Score = Weight * Overall Score

My issues are:
- Would I be better off with a measure or a calculated column?
- Can you please tell me what formula i need to write to make those calculations?
Thank you in advance!

[Jihwan_Kim · Accepted solution]

Hi, @MichelleR 

In my opinion, please try Calculated Measures.

Please also check the link down below, that is the sample pbix file.

Weight % =

DIVIDE (

SUM ( 'Table'[Evaluation result] ),

CALCULATE (

SUM ( 'Table'[Evaluation result] ),

ALLEXCEPT ( 'Table', 'Table'[Criteria N3] )

)

)

weight = [Weight %] * SUM('Table'[Evaluation result])

Score = SUM('Table'[Overall Score]) * [weight]

 https://www.dropbox.com/s/2dsrjth8jdgk9wy/MichelleR.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[amitchandak]

@MichelleR , As new measure

Weight (%) =

divide(sum([Evaluation result]), sumx(filter(allselected(Table), Table[Criteria N3] = max(Table[Criteria N3])),[Evaluation result]))

New column

Weight (%) =

divide(([Evaluation result]), sumx(filter(allselected(Table), Table[Criteria N3] = earlier(Table[Criteria N3])),[Evaluation result]))