
1. Re: Calculate Averages
Eric Hammond Apr 13, 2018 10:26 AM (in response to Daniel Higgins)Hi Daniel,
The table calculation that you have may complicate the solution, but here is a way to average a regular calculated field. Create a levelofdetail calculation at the Practice Code level; [Avg Patients] = {Fixed [Practice Code]: COUNTD([PatientID])}. Then on a new sheet drag [Avg Patients] to the text tile in the Marks shelf, and rightclick to change the aggregation from SUM to AVG.

2. Re: Calculate Averages
Deepak Rai Apr 13, 2018 10:36 AM (in response to Daniel Higgins)Hi Daniel,
You need to use this FIXED Calc
{AVG({Fixed [Practice Code]: COUNTD([PatientID])})}
and add your Filters to CONTEXT as Filteration with FIXED works only then.
Thanks
Deepak

3. Re: Calculate Averages
Daniel Higgins Apr 17, 2018 1:19 AM (in response to Eric Hammond)Hi Eric,
Thanks for the response. I've tried having a go yesterday, but can't seem to get this solution to work. Assume it's because the numbers I am trying to Avg is already based on another calculation, not sure?
See calculation below, that is creating the list of averages. Perhaps this isn't the best way to get to the result I want?
Cheers,



6. Re: Calculate Averages
Daniel Higgins Apr 17, 2018 1:49 AM (in response to sudheer.kumar.5)Hi Sudheer,
Sorry, I probably confused matters with that screenshot. I was just trying to show what figure I was getting back get back to in my original posts.
Basically, I just want the worksheet to simply say 2,902 with no other data or charts as I want to pull this figure into some narrative on a dashboard. "The average number is 2,902" for example. What I am trying to do is apply an average to an average, but I can't figure out how. The chart manages to apply an average quite easily, but I don't know how to replicate this as a single data item.
Cheers

7. Re: Calculate Averages
Eric Hammond Apr 17, 2018 3:20 PM (in response to Daniel Higgins)Hi Daniel,
[P List Size2] in your table calc appears to be an aggregated calculated field (the detail of which I can't see). I'll assume that it is SUM(P List Size), and you can adjust accordingly. The previous approach using a level of detail calculation doesn't work here because table calculations are not allowed inside LOD calculations. Try this:
 [FTEs per Practice Code] = {FIXED [Practice Code] : SUM([FTE])}
 [Total P List Size] = {FIXED : SUM([P List Size])} //adjust to fix your calculated field
 [Avg_Patient_GP] = AVG([Total P List Size] / [FTEs per Practice Code])
Drag [Avg_Patient_GP] to the text tile on the Marks shelf to see the average.