4 Replies Latest reply on Mar 6, 2017 8:43 PM by lei.chen.0

    Grand Total Using AGG Columns

    Spencer Merrill

      I have a challenge that I've emulated here using the Superstore data. 

       

      I need to determine and show the Percent of Sales Goal that has been achieved.  To get to this point I have done the following:

       

      1)  Since there is no Goal in the data, I've assigned Goals by Category using the "Assign Goals by Category" measure.

      2)  This will assign the Goal value to every instance of Category, so I've divided the sum of the Goals by the count of the category using "Count by Category" and then "Goals by Category" (which is simply "sum([Assign Goals by Category])/[Count by Category]"

      3) Now if I add Category to rows and bring in Sales and Goals by Category I see a table of Sales and the Goals by Category.  Great!  Now I want to see a percentage of the goal that has been achieved.  So, I create "Percent of Goal by Category" (which is  "sum([Sales]) / (sum([Assign Goals by Category])/[Count by Category])"

      4) This is great as for each row I can now see what percent of the goal my sales have achieved, i.e., Furniture has Sales of $742,000 and a Goal of $800,000 which is 92.7% of Goal.  MY QUESTION IS:  For each Category the percent is correct, but in the Grand Total row it is summing the percentages.  How can I see the Total Sales divided by Total Goal which would be 85.1%.

       

      This is what I want: 

      This is what I currently have in Tableau:

       

      I understand that I am dealing with AGG Measures since I do not have the Goal information in the data I am assigning that and therefore I have aggregated that measure when I divided the sum of the goal by the count.  But is there a way to show the Sum of Sales divided by the Sum of the Goal which will give me 85.1%?

       

      Workbook is attached.