It is very often when we have to work with data having some levels in it, whether it be geographical levels (country > state > city) or qualitative levels (Type A > Type A1 > Type 1) or levels on time (Year > Month > Week) or any combination.
In this post, I am going to discuss about visualizing data in Tableau having multiple level of details.
The Aim of this post is to create a single visualization which is able to visualize following:
- For each state, it takes the average of the Profits of all the cities in it.
- For each city, it shows the total contribution of the city in the state Profit.
If you've noticed, just averaging the Profits for each state wont work because it will average the Profit over every single item (since that is the most granular level), but we want to average over all cities.
Lets get started!
The data I am going to visualize comes from retail industry and contains details of order placed across various states of US.
Data can be downloaded from here.
There are two sheets in the data file. In the first sheet named ListOfOrders, there is a row for every single order. In the second sheet, i.e. OrderBreakdown, all the orders are broken down to the granularity of different products in the order.
In order to connect the Profits and Discounts to respective cities and states, we will have to perform a join. Its pretty easy in Tableau to join multiple sheets.
Part 1: Visualizing the average Profits over all cities for a state.
This can be done by a simple Table calculation using Include function available in the aggregate subsection of Table calculations. We are creating a relative path down a level using this.
By doing this, we go down to the level of each city, sum the Profits for each city and then take the average of this pill when placing it in the row/column shelf. This will take the average over the aggregated Profits for each city.