7 Replies Latest reply on Oct 1, 2018 5:16 AM by daniel.andell

# Growth from two aggregated values

I have sales *** AGG and budget as AGG when I use calculation Sales - Budget it works fine. But when I do Sales / Budget i get 0 as result.

How can I do this calculation with AGG values?

Thanks

• ###### 1. Re: Growth from two aggregated values

Hi Daniel,

Could you provide any details or the twbx file?

In general case, I would use sum(sales)/sum(budget) for the purpose.

Thanks

Arsene

• ###### 2. Re: Growth from two aggregated values

I have YTD sales and budget in my file

To filter budget to this Year it is a calculation as below.

IF [What year] = "This Year" Then [Budget] END

Now I would like to do the following calculation Sales / Budget

It seems that my budget calculation makes the result show 0

• ###### 3. Re: Growth from two aggregated values

Hi Daniel,

For the situation that calculation result be 0, you can check these:

(1). make sure that the sum( this year budget) is not null and > 0

(2). make sure that the sum(sales) > 0

Let's use the tableau superstore data as an example for discussion.

There is no budget in the data source, so I create a calculate field: budget (and assume that is equal to the sales).

And I create a "What Year " field from order date, and make it as a discrete dimension.

Now, there are "What Year", "Sales", "Budget" fields in the data source.

1. If the requirement is to calculate "Yearly Sales" / "Yearly Budget"  for each year.

Just create a calculate field CF_B/S as below, and drag "What Year" into the view.

2. If the requirement is to calculate "yearly sales" / "specific year : yearly budget" (e.g. all base on 2016 budget)

It need to use LOD.

(1). Create CF Budget_2016 :

(2). Create CF LOD_Budget_2016:

(3) Create CF CF_B/S_BaseOn2016:

(4) The View

3. It should do some exception handle if the budget might be 0 or null. e.g.

Arsene

• ###### 4. Re: Growth from two aggregated values

Now I have a better example.

When I write the calculation manually

SUM([Current_YTD_Sales])/SUM([DRS_Budget_Function CY])-1 it says AGG in front of it and works just fine

But when I have the same calculation in a measure and drag it into the chart it doesnt show any value at all?

SUM([Current_YTD_Sales])/SUM([DRS_Budget_Function CY])-1

• ###### 5. Re: Growth from two aggregated values

Hello,

Thanks,

Amar D. Savale

• ###### 6. Re: Growth from two aggregated values

I am not sure how to use your example.

Below is the data I have. First I have sales and budget in local, then I have sales and budget in USD.

I would like to calculate % vs budget for both of these.

I use the calculation below. But I only get 0 as a result. If I use the exact same calculation but add it manually it seeks to work. Why does it work with a manual formula but not as a calculated field?

SUM([Current_YTD_Sales])/SUM([DRS_Budget_Function CY])-1

• ###### 7. Re: Growth from two aggregated values

I think it was somekind of ghost in my Tableau. Now it works without any changes made. Thanks for all your help.