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

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

    adam.medcalf

      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.)

       

      Capture.GIF.gif

       

      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.

       

      Capture.GIF.gif

       

      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.

       

      Thanks for any advice!