We're trying to show how items in ColumnA compare against the average for all of the items, then to calculate actual and percentage-based difference to find the biggest outliers. This is shown in a table that has multiple columns from Table1 and Table2, some of which should continue to filter the end result, but not all.
The regular AVERAGE approach doesn't work as that is influenced by each row, so I think we need to use REMOVEFILTERS to prevent row-level filtering, but the issue is that we also have to re-add a large number of filters to get to the correct average number.
The page is using 11 filters, some of which have multiple selections, so the current solution looks like this:
Average_removed_filters =
CALCULATE(
AVERAGE(Table1[numbers]),
REMOVEFILTERS(Table1),
REMOVEFILTERS(Table2), --data for table visual comes from these two tables
Table1[ColumnB] = ...,
Table1{ColumnC] = ...,
. . .
Table2[ColumnA] = ...,
Table2[ColumnB] = ...
)
Only there are a lot of criteria to go through, which will make the whole DAX quite fragile if any of those filters change on a page level.
Is there a better way to do a calculation of AVERAGE across all items that can be shown on a page, and of row-based AVERAGE, that we can compare with the first?
