Welcome back to my blog. Last week I wrote about how I went about completing two simple Alteryx challenges and while I do plan on doing a similar blog but for more difficult challenge, that’s not what this week’s blog will be about. I am switching over to Tableau and completing a Workout Wednesday Challenge. I will both show you how I did it but will format this blog as more of a tutorial so you can follow along. Here is the quote from the Workout Wednesday website describing what they are all about.

Workout Wednesday is a weekly challenge to re-create a data-driven visualization. The challenges are designed to kick-start personal development in Tableau and Power BI.

It’s that simple! Each week they give you a visualisation and a dataset and you need to recreate the visualisation just by looking at it. I must admit that they are very challenging but a fantastic way of testing your skills. I solved my first challenge last Friday as that was the challenge set by our coach Shane, and I will go through how I solved it. I recommend these challenges to anyone learning Tableau as you will learn a lot of skills by recreating complex visualisations. Before I start, I would like to note that there are many ways to skin a cat, you might be able to come up with a simpler solution then mine, if so, I would love to see it. The one caveat that was set for us by our coach which was that we had to use at least one table calculation or LOD expression in our solution. Now, let’s get into the problem.

I challenge I had was from week 48 of 2019 titled, “Can you build a Bar Chart that automatically combines small contributions?”. You can find this challenge online, download the dataset and give it a go yourself if you like. Below is the visualisation that we are trying to reach as well as the requirements of the challenge.

**Requirements**

• Dashboard 1100 x 900 – 1 sheet

• Create a bar chart that dynamically combines States based on the percentage of total contributing Sales

• For states beneath the threshold, they should be combined into one bucket called “All Other States”

• This should be at the bottom of the bar chart

• The rest of the states that contribute more than the threshold should be displayed in descending order

• Tooltips for state names should be blue for the blue bars and gray for the “All Other States” bar

• Labels for bars should be sitting on top of the bar

• Construct a subtitle that shows the number of states in blue and the number in gray

• Match any remaining formatting

I broke this challenge down into two parts, creating the graph and then formatting. I honestly had more troubles with the formatting.

**Creating the graph**

This section will be broken down into two sections, the percentage of sales and then the states. The first thing I did was right-click and drag the sales cell onto the columns shelf and select ‘sum(sales)’. Next, right-click on the sales cell that is on the shelf and click ‘add table calculation’ and under table calculation you need to select ‘percent of total’ and select ‘table down’ under compute using and now you can exit out of the menu. Next, we need to create a states cell that we can then add to the rows, however this is quite complicated, and I struggled completing this, but I did reach a solution. We need the cell to show us the states above the threshold and then shows one bar to represent all other states. Therefore, we need the threshold, which will be a parameter, and a state set that will give us the states above the threshold. First, let’s create the parameter. Title it ‘Contribution Percentage’, give it a range of float values and ensure that the display format is a percentage. Also create a percentage of sales calculated field so that we can compare the two. Below is my parameter and calculated field if you want to follow along.

To create the set, right click on the state cell and under ‘create’, select create ‘set’. We want this set to include all states above the threshold parameter. Therefore, the set will be created by formula which will be the percent of sales calculated field is larger than or equal to the contribution parameter. Below is the set that I created.

We have all the parts we need to create the graph. Create a calculated field that is a state is in the state set then return the state if not then mark that state ‘All other states’ Below is that calculated field.

Now drag this calculated field onto the rows shelf and sort into descending order. In the solution, the bar for all other states is always at the bottom of the chart. To do this I created a calculated field where I subtracted 20000 so that it would always sit the lowest of all the bars. If this does not automatically work, then right click on the state cell on the rows shelf and go to sort. Ensure that field name is set to the calculated field we just calculated. Next, drag the state set onto colour and match the colours you see in the solution. Finally, go to the analytics tab and drag on a reference line and set the value to the parameter that we set. Below is the configuration for the reference line.

The final thing we need to do is create the dynamic title. There are four parts that are dynamic so we will be creating four calculated fields. One for the number of states above the threshold and one to write the word ‘states’ when the number is more then one and ‘state’ for when the number of states is one. Below will be all four of the calculated fields. Once these are created, they need to be on the graph for us to be able to use them in the title. I decided to drag them all onto detail, so they do not affect the graph, but I can still use them in the title.

Now here is also my title card. If you are following along, match the formatting. You can enter in a calculated field by clicking on insert and then the calculation.

Now let’s move onto part 2 of the challenge. This was hard to figure out that it required some help from our coach to help us finish it.

**Formatting**

The first thing to do is to right click on the states on the y-axis and unselect ‘show header’. Double click on the x-axis and the below menu should pop up where you can then change the title of the axis to ‘PERCENT OF SALES’. The next part of formatting is to get a label on top of every bar with the state the percent of sales for that state. I did this by creating another bar chart where every bar has a value of zero to which we add the label to. The sales and the zero chart are then put on a dual axis, by right clicking on one of the cells, and the axis were synchronised which can be done by right clicking on the axis. Right click on the axis and unselect show header. Next, make the label for the average of zero bar chart show the state and percent of sales. In the same label card, select ‘Match Mark Card’ under the font section. You can play with the size of both graphs so that the label sits right on top of the bar. This should get you the desired result. You might need to play around with colours to get it exact. This should give you the wanted visualisation.

I understand that this has been a fairly detailed blog. I will refrain from this in future blogs, but I wanted to have this as a tutorial that beginners can follow along. Future blogs I would like to show cool things I learnt but I will assume some knowledge in Tableau. Next week will most likely be an Alteryx blog. Next week, I and the other data schoolers are learning spatial analytics in both Alteryx and Tableau so I will also be doing a blog on that as well as it sounds very interesting.

Thank you for reading my blog.