This should be fairly easy to accomplish by creating a calculated value (right click in Measures pane and select Create Calculated Field). Give your field a name, like 'Herfindahl Index' and then in the formula pane write
If you don't have a TOTAL measure, just replace it with ([A]+[B]+[C])
Does that do the trick?
Thanks for your answer. However, I just realised that I posted my input data incorectly.
Your solution would have worked fine given the data set I gave, however, what I actually have is:
COMPANY, CATEGORY, VAL
OneCo, A, 1
OneCo, B, 5
OneCo, C, 4
TwoCo, A, 5
TwoCo, B, 0
TwoCo, C, 3
ThreeCo, A, 0
ThreeCo, B, 0
ThreeCo, C, 4
I've been working with customised grand total calculations at the moment, as my calculation goes across columns.
Have you tried using the "WINDOW_" functions to address the sum of fields at the pane or table level?
I have created a sample with the data that you provide. Is this what you were looking for?
Calculation across Pane.twbx.zip 21.2 KB
Thanks for the help. Your example gives me the indivual ratios for each A,B,C, but not the sum of their squares.
The output I want is one row per company as follows:
Thanks for the tip about the window_ functions. No joy so far, but I'll keep trying things out
I have added a second table calculation that allows you to get to that result.
You will also notice that in order to ensure that the Final Result is displayed on the final row, I have used the SIZE() and the INDEX() functions.
Hope this helps..
Calculation across Pane.twbx.zip 16.2 KB
Very helpful, thanks.
Ideally, I don't want to show the extra columns and rows. Do I just have to 'hide' them manually, or is it possible to remove them properly ?
If I take category off the rows, and put it in the level of detail box, I get overlapping text. I've not found a way to get the correct results using things like 'IF FIRST()=0' or IF(INDEX()=SIZE()'.