Pivot Table Drill Down Calculations

[#1]

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:

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)

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

[#2 · Accepted solution]

Problem solved thanks to Tim @ QV! Incase anyone needs to know the aggrigation should have been on [Company] and [Theme] (which is the drill dow dimension.

Regards