Is it possible to have an average measure summed in PowerPivot.
I am rolling up timesheet data and subtracting the actual from the estimate for each feature then rolling that up to the release level.
I am using PowerPivot measures to get the estimate and the actual.
The estimate is an average because it is stored in the task in the timesheet database.
The actual is a sum of all time booked to that task.
Estimate:=Average([ROMEstimate])
Actual:=Sum([Capital])
Variance:=[Actual]-[Estimate]
This works on the feature level but not the rolled-up release level. I do not want an average there but a sum of the averages below that.
[TABLE="width: 560"]
<tbody>[TR]
[TD="align: center"]Feature[/TD]
[TD="align: right"]Estimate[/TD]
[TD="align: right"]Actual[/TD]
[TD="align: right"]Variance[/TD]
[/TR]
[TR]
[TD]Release[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]
[TD="align: right"]36[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]-28[/TD]
[/TR]
[TR]
[TD]
[TD="align: right"]125[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]-109[/TD]
[/TR]
[TR]
[TD]
[TD="align: right"]20[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]-16[/TD]
[/TR]
[TR]
[TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]-7[/TD]
[/TR]
[TR]
[TD]
[TD="align: right"]56[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]-32[/TD]
[/TR]
</tbody>[/TABLE]
To avoid the average I did try a separate table with the estimate for each task but could not get that measure to work.
Any ideas?
I am rolling up timesheet data and subtracting the actual from the estimate for each feature then rolling that up to the release level.
I am using PowerPivot measures to get the estimate and the actual.
The estimate is an average because it is stored in the task in the timesheet database.
The actual is a sum of all time booked to that task.
Estimate:=Average([ROMEstimate])
Actual:=Sum([Capital])
Variance:=[Actual]-[Estimate]
This works on the feature level but not the rolled-up release level. I do not want an average there but a sum of the averages below that.
[TABLE="width: 560"]
<tbody>[TR]
[TD="align: center"]Feature[/TD]
[TD="align: right"]Estimate[/TD]
[TD="align: right"]Actual[/TD]
[TD="align: right"]Variance[/TD]
[/TR]
[TR]
[TD]Release[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]
Feature A
[/TD][TD="align: right"]36[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]-28[/TD]
[/TR]
[TR]
[TD]
Feature B
[/TD][TD="align: right"]125[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]-109[/TD]
[/TR]
[TR]
[TD]
Feature C
[/TD][TD="align: right"]20[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]-16[/TD]
[/TR]
[TR]
[TD]
Feature D
[/TD][TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]-7[/TD]
[/TR]
[TR]
[TD]
Feature E
[/TD][TD="align: right"]56[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]-32[/TD]
[/TR]
</tbody>[/TABLE]
To avoid the average I did try a separate table with the estimate for each task but could not get that measure to work.
Any ideas?