Could you provide any details or the twbx file?
In general case, I would use sum(sales)/sum(budget) for the purpose.
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
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.
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?
Hi amar savale
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?
I think it was somekind of ghost in my Tableau. Now it works without any changes made. Thanks for all your help.