Retain average trend line despite slicer

[messyjesse_]

Hello,

In the screenshot below I have different cohort default rates charted over time. Each solid colored line represents a college. The black dashed line is the average of all colleges for that cohort.

I have three slicers on this report page: one to control which cohorts/colleges to display, and one to simply toggle the average line.

A given cohort-college's average is defined like this:

M_COL_COHORT = 
    CALCULATE(CDR_FactTable[AverageDefaultRate], 
        FILTER(CDR_FactTable, CDR_FactTable[CohortYear] = "2014"), 
        FILTER(CDR_FactTable, CDR_FactTable[College] = "COLLEGENAME")) 

The average lines (black dashed) are derived from this:

M_All_2014 = 
    CALCULATE(CDR_FactTable[AverageDefaultRate], 
        FILTER(CDR_FactTable, CDR_FactTable[CohortYear] = "2014")) 

The measure I use on the line graph makes a decision based on whether the "Toggle average" is set to "No" or "Yes" and reads as such:

M_All_2014_Slicer = 
    IF (CONTAINS (IncludeAverage, IncludeAverage[IncludeAverage], "Yes"), 
        'CDR_FactTable'[M_All_2014], 
        BLANK ()
       )

I've disabled all interactions between the cohort/college slicers and the average toggle.

If the average line is toggled to "Yes", I want it to remain static for the ENTIRE cohort, to give users of a given college the opportunity to compare their individual college's default rate to the average default rate for the entire cohort. The problem is that changing the college(s) selected in the slicer also adjusts the average, so that the average then becomes the average of the selected colleges rather than of the entire cohort. (For example, selecting a single college means that the college line and average line overlap at 100%, since the "average" of 1 college for a given date is equal to the 1 college's data on that same date).

I've tried fussing with ALL(), using referenced tables and calculated tables (with and without relationships), and trying virtually every combination of slicer interaction, but nothing seems to work. If it's a problem with the measure (and I'm almost certain it is), helping me step through it and make better sense of what's happening would be great too.

Any help would be appreciated. This has been driving me nuts. If more info's needed, just let me know.

[v-yuta-msft]

Hi messyjesse_,

Based on your description, you want to have an average value which meets the condition and don't be affected by the slicer, right?

I recommend you to use calculate column instead of measure to calculate average rate. In addtion, could you please provide your table structure and measure to achieve the avreage default rate?

Regards,

Jimmy Tao