
1. Re: Calculation Trouble
Shawn Wallwork Jun 12, 2013 6:30 AM (in response to Benjamin Maulding)1 of 1 people found this helpfulHey guys, I'm working this one. Bit of education to do so it'll take a minute.
Shawn
PS: Benjamine, in the meantime read this KB article: Preparing Excel Files for Analysis  Tableau Software

2. Re: Calculation Trouble
Shawn Wallwork Jun 12, 2013 7:26 AM (in response to Benjamin Maulding)Benjamin, first a couple of housekeeping things, please
 Post a packaged workbook with your dummy data already set up. You're more likely to get a response if you don't make us do all the grunt work for you. Thx.
 Also if you are going to post an Excel file, make sure it's already 'prepared for Tableau', again to make our job easier. (See first tab of attached.) Collectively we've got quite a bit of knowledge around here, but we're pretty lazy folks (well at least I am), so please do most of our work for us. Thx.
The valuation formulas you included were very helpful, so it was good that you gave us those, otherwise I couldn't have answered this question.
OK, in Tableau the SUM() aggregate doesn't always mean SUM like you think of it in Excel. Depending on how your table is laid out the aggregates SUM() AVG() MIN() MAX() can all return the same value because there is only one value in the cell, so there's nothing to aggregate. This is one way to get around the aggregate/nonaggregate mismatch.
In the attached file I included this table calculation:
(SUM([Stores Base])/SUM([Stores Total]))/ (TOTAL(SUM([Stores Base]))/TOTAL(SUM([Stores Total])))
SUM([Store Base]) = C2 (in your Excel file)
SUM([Store Total]) = F2
TOTAL(SUM([Store Base]) = C15 (This is the same as summing a column to get the grand total)
TOTAL(SUM([Store Total]) = F15 (Same as above)
We don't need the * 100, because when you set the number format to percent Tableau automatically does the * 100 for you.
Lastly, since Joe Mako and Jonathan Drummey are both always preaching to avoid using relative addressing (Table Down), I went ahead and set the addressing to advanced with the two fields you need. I'll let them jump in and explain the whys. (They'll also jump in if I got any of this wrong. )
Hope this helps,
Shawn

ValuationSW.xlsx 22.8 KB

Benjamin.twbx.zip 33.5 KB