12 Replies Latest reply on Jul 11, 2018 6:34 AM by Gerardo Varela

# Weighted average of percent change

So I was having this problem several months ago. Posted it here and couldn't get a solution. Eventually I settled on a solution that involved exporting a Tableau worksheet to Excel and then using that export as a data source in the same Tableau file that had exported it in the first place. Basically simulated a SQL subquery through Excel because it didn't seem that Tableau could do what I needed it to do.

Well I am back with more experience in Tableau and with the hopes that someone can solve my problem this time. I have gotten closer, but am still not quite there. Created a file to illustrate my problem full of meaningless data because I cannot share the real stuff.

https://public.tableau.com/profile/chris.chutter#!/vizhome/Problem_2/Sheet1?publish=yes

Basically I have a dollar total for companies with varying numbers of employees over two years. Calculating the percent change from the first year to the second year is easy with a table calc. Calculating a weighted average of those percent changes is easy in Excel, but I cannot figure it out in Tableau. Every time I get close I am told I cannot aggregate an aggregate or I cannot put table calcs in a LoD expression.

Should be simple. For each row, multiply the percent change calc by the percentage of the total number of employees that work at that company and then add all those values together. Basically the last column in my workbook gets me most of the way (% change * employees / Total Employees). I just need to be able to add them all together. Again this is super simple in Excel.

I have experimented with removing the company number pill from the rows area and coding every thing with LoDs. And I can get to the point of needing to use a nested LoD expression to add them together... and BAMM I have a table calc in an LoD again and it doesn't work. Also I am aggregating an aggregate and it doesn't work. There has to be a way to do this. I could explain how to do this to anyone in MS Excel in like 5 minutes.

• ###### 1. Re: Weighted average of percent change

Hi Chris,

Not sure if the final values (Next Step Copy) are what you're looking for?  If you can provide what they should be, this might then need to be modified to a table calc.  But without the expected results, I'm just guessing at this point.  This is what I came up with based on the description of the problem:

• ###### 2. Re: Weighted average of percent change

At first first glace I am not sure why your calc is returning something different than my Next Step calc. Really what I am looking for now is a way to total my final column. It should give a result of 7615.5%. Your calc to me appears that it should do exactly what mine did, yet it clearly doesn't as the values are different. I've never used total() before though.

...

Very strange. After digging into it it seems your calc really should give the same result as mine, but in one step instead of two. But for some reason the Total() is giving a different value as part of the equation than it does when all by itself. All by itself it returns 1169 as it should. But the calc does not return what it would be expected to if that were equating to 1169.

Either way, you have gotten it as far as I did. I am looking to total those values.

• ###### 3. Re: Weighted average of percent change

Hi Chris, like this ?

I embedded your %change calc into your next step calc and changed the table calculation to specific dimensions and added year.

HTH 18.1 attached

Peter

• ###### 4. Re: Weighted average of percent change

Hi Peter,

What he's looking to do is grand total (sum of all the values in the columns) so "Next Step" for him should total to: "It should give a result of 7615.5%".

Ideas?  Thx, Don

• ###### 5. Re: Weighted average of percent change

Hi Don,

I've started unpacking it but have to go play our company soccer match. Might get back to this later in the afternoon.

Not quite working yet, but I got to the calc where you can't use the % change inside the table calc (since it doesn't exist for 2017), so have converted it to a standard weighted average.

Happy if you take it from there.

1 of 1 people found this helpful
• ###### 6. Re: Weighted average of percent change

Hi Peter, I am at home now and so do not have access to Tableau presently and can't view your file until Tuesday. I'm not sure if this is where you are going with it, but I think I see where you might be going here and I think that might work. If the Table Calc is screwing it up, remove the table calc and separate each year into its own calculated field? Never thought of that. Unfortunately complicated and will require new calcs each year, but far better than exporting to Excel and then bringing it back in. My real data spans almost two decades so this would require a lot of calculated fields. I am interested to try running with this next week. Thanks Peter. (if this is not where you were heading, please correct me)

• ###### 7. Re: Weighted average of percent change

Okay, so turning each year into its own calculated field and each year's percent change calc into its own calculated field and completely bypassing any kind of table calc is tedious, but it works without exporting to Excel and then bringing it back into Tableau. So the solution is far from ideal, but at least it works. Thanks Peter!

• ###### 8. Re: Weighted average of percent change

Hi All,

I know this question is marked answered but you can wrap "Next Step" in a WINDOW_SUM() and get what you are after.

Regards,

Gerardo

1 of 1 people found this helpful
• ###### 9. Re: Weighted average of percent change

Thanks Gerardo,

That is unquestionably a lot simpler. Just need to test if that works for an aspect I didn't mention which is that there are three levels of hierarchy to what I am calling 'Company' in my example. I think I can make this work though!

• ###### 10. Re: Weighted average of percent change

Let us know how it turns out.

Regards,

Gerardo

• ###### 11. Re: Weighted average of percent change

Yes this works and saves me creating like 30 Calculated Fields in order to avoid Table Calcs. Thanks Gerardo.

• ###### 12. Re: Weighted average of percent change

You're welcome!

Regards,

Gerardo