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]))
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.
Filter_example.twbx.zip 209.6 KB
Another strategy, which may be easier is to filter using a table calculation.
You'd create a new calculated field, say Product filter =
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.
And here describing why table calc filters can't be used as global filters: