-
1. Re: Average of dimension types (Calculated value)
Michel Caissie Jun 6, 2018 2:08 PM (in response to Andrew Hutchinson)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
-
Example workbook (mc).twbx 134.2 KB
-
-
2. Re: Average of dimension types (Calculated value)
Andrew Hutchinson Jun 8, 2018 3:07 AM (in response to Michel Caissie)Hi Michel
Thanks for your response - I was able to do this however I forgot to mention I would need it to be reactive to filters - so a weekly filter for example - when I tried filtering this doesn't change the calculated value - sorry I didn't include the week in the sample uploaded.
Thanks
Andrew
-
3. Re: Average of dimension types (Calculated value)
Michel Caissie Jun 8, 2018 8:18 AM (in response to Andrew Hutchinson)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.