1 2 Previous Next 18 Replies Latest reply on May 12, 2011 5:24 AM by Srinivas Dronamraju

# How do create Calculation that sums up across whole Table? not that easy...

This sounds simple, but unfortunately it's not. Or I am missing something.

I want to calculate expected values in a contingency table, see http://en.wikipedia.org/wiki/Contingency_table. I.e. Each cell should be

`((Row Total)*(Column Total)) / (Grand Total)`

The data is as such:

```Brand    Image

Orange    A
Apple    B
Orange    B
Apple    A
Orange    A
Lemon    A
Apple    B
Orange    B

```

Resulting in the following contingency table:

```    A    B    Grand Total

Apple    1    2    3
Lemon    1    0    1
Orange    2    2    4
Grand Total    4    4    8

```

E.g. for cell Apple/A I would now like to have the value (4*3)/12 in the according cell, which is the expected frequency if the two dimensions were independently distributed. How could I achieve this? I am only able to define table calculations for Row Sums and Col Sums, but not for Total Sums.

thanks!

• ###### 1. Re: How do create Calculation that sums up across whole Table? not that easy...

Erratum: i meant that the calculation should be  ((Row Total)*(Column Total)) / (Grand Total) = (4*3) / 8

• ###### 2. Re: How do create Calculation that sums up across whole Table? not that easy...

What I did:

Edit Expected value

Change to: ([Column Sum]*[Row Sum])/ TOTAL(SUM([Number of Records]))

Click "Default Table Calculation" at the top right of calculation window (just found this working on this - amazing)

Move Image and Brand to Compute Using colum, order along Number of Records

Click OK

Click OK

Does that do it?

• ###### 3. Re: How do create Calculation that sums up across whole Table? not that easy...

Fantastic, this seems to work! Thank you so much!!

If you've got time, maybe you can point me where this 'Order along' is explained in more detail. But it's great to see it working.

• ###### 4. Re: How do create Calculation that sums up across whole Table? not that easy...

I modified your calc fields to a WINDOW_SUM() instead of TOTAL(), adding one for your "Grand Total Calculation", and combining the three with one calc field.

I added a sheet that use the measure Values shelf to display all the values for the parts of the final calculated field.

I can make a video explanation of this and post a link to it as a comment tonight

• ###### 5. Re: How do create Calculation that sums up across whole Table? not that easy...

Thanks Joe, that also does the trick! Great!

• ###### 6. Re: How do create Calculation that sums up across whole Table? not that easy...

Hello Joe

Kindly help me out with this problem...I am currently calculating the percent of total of the share of market for a particular product by doing sum(dollars spend)/total(sum(dollars spend))  when i select all the brands in the market then this calculation is giving me correct values but when i try to select individual players in the market thats when the solution is aggregated and i am getting 100% for each brand.Kindly help me out

• ###### 7. Re: How do create Calculation that sums up across whole Table? not that easy...

Srinivas,

Sounds like an order of operations situation. You want to perform your filter on "Nameplate" after the table calculation had been evaluated.

One option is to create a calc filed like:

`LOOKUP(ATTR([Nameplate]),0)`

and place that on the Level of Detail shelf, and then instead of having a filter directly on your "Nameplate" field, have the quick filter on this calculated field.

Because this calc field is a table calc, it will be evaluated at the same time as the other table calculation, and I believe produce the results as you want.

I have attached a sample workbook showing before and after.

• ###### 8. Re: How do create Calculation that sums up across whole Table? not that easy...

It works....thank you so much Joe

• ###### 9. Re: How do create Calculation that sums up across whole Table? not that easy...

Joe

I was able to use the results of the calculation as a filter but is there a way i can make it global coz i need to apply the same aggreg (LOOKUP(ATTR([Nameplate]),0))  on all the sheets and need them to be dynamically selected.

• ###### 10. Re: How do create Calculation that sums up across whole Table? not that easy...

If you only want o look at one "Nameplate" at a time, then you can use a parameter and a calculated field like:

`(LOOKUP(ATTR([Nameplate]),0))=[Nameplate Parameter]`

and keep only when True.

If you want the ability to look at more than one at a time, with a multi-select, there are other options, the method to use depends on the the interaction you want to allow for:

- Do you only want the choice between a single nameplate and All nameplates?

- Do you only want to allow for a limit of 3 nameplates at a time?

- Or something else?

There are many options available, and many more possibilities in filtering options, with many methods and approaches, and the key in selecting a route is understanding your constraints, and what you want for a result.

• ###### 11. Re: How do create Calculation that sums up across whole Table? not that easy...

If i make changes in the single drop down menu that changes should replicate over the entire dashboard.I am looking for a multi select drop down list

• ###### 12. Re: How do create Calculation that sums up across whole Table? not that easy...

okay, then how about the attached?

• ###### 13. Re: How do create Calculation that sums up across whole Table? not that easy...

Hi joe

That method would be awesome for small number of nameplates but what about many nameplates? and how can i create a parameter with many nameplates..I have many name plates..

• ###### 14. Re: How do create Calculation that sums up across whole Table? not that easy...

We have segments like B car C car  etc and each segment has around 20 name plates so is there a way we can have relevant values in the drop down parameters rather than showing all the values?

1 2 Previous Next