Hello Friends!
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.
See the difference?
Part 2: Visualizing the contribution of a city in its state Profit.
For this, we want to go back to the level of the state, take the sum of Profits of all of the sales for each state, come back to the level of granularity of the city and then find the proportion of contribution of each city in the state Profit.
This can be done by the following Exclude function as follows:
How this works: It says, use the same level of aggregation as in the visualization but, exclude city if it is present. Since in our Viz there are three dimensions present, i.e, Country, State and City, it will exclude the City and calculate the sum of Profits at State level.
When this Table calculation is placed on the shelf with a level of detail city, it will return the value of same sum of Profits of that State for each city (and we need to use ATTR to aggregate it).
Now that we have access to Profits at both City level and State level, we can use them to find the desired proportion of the contribution a City is making in the State Profit. It can be done by dividing the Profit of a city with the corresponding state Profit.
Caution: What if the total Profit of the State is negative? For a city with positive Profit, it will give negative contribution!
Also, for a City with Total negative Profit, the total contribution will become positive of divided by negative overall State Profit.
If not taken care, this might lead to some wrong and absurd results. All this is taken care of in following ways:
We are going to represent the contribution of the city by the size of the bubble over the city location in the map. If the contribution of a city is more (even if negative), we want the size of that bubble to be more, hence we have to ignore any signs while calculating this.
Add this to the size card of the marks card: abs(SUM([Profit])/ABS(ATTR([LOD Exclude State Profit])))
Since our default aggregation is at the City level, SUM([Profit]) results in the total Profit for that City. As explained above ATTR([LOD Exclude State Profit])) results in the total Profit of the State. Diving them gives the Proportion and their absolute value makes sure more the magnitude of City Profit is, more is its contribution and hence more is the size of the circle, since negative value in size makes no sense.
Add this to the color card: SUM([Profit])/ABS(ATTR([LOD Exclude State Profit]))
This ensures that all the Cities contributing positively will be given a color range towards positive side of the color palette and vice versa.
Result:
Final Step: Combining the two Viz
By making a dual axis graph of:
State Profit which is the average of the net Profit in its Cities.
Contribution of a City in the net State Profit.
This viz gives a good insight about the City Profits, State Profits and the contribution of a City in its State overall Profit.
Any improvements possible?
Let me know.
Thanks!