3 Replies Latest reply on Sep 27, 2013 9:51 AM by adam.medcalf

# How to do "percent of total first" calculation ignoring partitions?

Hi everyone,

Am after some advice on what is probably a table calculation if possible!

I have a line chart showing count of unique people from a given cohort transacting in different categories as per the picture below. The cohort has been divided up into 2 segments, segment 0 and segment 1, shown on the left. (People may move between these segments at any time which is why the "1" segment increases in week 2 even though the total number of people in the analysis goes down.)

It’s easy to turn that into a “percent remaining” chart based on comparing all of the values in day 2, 3, 4 etc. to the first one using a “Percent from First” table calculation. That then treats each segment as being 100% as you would expect.

As people can move from 1 segment to another, what I actually want to do is show each segment as a % of the TOTAL count of people in the first place. To do this I need to work it out as a percent from sum of the first value in category 0 + first value in category 1. What I cannot work out how to do though is have the table cacluation refer to the “total first” and not  just the first in category 0 and the first in category 1. I looked through TOTAL, WINDOW_SUM etc. to no avail. They always keep the context of the partition they are in as far as I could tell.

Therefore what I would want for the first datapoint on the top chart is 130 / (130+127) = 51% , then the 2nd would be 120 / (130 + 127) = 47% etc. instead of 100% and 92% respectively.

If it makes a difference, my datasource is a hosted Powerpivot and I use Tableau 8.

• ###### 1. Re: How to do "percent of total first" calculation ignoring partitions?

Hi Adam, Just a suggestion that, this can be achieved easily if we use segment in color.

• ###### 2. Re: How to do "percent of total first" calculation ignoring partitions?

Here is another way to solve this using calculations that allows you to keep the segments separated out.

Hope this helps,

Dan

• ###### 3. Re: How to do "percent of total first" calculation ignoring partitions?

Hi,

Indumon, indeed I see how easy that can be but it doesn't quite give me what I was after. It is giving me the percentage of the blue line compared to the overal value of each week. But each week actually the overall total value changes, and I wanted to stick to dividing by the total in the first week. The biggest example is on day 4 where for the blue line I want the calculation of 8/(130+127) = 3%, whereas I think that approach is doing 8/(8+96) = 8%

But thanks, it's a useful technique for other purposes!

Dan, that solution does indeed work for me, thanks. And I don't think I would have come up with that in a hurry either, very clever :-)

[I had a little trouble implementing it as my data source is a cube so I can't change aggregations in Tableau ("sum is not a recognised function" etc. apparently ) but it works 100% on non-cube data, and I got the logic you used for "First day total" implemented in the cube itself and now it works great even on the cube.]