PierreA
asked on
Microsoft Reporting Services (Weighted Average)
I am using Reporting services to design a report. But some of the sub totals must be a weighted average (not a average).
Is there a function or a way to calculate the weighted average. I know in Crystal reports it is easy.
Please help
Thank you.
Is there a function or a way to calculate the weighted average. I know in Crystal reports it is easy.
Please help
Thank you.
Use a field in your data source to multiply the weight (volume, etc) by the number, and average that in the report.
ASKER
I am not sure what you mean.
In Crystal Reports you can select:
*Sum
*Count
*Average
*Weighted Average
The Weighted Average is weighted on another field.
Is it possable to do this in Reporting services?
In Crystal Reports you can select:
*Sum
*Count
*Average
*Weighted Average
The Weighted Average is weighted on another field.
Is it possable to do this in Reporting services?
What are you weighting it on?
ASKER
Example:
--------------------------
TONNES %
--------------------------
1900 0.78
1631 0.82
1451 1.93
1628 0.86
1540 0.46
706 0.49
----------- ----------
8856 [Weighted Avg]
====== =========
The weighted average is weighted on the TONNES.
There is a different between average and weighted average.
The average is a simply calculation, by only using the avg() function.
--------------------------
TONNES %
--------------------------
1900 0.78
1631 0.82
1451 1.93
1628 0.86
1540 0.46
706 0.49
----------- ----------
8856 [Weighted Avg]
====== =========
The weighted average is weighted on the TONNES.
There is a different between average and weighted average.
The average is a simply calculation, by only using the avg() function.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
To calculate the weighted average:
TONNES % TONNES * %
--------------------------
100 0.8 80
200 0.2 40
--------------------------
300 0.4 120
======================
The weighted average is 120 / 300 = 0.4
Where the average is 0.5
I don't want to add another field (VERY IMPORTANT).
Isn't there a way to do it through Custom code or something?
I can write a function in custom code, but then I need to send 2 parameters (An array of Tonnes and an array of %).
But sending the parameter (Fields!Tonnes.Value), I only get the first value of that field (in this case 100).
TONNES % TONNES * %
--------------------------
100 0.8 80
200 0.2 40
--------------------------
300 0.4 120
======================
The weighted average is 120 / 300 = 0.4
Where the average is 0.5
I don't want to add another field (VERY IMPORTANT).
Isn't there a way to do it through Custom code or something?
I can write a function in custom code, but then I need to send 2 parameters (An array of Tonnes and an array of %).
But sending the parameter (Fields!Tonnes.Value), I only get the first value of that field (in this case 100).
ASKER
I believe this question doesn't have an answer?
Solution provided and not accepted. questioner has closed mind as to solutions
ASKER
eg:
--------------------------
TONNES % Weighting
--------------------------
1900 0.78 1482 (1900*.78)
1631 0.82 1337
1451 1.93 2800
1628 0.86 1400
1540 0.46 708
706 0.49 346
----------- ----------
8856 [Weighted Avg] (Total of weighting)
====== =========
[Weighted Avg] = (Total of weighting)/8856
Hide Weighting Column. (THIS IS THE SOLUTION)
Podiluska you were very close with your answer so I will give you the points.
--------------------------
TONNES % Weighting
--------------------------
1900 0.78 1482 (1900*.78)
1631 0.82 1337
1451 1.93 2800
1628 0.86 1400
1540 0.46 708
706 0.49 346
----------- ----------
8856 [Weighted Avg] (Total of weighting)
====== =========
[Weighted Avg] = (Total of weighting)/8856
Hide Weighting Column. (THIS IS THE SOLUTION)
Podiluska you were very close with your answer so I will give you the points.