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

    Growth from two aggregated values

    daniel.andell

      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
          ArseneXie

          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
            daniel.andell

            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
              ArseneXie

              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
                daniel.andell

                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
                  amar savale

                  Hello,

                  Please Check the below attachment if it is Correct or Helpful.

                   

                   

                   

                   

                   

                   

                   

                   

                   

                  Thanks,

                  Amar D. Savale

                  • 6. Re: Growth from two aggregated values
                    daniel.andell

                    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?

                     

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

                    • 7. Re: Growth from two aggregated values
                      daniel.andell

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