5 Replies Latest reply on Aug 3, 2016 12:29 PM by Blair Gibb

# Averages or roll-ups of given percentages

I am collecting data about projects. There are goals, strategies, tasks, and actions, and these make up a great hierarchy in Tableau. The user will enter the percent complete for each action (or possibly task if there are no actions defined). I want to show the roll-up of these percentages at each level.

The issue is that when average is used it will average all the fixed percentages (no weighting) from levels below it:

FirstFix itFix AA130
FirstFix itFix AA240
FirstFix itFix BB110
FirstFix itFix BB220
FirstFix itFix BB330

In this scenario, the completion for percentage for Task Fix A should be 35 and for Fix B 20. If this is entered in Tableau, when the hierarchy is rolled up to Strategy, the rolled up percentage shows as 30%. But it really should be the average of the roll-ups at the task level, 27.5%.

I'm having trouble coming up the right table calc/calculated field or combination to make this work this way.

Any ideas?

• ###### 1. Re: Averages or roll-ups of given percentages

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 roll-ups of given percentages

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:

you are able to do this.

I hope this is what you are after?

Simon

1 of 1 people found this helpful
• ###### 3. Re: Averages or roll-ups of given percentages

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 roll-ups of given percentages

FIXED still uses all the values below to calculate the average, so it gives the same answer as the roll-up. I did learn about FIXED and will be able to use it in other visuals. Thanks!

• ###### 5. Re: Averages or roll-ups of given percentages

updating: I can create a calculated filed that works at the task level,

and then use that to create another calculated field at the strategy level: