2021: Week 11 - Solution

Solution by Tom Prowse and you can download the full workflow here. 

This week we were taking a look at cocktail recipes with a challenge put together by Viven Ho. This covered a lot of fundamental data prep skills so hopefully you found it useful to bring your Tableau Prep skills together. 

Step 1 - Split Ingredients & Measurements

The first step is to input the Cocktails table, and then we want to split apart the 'Recipe (ml)' field so that each of the different ingredients have their own column. We can split these using a custom split on a ';' separator: 

As a result we have 4 columns that we can pivot into a single column using a Columns to Rows pivot. In the pivot setup we can use a Wildcard on 'Recipe' to bring through all of the fields containing our recipe ingredients: 

After the pivot our recipe ingredients are all have their own row and the table looks like this: 

Now the ingredients are in a single column, we can then extract the measurements from each ingredient. We are again going to use the split functionality but this time we are using an automatic split and as a result we have two new columns that we can rename to 'Ingredient' and 'Amount (ml)':

Step 2 - Ingredient Price

Next we want to bring in the Sourcing and Conversion Rate tables then join these on the Currency field: 

Once these tables are joined we can calculate the price in £ using: 

Price £ 
[Price]/[Conversion Rate £]

We can then calculate the price per ml using: 

Price £ per ml 
[Price £]/[ml per Bottle]

Once we remove any of the other fields and our table should look like this: 

Step 3 - Price per Ingredient

Now we are ready to combine both parts of our workflow and then calculate the Price per Ingredient. First we want to join both tables using the Ingredient field: 

We can now calculate the 'Price per Ingredient' using:

Price per Ingredient 
[Amount (ml)]*[Price £ per ml]

Then we want to remove any unwanted fields so our table looks like this: 

Step 4 - Calculate Profit Margin

The final step of the challenge is to calculate the profit margin for each cocktail. Therefore, we first need to find out how much each cocktail costs and the price that it is being sold at using an aggregate tool: 

After the aggregation, we need to round the cost to 2 decimal places using the following calculation: 

Cost 
Round([Cost],2)

And then finally we can calculate the margin for each cocktail: 

Margin 

[Price] - [Cost]

And that's it for this week, we have our desired output!

The full output can be downloaded here.

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @vivienho22, @Datajedininja, @JennyMartinDS14 & @TomProwse1

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!