
1. Re: Compare Sum of allocated amounts to Sum of Unique Original Amounts
Bill Saltmarsh Jan 13, 2016 2:16 PM (in response to Dave Snider)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 Jan 13, 2016 2:20 PM (in response to Dave Snider)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 201501.
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!

FunkyNumbers.twbx 19.3 KB


3. Re: Compare Sum of allocated amounts to Sum of Unique Original Amounts
Dave Snider Jan 14, 2016 3:12 AM (in response to Bill Saltmarsh)Bill,
Thanks. I was trying the LOD Fixed calculation but must have had something wrong. I will try your calculations later today.