2 Replies Latest reply on Sep 6, 2012 8:41 PM by Ratna Sarkar

    How to force overall average rather than sum of group averages?

    Ratna Sarkar
    Ratna Sarkar Sep 4, 2012 3:26 PM

      I'm a newbie to Tableau. I really want to force myself to use Tableau Calculated Variables, rather than run off each time to Excel, quickly create the new variable I need and come back into Tableau (and refresh data)...hence my very basic question.

       

      In my dataset, I have salary outlays and faculty counts  (2 measures) by rank and gender (two dimensions). I want to calculate something very simple for each rank: Sum(total salary outlays for males, total salary outlays for females)/Sum(count of males, count of females).

       

      When I create a new variable Av Salary = [Salary Outlay]/[Fac Counts]  and I get almost double the numbers  I expect, I see that Tableau is calculating is actually Male Average Salary + Female Average Salary.

       

      I want Tableau to sum the salary outlays and the fac counts first, then calculate the average. When I change my Av Salary computation to

      SUM([Salary Outlay])/SUM([Fac Counts]) then everything in the main window gets greyed out, all my filters get blanked out -- like this computation does no make sense.

       

      Would appreciate some help with this (cannot sent the Tableau file or even an image -- confidentiality issues.)

       

      Thanks

       

      Ratna

        • 1. Re: How to force overall average rather than sum of group averages?
          Tram Trinh
          Tram Trinh Sep 4, 2012 5:10 PM (in response to Ratna Sarkar)

          Hi Ratna,

           

          After the pane got greyed out, did you try dragging the [Av Salary] with the new calculation over to replace the previous one already on the shelf?

          It's possible that you still had the previous pill with an AVG aggregation on it, so when you modified the calculation to add the SUM aggregations within it, it was no longer valid in the pane. Usually just replacing the pill with the updated calculated field fixes this. (When you replace it, the pill will then have the AGG() function around the measure, rather than AVG()).

           

          Regards,

          Tram.

          1 of 1 people found this helpful
          • 2. Re: How to force overall average rather than sum of group averages?
            Ratna Sarkar
            Ratna Sarkar Sep 6, 2012 8:41 PM (in response to Tram Trinh)

            Hi Tram

            Thanks for your reply. Replacing the pill was not _exactly_ the answer, but, it got me thinking that perhaps I was asking the table to display something undisplayable -- and indeed that was the case. I had gender as one of my variables, and once I forced my calculated average to be an overall sum, it could not be displayed in a table which had different rows for males and females. Removing gender from the table fixed the problem!

            Thanks and I hope this helps someone else.

            Ratna