Average Value Unaffected By Filters

[ledders2023]

Hi everyone

I'm struggling with something I know should be relatively straightforward.

I have three tables: Regions (one side) related to Locations(many side). And Locations (one side) related to Audits (many side).

I have created a measure:

AvgAuditScore = AVERAGE(Audits[Audit Score]).

I have created a measure:

Avg Audit Score - Group =

CALCULATE(

        [Avg Audit Score],

        ALL(Homes),

        ALL(Regions)

        )

Both these measures behave in the way I expect - the first is affected by all filter contexts, and the second is only affected by the filter context from the Audits table so that the average score is for the Company Group.

I want to create a measure that calculates the average score for the Regions.  I still want it to be affected by the filter context from the Audits table, but I want ignore Location filters so that in a table visual it reads:

Location	Location Average	Group Average*	Region Average**
A	87.44	85.55	86.00
B	89.89	85.55	86.00
C	82.90	85.55	91.00
D	97.53	85.55	91.00

* The group average is calculated by the filter context applied from the Audits table (eg, audit type)

** The Region average is the average for the region associated with the location, and should be affected by the filter context from the audits table.

I've tried to use ALLEXCEPT(Regions, Region[Name]) but the Region Average column displays the same value Location average.

How would I acheive this?

Many thanks in advance.

[sjoerdvn]

Since your visual doesn't contain any columns from the Region table, there is no context to filter, that's why intially you got the average of all regions. There is a context with location, so with the bidirectional filter in place, the Region table is filtered. An alternative to making the relationship bidirectional all the time is to set it to bidirectional in the context of your measure. In your Calculate statement, add an argument: CROSSFILTER(Regions[RegionId],Locations[RegionId],BOTH)

[ledders2023 · Accepted solution]

So after much head scratching I got this working by making the relationship between the Regions and Location tables bidirectional.  The following code then worked:

Avg Audit Score - Regions =

 CALCULATE(

    [Avg Audit Score],

    REMOVEFILTERS(Locations),

    VALUES(Regions))

I now get the regional average for each location associated with a particular region, with the total for all regions equal to the the company group average.  The values can still be filtered by columns in the Audits table, which is the desired outcome.

I'm not sure if there is still a better way to code this without resorting to making the relationship bidirectional?

[123abc]

Your solution with the bidirectional relationship and the DAX code you provided seems to be working for your specific case. However, bidirectional relationships can have some performance implications, and it's generally a good practice to use them sparingly.

An alternative approach without the bidirectional relationship is to leverage the RELATED function to navigate from the Locations table to the Regions table. Assuming there's a direct relationship between Regions and Locations, and Locations and Audits, you can try the following DAX code:

Avg Audit Score - Regions =
CALCULATE(
[Avg Audit Score],
ALL(Locations[Location]),
VALUES(Regions[RegionName])
)

This code removes the filter on the Location table while keeping the filter from the Regions table. It assumes that there is a direct relationship between Locations and Regions, and Locations and Audits, and that the relationship between Regions and Locations is not bidirectional.

Remember, the effectiveness of DAX code often depends on the specific data model and relationships in your Power BI or other tabular model. It's always a good idea to test and evaluate the performance of different solutions with your specific dataset to determine the most efficient approach.

[ledders2023]

Thank you for your ongoing support here. Much appreciated and is really helping me think about filter context (which I'm still relatively new to).

The DAX you suggested:

Avg Audit Score - Regions =
CALCULATE(
[Avg Audit Score],
ALL(Locations[Location]),
VALUES(Regions[RegionName])
)

This only works when the cross filter between Regions and Locations is bidirectional. In a single direction relationship the value is the Company Group average.  However, I think this makes sense I think for the following reason:

When I create a table visual with Location[Name] in the first column I am creating a filter context with that column.  The Average Score - Region measure calculates the Audits[Score] for the Regions.  However, in order to retreive the value of the Region[Name] from the Regions table, the Location[Name] needs to filter the 'Regions' table to get the Region[Name] from the 'Regions' table.  This cannot happen in a single direction relationship from Region > Location, and so the relationship must be bidirectional.

For clarity my model is:

'Regions' ([RegionId]1>*[RegionId]) 'Locations' ([LocationId]1>*[LocationId]) 'Audits'

However, I think you might be on to something with the RELATED function as this I hope will overcome the issue of cross-filter direction.  I somehow need to introduce this into the 'Average Score - Region' measure so that when the filter context including 'Locations'[Name] is created in the visual, the measure traverses back through the relationships to 'Regions' to get the single value from Regions, which then computes the average for the region but ignores the filter context from the visual.

Any suggestions on how to intorduce RELATED given the information above would be greatly appreciated.  I'd love to mark this post 'Solved'! 

[ledders2023]

Thank you for your suggestion.  I have updated my measure to:

Avg Audit Score - Regions =

CALCULATE(

    [Avg Audit Score],

    ALLEXCEPT(Regions, Regions[Region Name]),

    ALL(Locations)

)

However, when I create a table with columns Location Name and the three measures, the Region Average is the same as the location average, which is not the desired value.  Irrespecitve of the location, the Region average should be the average for the region associated with that location.

[123abc]

Apologies for any confusion. It seems like there might be a need for a more explicit context transition to get the desired behavior. Try using the CALCULATETABLE function to define a table with the desired filter context for the Regions, and then use VALUES to create a single-column table with the Region values. Here's an updated version of the measure:

Avg Audit Score - Regions =
CALCULATE(
[Avg Audit Score],
CALCULATETABLE(
VALUES(Regions[Region Name]),
ALLEXCEPT(Locations, Locations[Location Name])
)
)

This measure should calculate the average audit score for each region, considering only the filter context from the Audits table, and ignoring the filter context from the Locations table. Please give this a try and see if it provides the correct results for your scenario.

[123abc]

To achieve the desired result, you can create a measure for Region Average by considering the filter context from the Audits table while removing the filter context from the Locations table. Here's a possible DAX formula for your measure:

Region Average =
CALCULATE(
[Avg Audit Score],
ALLEXCEPT(Locations, Locations[Region]),
ALL(Homes)
)

This measure uses ALLEXCEPT to remove the filter context from the Locations table, keeping only the filter context related to the Region. It then uses ALL(Homes) to ensure that the filter context from the Audits table is retained.

Make sure to replace Locations[Region] with the actual column and table names you are using in your data model.

Now, when you use this measure in a table visual, it should give you the average score for each region, taking into account the filter context from the Audits table while ignoring the filter context from the Locations table.