
1. Re: Averages or rollups of given percentages
Blair Gibb Jul 7, 2016 4:58 PM (in response to Blair Gibb)I ended up adding formulas in the Excel sheet that the users are using to enter the data. Not perfect, but in a hurry.

2. Re: Averages or rollups of given percentages
Simon Padget Jul 8, 2016 8:05 AM (in response to Blair Gibb)Hi Blair,
Good question  I think I have solved it here.
What you need is a FIXED calculation to "fix" the average at the Task level. So by using:
{FIXED [Task]: AVG([Percentage])}
you are able to do this.
I hope this is what you are after?
Simon

Percentages 20160708.twbx 140.3 KB


3. Re: Averages or rollups of given percentages
Blair Gibb Jul 11, 2016 8:08 AM (in response to Simon Padget)That looks promising, but I would need a formula like that for each level. I’m going to look at it a little later as this project is due. I think the Excel formula approach is ok for now.
Thanks!

4. Re: Averages or rollups of given percentages
Blair Gibb Jul 29, 2016 5:09 PM (in response to Simon Padget)FIXED still uses all the values below to calculate the average, so it gives the same answer as the rollup. I did learn about FIXED and will be able to use it in other visuals. Thanks!

5. Re: Averages or rollups of given percentages
Blair Gibb Aug 3, 2016 12:29 PM (in response to Blair Gibb)updating: I can create a calculated filed that works at the task level,
{FIXED [Task]: SUM([% Complete])/COUNT([% Complete])}
and then use that to create another calculated field at the strategy level:
{FIXED [Strategy] : AVG([TaskAVG])}
These produce the correct values for my purposes. I can get them to show up in the Tooltip, but I haven't figured out how or if I can put them in the table. They would need to replace the totals in the table for the appropriate level. Not sure that is possible.
Any ideas would be helpful.