Attached is a workbook. This is now called fun with table calcs, oh boy here it goes.
I made a three calculations. 2 of these are near identical they count the losses and the gains. They do this using a window_sum() function.
Now your table calculation to find out the percent change runs across your table. In terms of tableau speak it has partitioned (grouped) by city and addressed (ran along) the year. Hence each cities calculation is independent of one another.
But I what I wanted to do is to take your calculations value and compare it to 0. If it was greater than 0 then I summed up all instances of this. But what I did was group by year and ran along the city. As such we would get the number of gains per year.
But to do this and just get a single line we have to muck around a bit.
I moved city to the details shelf and edited your calculation from table across (there was no table) to the correct partitioning and addressing. In this case we can say compute using year. At this point, yes things look funny. I then made another table calculation to use as a filter.
FIRST()=0. What this will do will only keep the first line of data. It's a table calculation so it runs after all the other filters and calculations run, which is good for us. We can now take the WINDOW_SUM() of those that exceeded 0 and were below 0 and place these into the view.
These are nested table calculations. As there's the % change that runs for each city going across the years and the sum of those that were above 0 that runs for each year running through all the cities. So we have to edit these table calcs to behave appropriately. Open them up and choose edit table calc. There is the option to choose inner calc (your % change) and the outer calc (my window function).
These can be a bit heavy but do not despair! This is a pretty good place to start looking if you get stuck reading through my logic:The Vizioneer: Demystifying Nested Table Calcs in Tableau
city valuation changes.twbx 274.4 KB