Benchmarking using Filters (selected and comparison to remainder)

[bglibkowski123a]

I have a table with segments (e.g., Department, job tenure, education).  Each segment has selection options.  For example Departments (e.g., Marketing, Accounting, Finance).  I want to be able to select a segment option and see the average for that selected segment (e.g., Marketing = 80% average for Job Satisfaction).  Then I want to to be able to compare that Job Satisfaction score to all remaining departments combined together (e.g., Accounting and Finance departments Average of 75% Job Satisfaction).

The solution needs be dynamic in that I have multiple segments and the segments will change from report to report.  Therefore, I'm not looking for a solution that is manual, but is responsive to filtered/unfiltered selections. I'm stuck how to accomplish this with a DAX formula.

Thanks in advance.

[v-ljerr-msft · Accepted solution]

Hi @bglibkowski123a,

According to your description above, the formulas(DAX) should work in your scenario. 

Selected = CALCULATE ( AVERAGE ( 'Table1'[Job Satisfaction] ) )

Remainder =
CALCULATE (
    AVERAGE ( 'Table1'[Job Satisfaction] ),
    FILTER (
        ALL ( 'Table1' ),
        NOT (
            CONTAINS ( 'Table1', 'Table1'[Department], 'Table1'[Department] )
                && CONTAINS ( 'Table1', 'Table1'[job tenure], 'Table1'[job tenure] )
                && CONTAINS ( 'Table1', 'Table1'[education], 'Table1'[education] )
        )
    )
)

Regards