3 Replies Latest reply on Feb 27, 2013 6:19 AM by stryder dsf

# calculate ratio of two values with different filter

my problem is i need to calculate the percentage of sales of a particular state(say California) to that of the total sales of USA.

I have a table of all the sales of stores with the states they are in. now if i apply a filter to see the stats of a particular state,I get its sum.I need to get the percentage of sales done in California to the total sales of USA but how can I get the total sales as after applying the filter the sales of other states vanish from the calculation.

• ###### 1. Re: calculate ratio of two values with different filter

One strategy is to duplicate the data source, since filters are not applied across data sources.

Once you have your main view; for example, a list of products sales, and % of total sales (step 1 in attached workbook),

1. Duplicate the worksheet

2. Right-click on data source > Duplicate.

3. Right-click on the % total table calculation > Edit Table Calculation > Edit Formula and replace the denominator with the same field from teh second data source:

SUM([Sales]) / TOTAL(SUM([Sales]))

becomes

SUM([Sales]) / TOTAL(SUM(([Sample - Coffee Chain (Access) (copy)].[Sales])))

4. Select the duplicate data source, rename Product to Product (copy) --- this breaks the link between the data sources and now when you filter on product in data source 1, it won't affect data source 2.

• ###### 2. Re: calculate ratio of two values with different filter

Another strategy, which may be easier is to filter using a table calculation.

You'd create a new calculated field, say Product filter =

LOOKUP(ATTR([Product]),0)

Now add this field to your filter shelf and add a quick filter. Note that table calculation filters can only be applied at the worksheet level. If you had a dashboard or wanted Product to be a global filter, you'd need to use the duplicate data strategy (or custom SQL, ...).

There a nice thread here on the table calc filter technique.

Dynamically show/hide dimensions from Quick Filter selection

And here describing why table calc filters can't be used as global filters:

Problem using Table Calc as a Global filter

• ###### 3. Re: calculate ratio of two values with different filter

Thanks Jim,

that worked.