A question I was recently asked by an in training Tableau User was ‘Why would I apply a table calculation by cell?’. To answer this I have produced a simple example of a by cell Table Calculation. Before I get to it I would like to run through some Table Calculation basics.
One of the key principles in Table Calculations is the direction of the calculation. Consider the following table which shows the ‘Running Sum’ table calculation running ‘Table Across’:
As you can see this running sum is running horizontally left to right and restarting on each row. If you ran the calculation again, this time ‘Table Down’ the calculation would work vertically down the table restarting for each column.
‘Across then down’ runs first horizontally and then carries the result to the next row running against the entire table:
Down then across runs vertically but carries the result to the next column of data, working across the whole table.
The by cell calculation runs the calculation for a single cell of data (i.e. the lowest level of granularity in the view) Consider the following example:
This chart shows Sales broken down by product type and market. I have applied ‘Type’ to the colour of the chart and set up my table calculation as percentage of total.
What I would like to see is the % split between decaf and regular sales for each Product Type and Market. By default this calculation has been applied as ‘Table Across’ so each colour split is the % of the total for the entire row of data.
If I want to see the split value for each cell I need to set the calculation to run ‘By Cell’. Click ‘By Cell’ to see this calculation in action.
Now you can see that each cell of data has a total of 100% showing the split between Decaf and Regular drink sales.
Any questions at all, please don’t hesitate to get in touch. We love a challenge!