Andrew,

Check in the attached if it is what you are looking for.

First we will need the Score not only as a measure but also as a dimension, so I did the following calculation Score(ifNullThen0)

ZN( [Score for service] )

and convert it to a dimension. Basically, I create a dimension with Score for service and I replace the Nulls with 0.

I did a view with the average for both  the Type of service  and   the  Type of service (group)

Type of service:

I get the average with

MIN({FIXED [Type of service]: SUM([ScoreTotalValue])}) / MIN( [TotalNumberRecordsPerTypeOfService] )

where [ScoreTotalValue] is

{FIXED [Type of service],[Score (ifNullThen0)]: SUM( ZN( [Score for service] ) * [Number of Records] )}

and [TotalNumberRecordsPerTypeOfService] is

{FIXED [Type of service]: SUM( [Number of Records] )}

Type of service (group) :

I get the average with

MIN({FIXED [Type of service]: SUM([ScoreTotalValue (group)])}) / MIN([TotalNumberRecordsPerTypeOfService (group)])

where [ScoreTotalValue (group)] is

{FIXED [Type of service (group)],[Score (ifNullThen0)]: SUM( ZN( [Score for service] ) * [Number of Records] )}

and TotalNumberRecordsPerTypeOfService (group)] is

{FIXED [Type of service (group)]: SUM( [Number of Records] )}

You can see the result of those calculations in the ValidateData sheet. As you can see you get a value of the Avg for every columns.

This is why I used a MIN in the AVG calculation.(you could use MIN,MAX or AVG).

On the  view sheets, I used a dual-axis with a Bar mark, in order to be able to color the cells with the color logic.

Be aware that I changed the numbers in the color logic  so I could display both colors.

Michel

Andrew,

Try adding you filter to Context  (right-click the Date pill on the filter shelf and select   Add to Context).

This way the filtering will occur before  the FIXED is computed.