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

# aggregate value in 2 steps

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

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.