3 Replies Latest reply on Jan 14, 2016 3:12 AM by Dave Snider

    Compare Sum of allocated amounts to Sum of Unique Original Amounts

    Dave Snider

      I am currently working with a data set that has amounts allocated to various buckets and I need to calculate the variance of the sum of the allocated amounts to the original amount. I have not been able to figure out the formula to do this. The Detail tab of the attached Excel file contains an example of what I am working with and the Comparison tab shows what I want to create in Tableau. I have tried the "Fixed" LOD calculation without success. I am currently using version 9.0.6 of Tableau.

        • 1. Re: Compare Sum of allocated amounts to Sum of Unique Original Amounts
          Bill Saltmarsh

          Hi Dave,

          You can get to what you need by creating two separate FIXED calcs.

               1. For your Program Amount, use this:

                    { FIXED [Fiscal Period]: SUM([Program Amount])}

               2. For your Total Amount, use this:

                    { FIXED [Fiscal Period]: MAX([Total Amount])} + { FIXED [Fiscal Period]: MIN([Total Amount])}

           

          You can then create another calculated field that will find the variance, or you can just do it all in one calculated field. Something like this:

          { FIXED [Fiscal Period]: SUM([Program Amount])} -

          ({ FIXED [Fiscal Period]: MAX([Total Amount])} +

          { FIXED [Fiscal Period]: MIN([Total Amount])})

           

          Hope that helps.

           

          Bill

          • 2. Re: Compare Sum of allocated amounts to Sum of Unique Original Amounts
            Carl Slifer

            Howdy Dave,

             

            The issue is going to be your data is pretty confusing and it doesn't readily have a logical structure. Let's focus our inquiry just on Fiscal Period 2015-01.

             

            Let' notice that you have 8 rows of data. 6 of those have the value 100,000.01 for the Total Amount and 2 of them the value of 10,000.00. WHY! Maybe there's some great reason why this exists. But I cannot discern it. I would suggest making the structure 'better' so that you don't need to apply some messy workaround (below).

             

            In tableau how are you wanting this to be read? From having to guess from the Comparison tab you want to count each unique value once. Even though there are duplicate values. I have a workaround but its messy.

             

            To get the 'proper' total amount as it matches your comparison tab:

            {FIXED [Fiscal Period], [Total Amount] : AVG([Total Amount])}

            What this does is it looks through every combination of Fiscal Period and Total Amount. Even though total amount is a measure. IT then returns the average. Because it returns the average an identical array it will only have the 1 value.

             

            So when we sum that we get the' proper' values. Now to get the variance (non statistical variance, I initially tried this and of course it was way off.)

             

            SUM({FIXED [Fiscal Period], [Total Amount] : AVG([Total Amount])})

            -

            SUM(Program Amount)

             

             

            Cheers!

            Carl Slifer

            InterWorks

            • 3. Re: Compare Sum of allocated amounts to Sum of Unique Original Amounts
              Dave Snider

              Bill,

               

              Thanks. I was trying the LOD Fixed calculation but must have had something wrong. I will try your calculations later today.