how to clear all filters on table except filters coming from another table

[Anonymous]

I have the following data model:

Fact Annual Employee Sales Target, Dimension Week, Dimension Employee

'Fact Annual Employee Sales Target'[Employee Key]     * - 1       'Dimension Employee'[Employee Key]

'Fact Annual Employee Sales Target'[Week Key]            * - 1       'Dimension Week'[Week Key]

My fact table holds my yearly sales targets for each of my employees.  Its time resolution is week (not year, despite the table name), which I know is redundant (but is necessary for other purposes).

I have a page where a user picks an employee (via 'Dimension Member'[Employee Name]) in a filter pane (which filters down [Job Title] in the data model).  The user also picks the year (via 'Dimension Week'[Year]).

I want to calculate the average annual sales target for the selected employee's "peer set" for the selected year, where I define "peer set" as all employees with the same [Job Title].  How can I do this?

I have tried the following measure thus far:

Annual Sales Tgt - Peer Set =

VAR __table = 
ADDCOLUMNS(

    SUMMARIZE(

        ALLEXCEPT( //necessary because the [Employee Name] filter must be cleared

            'Fact Annual Sales Target'

            ,'Dimension Week'[Year]

            ,'Dimension Employee'[Job Title]

        )

    )
    ,"Annual Sales Target"

    ,MAX('Fact Annual Employee Sales Target'[Target])    //necessary because this table is at the week (not year) resolution
)

RETURN

    AVERAGEX(

        __table

        ,[Annual Sales Target]

    )
 

However, this measure is returning the sales target of the selected employee, not the average of the employee's peer set.  I suspect that the problem is that I'm not properly clearing the filters.  But, I don't know how to correct that.

[CNENFRNL · Accepted solution]

Hi, @Anonymous , 

First, as to this part of your measure,

SUMMARIZE(
        ALLEXCEPT( //necessary because the [Employee Name] filter must be cleared
            'Fact Annual Sales Target'
            ,'Dimension Week'[Year]
            ,'Dimension Employee'[Job Title] )
...

ALLEXCEPT() doesn't materialize an intermediary table as expected. When used as a table function, ALLEXCEPT materializes all the unique combinations of the columns in the table specified in the first argument that are not listed in the following arguments.  In this case, the result only has the columns of the table and ignores the expanded table. You might want to refer to this article for more details, which I strongly recommend.

Secondly, I sketch a mockup accordingly to your desciprtion,

Then a measure is authored

Avg Target = 
VAR __titles =
    DISTINCT (
        SELECTCOLUMNS (
            ALLSELECTED ( 'Dimension Employee' ),
            "Title", 'Dimension Employee'[Job Title]
        )
    )
RETURN
    CALCULATE ( [Avg], ALL ( 'Dimension Employee' ), __titles )
//referenced measure: Avg = AVERAGE( 'Fact Annual Employee Sales Target'[Target] )

It seems to fit in my mockup, you might want to see more details in the attached file.