5 Replies Latest reply on Jan 15, 2019 1:16 PM by Deepak Rai

    Average of Average

    D'Jreya Boyd

      I'm trying to create a work around that is similar to the for statement in excel. Tableau is currently calculating the average using the sum of all the values divided by the count of the values. I would like it to take averages of averages. I'm currently calculating weekly averages per pane, and would like to add 2 layers (1 An average of the weekly averages per pane and 2 have a grand average that is the average of each of these pane averages). I would basically be saying for each week calculate the averages and for each pane calculate the average of the weekly averages, because the sum of total values/ count of values doesn't calculate the same value. Any help is appreciated.

        • 1. Re: Average of Average
          Claire Smith

          Hi D'Jreya,

           

          You should be able to accomplish this using the totals and subtotals function.  In the screen shot below, you'll see I drug Sales out in the view, then right clicked on it and under Measure, change to Average.

           

          Next, go to Analysis > Totals, and turn on the column grand totals and add all subtotalsImportant Step: After turning on the totals, got to Total all Using and select Average. Now you're seeing the average of the average per pane, and a grand total (see screen shots below).

           

          If you're still having issues, please attached your .twbx file so we can better see the problem.  If this helps, please mark as the correct answer to assist others in finding it quicker.  Thanks!

           

          • 2. Re: Average of Average
            Deepak Rai

            Hi Boyd,

            You can use an LOD to Do that. Pl see attached. I first calculated Weekly Averages per category and then Calculated Overall Avg Per Category and Also Overall Average.

             

            Thanks

            Deepak

            1 of 1 people found this helpful
            • 3. Re: Average of Average
              D'Jreya Boyd

              I'd like to start by saying thank you for the reply!!! This creates an average using the overall sum/ overall count instead of creating an average based on each week's average value.

              • 4. Re: Average of Average
                D'Jreya Boyd

                Do you know if it's possible to make the value update with filters? As I change the date filters is it possible to update the value or should I write the changes within the formula?

                • 5. Re: Average of Average
                  Deepak Rai

                  Just add Date to CONTEXT . FIXED LODs work with CONTEXT filters.