
1. Re: Is it possible to create and use a Table Calculation that computes across a field that is not displayed on the grid?
Jonathan Drummey Apr 18, 2012 1:50 PM (in response to sirajmandayippurath)Hello Siraj,
One way I think of table calculations is that all the dimensions*** on the Rows, Columns, and Marks card are used by Tableau to identify the dimensions that create a "canvas" from which the table calculations pluck numbers from based on their settings. ***blue (discrete) pills excepting other discrete table calculations, discrete aggregated measures that are by default ignored in table calculations, and including green (continuous) pills that are dimensions.
Tableau applies context filters and regular filters as it is pulling data from the database, then it performs table calculations. So any normal filters remove data from all calculations, and as you noted having dimensions on the filters shelf does not make them available to table calculations in the view. The method around this is that Tableau can also use the results of table calculations as a filter. In that case, data that is filtered out by the results of those table calculations is simply hidden from the view. One easy way to start hiding certain dimensions while leaving the rest of the calculations untouched is to use a calculated field of the form LOOKUP(ATTR([field]),0) and put that on the Filter shelf. It effectively replaces [field] on the Filter shelf with a table calc that returns the same data. In the attached workbook I included a version of this for both the Region and Country.
Another (sometimes more complicated) way of enabling a subset of data to be shown while totals remain unchanged is to duplicate your data source and set up a blend, with the subset of data as the primary and the relationships set up such that all data is returned from the secondary to be available in calculations.
Finally, I believe your average calculation is not returning a true persale average. When the partitioning is set up as you describe, the WINDOW_COUNT(SUM([Sales])) only returns a value of 3 (the set of Germany, UK, USA for each Product), whereas there are actually 6 records in that partition. TOTAL(SUM([Number of Records])) will give you the result of 6.
Jonathan

Calculated Member.twbx.zip 35.6 KB


2. Re: Is it possible to create and use a Table Calculation that computes across a field that is not displayed on the grid?
Jonathan Drummey Apr 18, 2012 1:52 PM (in response to Jonathan Drummey)One more thing, relating to the subject of your post. You can definitely have fields that are part of the calculation that are not displayed, that's a main purpose of the Level of Detail shelf on the Marks card (the other is to add fields to the view to be displayed in tooltips and reference lines).

3. Re: Is it possible to create and use a Table Calculation that computes across a field that is not displayed on the grid?
sirajmandayippurath Apr 18, 2012 3:34 PM (in response to Jonathan Drummey)Thanks Jonathan  that is all very helpful!
Cheers
Siraj