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

Averages or roll-ups of given percentages

Blair Gibb Jul 7, 2016 10:59 AM

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:

Goal	Strategy	Task	Action	Percentage
First	Fix it	Fix A	A1	30
First	Fix it	Fix A	A2	40
First	Fix it	Fix B	B1	10
First	Fix it	Fix B	B2	20
First	Fix it	Fix B	B3	30

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

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.
Like Show 0 Likes(0)

Actions
2. Re: Averages or roll-ups 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

1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
3. Re: Averages or roll-ups 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!
Like Show 0 Likes(0)

Actions
4. Re: Averages or roll-ups 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 roll-up. I did learn about FIXED and will be able to use it in other visuals. Thanks!
Like Show 0 Likes(0)

Actions
5. Re: Averages or roll-ups 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.
Like Show 0 Likes(0)

Actions

Go to original post

Tableau Community

Averages or roll-ups of given percentages

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

2. Re: Averages or roll-ups of given percentages

3. Re: Averages or roll-ups of given percentages

4. Re: Averages or roll-ups of given percentages

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

Actions

More Like This