In third week of my training, we were assigned with different tableau challenges. I was assigned with week 7 challenge of workout Wednesday. In the challenge, I was required to make a scattered plot for Profit Ratio vs sales by each state. An important requirement for the scattered plot was to add a reference box with marks of countries that fell in 25th and 75th percentile for each measure. Secondly, to add more context to the scattered plot a bar graph with same measures was also build. Lastly, a line graph to see how profit ratio and sales did over time comparing it with other states was also build.

Scattered Plot

For the axis of scattered plot, we already had sales, but we needed profit ratio as well. Using a calculated field, profit ratio was calculated by this formula (Profit ratio= Sum(profit)/Sum(sales)).

After this, profit ratio & sales were dragged to rows and columns respectively. Then States were dragged to detail, so that we could see all states sales and profit ratio in the scattered plot. Our reference state was New jersey, so a parameter (string) was created. For this parameter, a calculated field was also made to make it dynamic meaning users could change the reference point to any state with the help of this calculated field. The new calculated field was then dragged to color and size so that the color & size of our chosen state is different & bigger than the others.

Figure 1:Profit Ratio Calculation

Figure 2: State Parameter

Figure 3: State calculation

Figure 4: Scattered plot initial stage

To create the reference box, add a reference line to sales axis like as follows.

Figure 5: Reference box

Now, the scattered plot is complete.

Figure 6: Completed scatter plot

Bar Graph

To add more context to scattered plot, a bar graph was made. For this graph, first we need to drag states to rows. This will give us all the states, but we only require our reference state on top which is new jersey in our case. States that lie in the reference box in the middle and all the outlier’s median at the bottom. To calculate this, we will need to make some calculated fields. For our Reference box states, we need to create a set that contains all the states that lied in the reference box in our scattered plot.

Following formula is used to create the set

sum([Sales])>=sum({ PERCENTILE({ FIXED [State]: SUM([Sales]) },0.25) })

and

SUM([Sales])<=sum({ PERCENTILE({ FIXED [State]: SUM([Sales]) },0.75) })

and

[Profit Ratio]>=sum({ PERCENTILE({ FIXED [State]: [Profit Ratio] },0.25) })

and

[Profit Ratio]<=sum({ PERCENTILE({ FIXED [State]: [Profit Ratio] },0.75) })

We will then drag our new set to our rows to see which lie inside the reference box and which lie outside.  Then we will need to assign the outliers into one category i.e. other states median. Now we need sum of sales and profit ratio of all reference states and our reference point state and median of all the outliers on canvas. To do that we need to make a calculated field that will be our axis. For sales we will use a formula like this

 

Figure 7: Axis calculation

After this we need to make a calculated field for its sorting and leveling which looks like this

Figure 8: Level and sorting calculation

This is our final bar graph

Figure 9: Completed bar graph

 

Line Graph

For line graph, drag order date to columns and change it to continuous month. For axis we need to make a calculated field for sales and profit which look like as follows

Figure 10: Axis calculation for line graph

 

After that jus drag the sorting calculation to colors and level to detail, chosen state calc to size. This will give us our required line graph of sales and profit ratio per month.

Figure 11: Completed line graph for sales and profit ratio per month

 

Maliha Athar
Author: Maliha Athar