Showing average percentage for whole department and average percentage for individual on line chart

Question

I want to display the average percentage for all 'excellent' case outcomes over the 12 month period for all colleagues on a line chart. I want this line to be unaffected by the colleague name filter on the visual.

This is some sample data from the 'case' table:

This is the measure I have so far:

Excellent Fixed = 
   CALCULATE(
     COUNTROWS('Case'), 
      FILTER('Case', 'Case'[Case Outcome]="Excellent"),
       ALLEXCEPT('Case', 'Case'[Date].[Month])) / 
   CALCULATE(
     COUNTROWS('Case'),
       ALLEXCEPT('Case', 'Case'[Date].[Month]))

On the line chart visual, the 'Axis' is set to Date - Month and the 'Values' has the 'Excellent Fixed' measure as shown above. This correctly displays the average Excellent cases over the 12 month period but the visual is affected by the colleague name filter (i.e when selecting John Smith from the colleague name visual filter, the visual changes to just show the average for that colleague. Also, I have noticed that the average then being displayed for that colleague is incorrect).

The expected result should be that the measure is unaffected by the 'colleague name' visual filter.

sergiom sergiom · Accepted Answer · 2021-01-15T10:23:16

I think that the problem is the FILTER over the table 'Case'

Since the 'Case' in the current filter context is sliced per Collegue Name and therefore it re-inserts the filter removed by the ALLEXCEPT

A possible solution is to change the FILTER with a filter argument on the single column Case Outcome

Excellent Fixed =
DIVIDE(
    CALCULATE(
        COUNTROWS( 'Case' ),
        'Case'[Case Outcome] = "Excellent",
        ALLEXCEPT( 'Case', 'Case'[Date].[Month] )
    ),
    CALCULATE( COUNTROWS( 'Case' ), ALLEXCEPT( 'Case', 'Case'[Date].[Month] ) )
)

Showing average percentage for whole department and average percentage for individual on line chart

1 Answers