Sum Total Row based on Weighted Average values above it

[aykim101]

Trying to sum the values of a weighted average column using the records above the "Total" row

I can only get the Total Row to use the same weighted average calculations as the rows above it

Projects	Count	Percent of Total	Average Score	Weighted Score
				=C*D
Project A	10	29%	3.25	0.93
Project B	5	14%	3.10	0.44
Project C	20	57%	2.50	1.43
Total	35	100%	2.95	2.80	=.93+.44+1.43
				Need to get 2.8 NOT 2.95
			I get this	2.95	=100% * 2.95

[v-yulgu-msft]

Hi @aykim101,

Have you worked it out? If so, would you please kindly mark the corresponding reply as an answer? If not, please feel free to ask.

Regards,

Yuliana Gu

[aykim101]

i still cannot get the total row to sum the weighted score rows above it

what do you mean by "add a calculated column"?

[v-yulgu-msft]

Hi @aykim101,

What I meant "Add a calculated column" is creating a column rather than measure.

Could you please teel me in your scenario, whether above fields [Count], [Percent of Total], [Average Score] and [Weighted Score] are columns or measures?

Regards,
Yuliana Gu

[v-yulgu-msft]

Hi @aykim101,

For 'Average Score', you could create a measure like:

Average Score = AVERAGE(Sheet1[Score]) 

For 'Weighted Score', you could create a calculated column like:

Weighted Score Col = Sheet1[Percent of Total]*[Average Score]

Best regards,
Yuliana Gu

[aykim101]

thank you for the reply

that is the logic being used; however the "Total" row needs to sum the row values above it and NOT use the same Weighted Score calculation

[v-yulgu-msft]

Hi @aykim101,

If you add a calculated column into table visual and enable the total row, the "Total" row will sum the row values above it automatically.

Regards,
Yuliana Gu

[Greg_Deckler]

Assuming these are measures, you generally have to use an IF statement coupled with a HASONEFILTER statement to calculate the measure one way versus another for the individual rows versus the total row.