Hi Scottsen, thanks for your reply. I’ll outline what’s going on, highlighting in bold where I’ve used calculated measures:
I’m having some trouble with a weighted average calculation I’m trying to achieve. Assuming I have a table of vehicle leasing agreements sales, I’ll provide an example dataset:
Salesperson
| Cost
| Life (months)
|
Roger
| 500
| 36
|
Roger
| 800
| 48
|
Roger
| 2000
| 48
|
Roger
| 750
| 36
|
Keith
| 3000
| 60
|
Keith
| 1500
| 36
|
Keith
| 2100
| 48
|
Roger
| 600
| 36
|
Keith
| 500
| 48
|
Keith
| 900
| 60
|
<tbody>
</tbody>
I’m trying to calculate an average of the “Life (months)" field, weighted by the value of the “Cost” field, and express that by “Salesperson.” The steps I'm using to calculate this are as follows:
1.Sum the “Cost” field by “Salesperson”
MyNumber:=SUM(Cost):
Roger = 4650
Keith = 8000
2.Divide each record of “Cost” by the corresponding “Sum of Cost by Salesperson”
MyPercentage:=[MyNumber]/CALCULATE([MyNumber],ALL([MyNumber])):” e.g. Roger:
Salesperson
| Cost
| Life (months)
| MyPercentage
|
Roger
| 500
| 36
| 0.107527
|
Roger
| 800
| 48
| 0.172043
|
Roger
| 2000
| 48
| 0.430108
|
Roger
| 750
| 36
| 0.161290
|
Roger
| 600
| 36
| 0.129032
|
<tbody>
</tbody>
3.Multiply the “Life (months)” field by the “
MyPercentage” measure
ThisWontSum:=SUM([Life (months)])*[MyPercentage]:
Salesperson
| Cost
| Life (months)
| MyPercentage
| ThisWontSum
|
Roger
| 500
| 36
| 0.107527
| 3.87
|
Roger
| 800
| 48
| 0.172043
| 8.26
|
Roger
| 2000
| 48
| 0.430108
| 20.65
|
Roger
| 750
| 36
| 0.161290
| 5.81
|
Roger
| 600
| 36
| 0.129032
| 4.65
|
<tbody>
</tbody>
4.Finally, sum the “
ThisWontSum" measure:
Correct Answer: Roger = 43.24
I’ve gotten right up to completing step 3 but the final problem is that the sum of the “
ThisWontSum" measure is actually calculating the sum of “Life (months)” field (displaying 204 when it should be 43.24). When you create a PivotTable with Salesperson & Cost as ROWS and
ThisWontSum as VALUES
, ThisWontSum is calculating the individual rows perfectly – it’s just the Grand Total causing me grief.
Sorry if that’s a bit expansive; I’ve just tried to break it all down as clearly as possible. P.S. I’ve thrown “Keith” into the data set merely to demonstrate that there is >1 salesperson.
Thanks in advance for your help scottsen!
Rich