Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
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
i still cannot get the total row to sum the weighted score rows above it
what do you mean by "add a calculated column"?
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
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
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
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
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |