Short of learning and thus writing MDX or, using CustomSQL with OpenQuery to pre-flatten the source (discussed here), this can be very problematic.
However, unlike many of Tableau's competitors, there us a fairly simple way around this problem but, this can be process heavy, and you need to be very aware of the ordinal positions of your data as it appears in your view, and crucially, all amendments and enhancements to charts are definitely best done as a table before re-drawing as a chart.
Consider this image: assume for this example that we are looking at a cube output
Let's imagine for a moment that you want to calculate the percentage difference between Sales and Forecast for Binders. Keep in mind that more often than not, measures are also partitioned by a dimension (Sales, Profit, Forecast), so it is often the case that you will be unable to refer to the measure-name too.
This is easy, we can perhaps write a calculation similar to this:
( (If Min([Sub-Category]) = 'Binders' Then Sum(Sales) Else Null End) - (If Min([Sub-Category]) = 'Binders' Then Sum(Forecast) Else Null End) ) / (If Min([Sub-Category]) = 'Binders' Then Sum(Forecast) Else Null End)
Great, this would work,, in a relation world, but this is OLAP, we are unable to slice the dimension data here, with Tableau returning an unknown error for the split.
Therefore, really, this calculation would become:
( Sum(Sales) - Sum(Forecast) ) / Sum(Forecast)
Which isn't what is required as this would provide an output for all the fields.
Table Calculations to the rescue
Yes, table calculations are your friends here as these use ordinal positions to provide the output:
Being more complex, positional table calculations do generate second-level calculations needed to be processed by Tableau so you will need to consider this when building your view.
|Content by Label|