7 Replies Latest reply on Apr 17, 2018 3:20 PM by Eric Hammond

    Calculate Averages

    Daniel Higgins



      I would like to show the average for the row of numbers I have attached, and just display the average on it's own. The average for the below is around 2,900 so I would want Sheet 7 to simply say 2,900 with nothing else on the sheet.


      As I filter between different regions there will be more or fewer practice codes. I have filtered on one specific region in this example.





        • 1. Re: Calculate Averages
          Eric Hammond

          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 level-of-detail 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 right-click to change the aggregation from SUM to AVG.

          • 2. Re: Calculate Averages
            Deepak Rai

            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.



            • 3. Re: Calculate Averages
              Daniel Higgins

              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?





              • 4. Re: Calculate Averages
                Daniel Higgins

                To add, I can get the average to show on a graph by adding a reference line, but can't figure out how to display 2,902 as a single figure on the worksheet.



                • 5. Re: Calculate Averages

                  Hello Daniel,


                  This may help. Need to select the Label as Value.





                  • 6. Re: Calculate Averages
                    Daniel Higgins

                    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.



                    • 7. Re: Calculate Averages
                      Eric Hammond

                      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.