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,