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

    Weighted average of percent change

    Chris Chutter

      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.