I need to be able to compare an individual’s measure (e.g. Number of assigned cases, Average age of case) against the larger group average (e.g., for a management team, program, all investigators).

Because my base table has multiple records for each investigator, and sometimes multiple records for a given case (which can have multiple allegations), I wanted to create a measure that gets the average assignment rate but only counts each case once, so I started my measure by creating a virtual table to get just the relevant records for assignment rate as follows:

    CALCULATETABLE(
        SUMMARIZECOLUMNS(
            'baseCombinedData'[YearMonthAssigned]
            , 'baseCombinedData'[Investigator]
            , 'baseCombinedData'[Manager]
            , "NumAssigned", DISTINCTCOUNT('baseCombinedData'[victimCase])         
) 
        , 'baseCombinedData'[CASorAssigned] = "Assigned"
        , 'baseCombinedData'[Assigned_prev6] = “Y”
 , USERELATIONSHIP('baseCombinedData'[DateAssigned], tblCalendar[Date])

    )

This creates a table where I get the number of assigned cases in the previous 6 months for each investigator each month. I’ve tried various approaches but I can’t figure out how to embed this in a measure to get a single average assigned-per-month measure that can then be dropped into a visual by month, or investigator. I assumed I would use an AVERAGEX() but I get an error that it needs base columns, not columns generated in a virtual table, or that the SUMMARIZE() function requires Argument 3 (in a different attempt, even when all arguments were included and outside of the measure the table is produced fine on its own). I tried multiple iterations found on the site and elsewhere like this to embed the table and produce the measure:

AvgAssigned = 
VAR tblAssigned = 
    CALCULATETABLE(
        SUMMARIZECOLUMNS(
            'baseCombinedData'[YearMonthAssigned]
            , 'baseCombinedData'[Investigator]
            , 'baseCombinedData'[Manager]
            , "NumAssigned", DISTINCTCOUNT('baseCombinedData'[victimCase])         
) 
        , 'baseCombinedData'[CASorAssigned] = "Assigned"
        , 'baseCombinedData'[Assigned_prev6] = “Y”
 , USERELATIONSHIP('baseCombinedData'[DateAssigned], tblCalendar[Date])

    )

RETURN
AVERAGEX(tblAssigned, [NumAssigned])

I feel like I’m missing something pretty simple but I’m at my rope’s end and I’m running out of time. It would be great if someone has a good approach to resolve this. In the attached graphic example, the AverageAssignRate would vary month-to-month, and would be responsive to slicers which would filter all investigators under a particular manager, or all investigators involved in a particular case type (regardless of manager).