cancel
Showing results for
Did you mean:
Frequent Visitor

## Sum Total Row based on Weighted Average values above it

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
7 REPLIES 7
Super User

## Re: Sum Total Row based on Weighted Average values above it

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.

Proud to be a Datanaut!

Community Support Team

## Re: Sum Total Row based on Weighted Average values above it

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Sum Total Row based on Weighted Average values above it

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

Community Support Team

## Re: Sum Total Row based on Weighted Average values above it

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team

## Re: Sum Total Row based on Weighted Average values above it

Hi @aykim101,

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

## Re: Sum Total Row based on Weighted Average values above it

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

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

Community Support Team

## Re: Sum Total Row based on Weighted Average values above it

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.