Forum Discussion

bglibkowski123a's avatar
bglibkowski123a
Frequent Visitor
8 years ago
Solved

Benchmarking using Filters (selected and comparison to remainder)

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.

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi bglibkowski123a,

     

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

     

    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

1 Reply

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi bglibkowski123a,

     

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

     

    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