1 Reply Latest reply: Jan 5, 2010 6:51 AM by edtclarke RSS

    Pivot Table Drill Down Calculations

    edtclarke

      Hi,

      I am having trouble trying to get a calculation to work correctly in a pivot table with a drill down dimension. Here's a little bit of background:

      The data represents numbers of articles found about 6 different companies and a range of major and minor themes about those companies. I want the pivot to to display linear gauges where the bars will all be proportioned to the maximum value of the displayed set. For example:

      error loading image

      So Company A, Environment/Ethics is the highest value with 1513 and I want all the bars calculated as a proportion of this. You then can drill down to Minor Theme. I have the calculation working at the bottom level but it does not seem to work at the top level.

      The following pictures illustrate my point (I have added 2 text fields after the bars to show the 2 numbers needed to calculate the bars by dividing one by the other)

      error loading image

      error loading image

       

      The calculation I am using for the linear gauge is as follows:

      (count(uri) / max(total aggr(count(uri),Company, [Major Theme], [Minor Theme]))) * 100

      Can anyone shed any light on this as I am clearly missing some important little snippet of info to make this work!!!

       

      Many thanks in advance,

      Ed