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.

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Super Contributor

## 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

Highlighted
Super Contributor

## 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.
Super Contributor

## 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.
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"?

Super Contributor

## 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.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 123 members 1,356 guests
Recent signins: