1 Reply Latest reply on Oct 4, 2015 4:59 PM by Bill Lyons

    aggregate value in 2 steps

    rodolf guich

      If we have the following sample structure

          - measures: Nb_of_Sales,Nb_of_Employees

          - dimensions: month, year, shop, city, state

          - a specific calculated field:

                  profitVar = Sum(Nb_of_Sales)/sum(Nb_of_Employees)

      I can plot profit per month-year for each shop

      Is there a way to simply calculate profit per city which should be

          {sum(Nb_of_Sales)for all shop in the city} / {sum(Nb_of_Employees)for all shop in the city}

          which is different of     sum{(Nb_of_Sales/Nb_of_Employees)for all shop in the city}

         

      I have 2 questions:

          - is there a way to have the 2 parts {sum(Nb_of_Sales)for all shop in the city} and {sum(Nb_of_Employees)for all shop in the city} calculated first

              then have the ratio evaluated ? I ve tried with calculated field, parameter,.. but I dont find any way to have calcul made in 2 steps:

                  - first aggregate values to have {sum(Nb_of_Sales)for all shop in the city} and {sum(Nb_of_Employees)for all shop in the city}

                  - 2nd: have the ratio calculated

          - is there a quick way to calculate {sum(Nb_of_Sales)for all shop in the city} / {sum(Nb_of_Employees)for all shop in the city} automatically whatever the required aggregation (per city, state, per year..)?

        • 1. Re: aggregate value in 2 steps
          Bill Lyons

          The simple answer is, "yes." You can have one calculated field be used inside another, so you can have 2 steps,5 steps, or whatever you want.

           

          However, based on your description, I don't think you really need to use multiple steps. For example, if you put city on the rows shelf, the ratio to place in detail is simply:

          SUM(Nb_of_Sales)/SUM(Nb_of_Employees)

           

          If you are wanting to fix this formula at the City level regardless of other dimensions and filters, you can use a Level of Detail (LoD) calculation:

          { FIXED City : SUM(Nb_of_Sales)/SUM(Nb_of_Employees) }

           

          If I have misunderstood your question, it would greatly help if you can attach a packaged workbook illustrating what you want, what you have accomplished so far, and what you want it to look like. See Posting a Perfect Question, Getting the Most From the Tableau Forums and Anonymize your Tableau Package Data for Sharing for more information.