Reallocation of Values in Tableau
If you have spent a few years working with data in Finance, you may have come across the challenge of the reallocation of values. This can be even more challenging if you are reallocating Region specific costs. In Tableau, this can be a challenge and before Level of Detail calculations became possible in version 9, would have been a complete nightmare.
When working with a retailer recently, I came across the challenge again so here is how I went about resolving the issue as can imagine a few scenarios where you might come across this. I teach both new and experienced Tableau users to build out solutions step-by-step and this is no different.
Scenario: I need to reallocate the sales from the Technology category in each Region across the other categories based on their share of the overall sales in the company.
Stage 1: Identify the totals so I can check calculations as I work through the solution
Just a simple build of a table and using the Analysis menu to add column totals and subtotals
Stage 2: Turn the values to percentages to work out the proportional share for the categories
This gives us the proportional shares of sales across the whole company but we need to be able to take the Technology sales out of the equation.
Stage 3: Create a calculation that will let you find all non-technological sales
Place this calculation in the table and you will see the effect.
Stage 4: Percent of Total Table Calculation computed down the table
This stage allows us to see the % share of sales across the non-technological sales.
With all the setup so far we are now in a position to create the calculations that will enable us to isolate the Technology sales and reattribute it across the other categories based on their proportion of sales.
Stage 5: Create a Technology Sales field
Here you can see the effect on the normal base table when just this measure is added to the Discrete (blue) data fields. Only Technology sales are returned.
Stage 6: Create a Technology sales value at the Region Level of Detail
This calculation returns the constant value for Technology sales in each region across the whole table. This will enable us to take a proportion of this value and add it to the existing sales.
Stage 7: Create a percentage of sales value for each Region by Category (for non-Technology Categories)
This has the same effect as the Technology Level of Detail calculation earlier but this time for non-Technology Sales. Here the calculation is used to create a % of the overall non-technology sales against each sales total for Category, Sub Category and Region combination.
Stage 8: Now allocate the cost by proportions created in Stage 7 but by adding only the sales for Technology in that Region
And here are the results:
All you need to do now is right click on Technology and select hide and you are done. You have reallocated the technology costs in that Region across the other Categories. Imagine this is management overheads instead of technology and you will see where it might apply to you.
Let me know of the efficiency steps you can make to this technique as I created it in limited time.
