Forum Discussion

SevsBo's avatar
SevsBo
Icon for Responsive Resident rankResponsive Resident
1 year ago
Solved

Compare total average to per-row average?

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?

  • SevsBo's avatar
    SevsBo
    1 year ago

    Issue was resolved by creating a calculated table based on the Page-level filters, and calculating relevant averages within that table. This is was then used in measures within the existing tables to derive variance between row-calculated averages and absolute averages for the data.

6 Replies

  • Hi,

    If the number of columns to be retained are lesser than the ones which need to be cleared, then consider using the ALLEXCEPT() function.

    • SevsBo's avatar
      SevsBo
      Icon for Responsive Resident rankResponsive Resident

      I have tried that, using ALLEXCEPT on the two tables that filter the results on page-level, but the issue is some of those are also included in the actual table visual, so they filter the AVERAGE calculation to a single item.

      • v-dineshya's avatar
        v-dineshya
        Icon for Community Support rankCommunity Support

        Hi SevsBo ,

        Thank you for reaching out to the Microsoft Community Forum.

        Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.


        Regards,
        Dinesh

  • v-dineshya's avatar
    v-dineshya
    Icon for Community Support rankCommunity Support

    Hi SevsBo ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    Hi amitchandak , Ashish_Mathur , Thanks for your prompt response.

     

    Hi SevsBo , If amitchandak , Ashish_Mathur  responses has resolved your query, Please mark their responses as  "Accept as solution" , to assist other community members in resolving similar issues more efficiently.

    Regards,

    Dinesh