Removing report filters from totals in hierarchy

[mike_asplin]

I'm sure I have done this before but cant remember how!!!

I have a table MSOA_mapping that contains data in geographical hierachy with MSOA being the smallest

MSOA

LA

County

Region

Each MSOA and each LA has been ranked based on some demographic data

An example looks like this

[Target MSOA Rank] gives each MSOA a score, but i seemed ot have to create the 2nd measure to get it to average over the LA level

AVG MSOA Rank = AVERAGEX(ADDCOLUMNS(SUMMARIZE(MSOA_Mapping,MSOA_Mapping[MSOA Name]),
               "RankMSOA",[Target MSOA Rank]),
               [RankMSOA])

[Target LA Rank] scores each LA then I average it obver hgiher levels using

AVG LA Rank = IF(ISINSCOPE(MSOA_Mapping[MSOA Name]),BLANK(),
                    AVERAGEX('Age Range Selection',
                            AVERAGEX(ADDCOLUMNS(SUMMARIZE(MSOA_Mapping,MSOA_Mapping[LA Name]),
                                        "RankLA",[Target LA Rank]),
                                        [RankLA]))
                  )

So my question is if i apply a filter on the report page of Target MSOA Rank less than 2 I get

however I would like the AVG MSOA Rank at the Ashfiled level to remain as 1.8 i.e at the LA level ignore the filter applied to the MSOA level. similarly the AVG LA Rank number to not be affected

I've tried using removefilters and ALL (MSOA Name) and makes no difference. I'm guerssing the filter applied on the report page is the last thing the measure before calcuating sees so essentially you cant override it?

Any advice appreciated

Mike

[johnt75 · Accepted solution]

Try

AVG MSOA Rank =
AVERAGEX (
    CALCULATETABLE (
        ALL ( MSOA_Mapping[MSOA Name] ),
        VALUES ( MSOA_Mapping[LA Name] )
    ),
    [Target MSOA Rank]
)

This removes all filters from the MOA name but then reapplies the filter on the LA so that only MOAs for the currently selected LA will be shown.

You can apply the same principle with

AVG LA Rank =
IF (
    ISINSCOPE ( MSOA_Mapping[MSOA Name] ),
    BLANK (),
    AVERAGEX (
        'Age Range Selection',
        AVERAGEX (
            CALCULATETABLE (
                ALL ( MSOA_Mapping[LA Name] ),
                VALUES ( MSOA_Mapping[County] )
            ),
            [Target LA Rank]
        )
    )
)

[mike_asplin]

The MSOA one works great. Much appreciated

The second one producing something odd where AVG LA Rank test is your code. Not obvious what it is averaging as average of all LA is 3.2

[johnt75]

What exactly is it supposed to calculate?

[mike_asplin]

shoudl be the same as this

AVG LA Rank = IF(ISINSCOPE(MSOA_Mapping[MSOA Name]),BLANK(),
                    AVERAGEX('Age Range Selection',
                            AVERAGEX(ADDCOLUMNS(SUMMARIZE(MSOA_Mapping,MSOA_Mapping[LA Name]),
                                        "RankLA",[Target LA Rank]),
                                        [RankLA]))
                  )

, but not change if you filter some of the LA names out. Your code looks logical.

The La Rank is being worked out 6 times based on some criteria in the [Age Range Selection] hence averaging to get average rank across the 6 sets of criteria. I treid removing that bit and didnt make any difference.  

the [Target LA Rank] has a reference to LA Name so maybe the ALL part is feeding through to that calculation

Target LA Rank = 

VAR
CHA12=IF([Pop % A12 11-20]>=0,1,0)
VAR
CHA=IF([Pop % A 11-20]>=0,1,0)
VAR
IG=IF( CALCULATE(VALUES(MSOA_Mapping[Income Tgt LA]),
                         FILTER(MSOA_Mapping,MSOA_Mapping[LA Name]=SELECTEDVALUE(MSOA_Mapping[LA Name])))
                            <>BLANK(),1,0)
VAR
PopPctA5YLA=IF( [Pop % A 5Y LA]>=0,1,0)
VAR
PopPctA10YLA=IF([Pop % A 10Y LA]>=0,1,0)
VAR
Density=IF([Pop Density Tgt A]>[Density 50%] && [Pop Density Tgt A]<=[Density 80%],2,IF(OR([Pop Density Tgt A]<=[Density 50%] && [Pop Density Tgt A]>[Density 20%],[Pop Density Tgt A]<=[Density 95%] && [Pop Density Tgt A]>[Density 80%]),1,0))
RETURN

IF( IG=1 && Density=2 && OR(PopPctA5YLA=1,PopPctA10YLA=1),
     1,
   IF(IG=1 && Density>=1 && OR(PopPctA5YLA=1,PopPctA10YLA=1),
             2,
             IF(Density>=2 && OR(PopPctA5YLA=1,PopPctA10YLA=1),
            3,
        4
        )
        ))

i'll have a rethink as get the gist behind your idea. Much appreciated.