5 Replies Latest reply on Aug 20, 2014 2:27 AM by Jan Brooijmans

# How can I create a constant total to use in a table calculation regardless of filter?

I have sales data with multiple customer segments and events. I would like to be able to calculate an index which compares the total sales distribution by event to the selected segment (filter) sales distribution by event (for instance, the total sales index would be 100 but when filtered on a strong segment it would be 150). The calculation would be Sum of Sales % / Grand Total Sales % for column * 100

I hope that was clear and not too confusing. I have been searching online and I cannot seem to find any solutions to this.

Has anyone tried to do anything similar to this in the past, and how did you figure it out?

Thanks

• ###### 1. Re: How can I create a constant total to use in a table calculation regardless of filter?

Hi Vito,

I'm not sure I understand exactly. Maybe you could post a workbook (twbx file)?

However, table calculations take into account whatever is in the view, and if there is data that is filtered out it won't be included in the calculation. One way to overcome this is to create a second calculation that sets the value to either 'hide' or 'show'. Then, right click on the 'hide' section (once this calc is on the view) and select Hide. This won't actually filter out any data, it just won't show it.

Hope this helps!

-Tracy

1 of 1 people found this helpful
• ###### 2. Re: How can I create a constant total to use in a table calculation regardless of filter?

Thanks Tracy,

I think what I'm trying to get at is figuring out how to use a constant benchmark to compare filtered values to. I'm probably not explaining it very well, but I would like to compare the sales of one event to the average sales of all events.

I hope that is clearer

Vito

• ###### 3. Re: How can I create a constant total to use in a table calculation regardless of filter?

you can also use a filter on a table calculation to filter the data, and the filtering with happen after the evaluation of all table calculations, something like:

LOOKUP(ATTR([Segment]),0)

This way the filter on Segment happens after computation. (just be sure a Dimension pill for Segment exists on the worksheet on a shelf other than the Filter shelf)

see the attached for a simple example.

• ###### 4. Re: How can I create a constant total to use in a table calculation regardless of filter?

Thanks Joe, this is really cool.

• ###### 5. Re: How can I create a constant total to use in a table calculation regardless of filter?

I'm having a similar issue, and I'm not getting it?

I want to show the distribution of a selection, as well as a benchmark based on the total (of all data). To show different views I'm using other dimensions. The result is that the total in the benchmark will change according to the view.

How can I keep this a constant?

See attached twbx for the simple version of my request in the coffee chain example.

Can someone help me with a step-by-step procedure?

thanks a lot,

Jan