6 Replies Latest reply on Sep 12, 2012 1:27 AM by steve.parsons

# Sum of squares

Hi, I've just started using tableau, so apologies if my question is obvious.

I'm trying to display the sum of the squares of market shares (hefendahl index).

i.e. if a row has 3 values:

A  B  C  TOTAL

1  4   5   10

The value I want to calculate is:

(1/10)^2 + (4/10)^2 + (5/10)^2 = 0.42

Columns A, B and C could be totals from other dimensions.

Sample data:

Company, A, B, C

OneCo, 1, 4, 5

TwoCo, 5,0,3

ThreeCo, 0,0,4

I'd like to display:

OneCo     0.42

TwoCo     0.53125

ThreeCo   1

Any help would be greatly appreciated.

• ###### 1. Re: Sum of squares

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

([A]/[TOTAL])^2+([B]/[TOTAL])^2+([C]/[TOTAL])^2

If you don't have a TOTAL measure, just replace it with ([A]+[B]+[C])

Does that do the trick?

• ###### 2. Re: Sum of squares

Hi Robin,

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.

• ###### 3. Re: Sum of squares

Steve,

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?

Cheers

Siraj

• ###### 4. Re: Sum of squares

Hi Siraj,

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:

Company      CalculatedValue

OneCo          0.42

TwoCo          0.5061728

ThreeCo       1

Thanks for the tip about the window_ functions. No joy so far, but I'll keep trying things out

• ###### 5. Re: Sum of squares

Hi Steve,

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

Cheers

Siraj