2021: Week 31 - Solution
Solution by Tom Prowse and you can download the workflow here.
This week we are looking at a technique that is ubiquitous within Excel - the Pivot Table. We again re-visit the Allchains bike store and look restructuring a sales table across different items and stores.
Step 1 - Remove Returns
After we have input our data set, the first step is to remove any rows that contain returns. These are documented in the 'Status' field with a 'Return to Manufacturer', so we can right-click on this in the Profile Pane and then Exclude.
Whilst we are here, we can tidy the table up a little bit. We aren't going to be using the Status or Date fields so we can remove these fields as well. Our table should now look like this:
Step 2 - Total Items Sold per Store
Next we want to find the total items sold for each store. This can be achieved by using a Fixed LOD calculation, where we group by store and sum the number of items:
As a result we have got the total number of items each store has sold on the same row and each of sales. This is important for when we come to pivot the data in the next step.
Step 3 - Pivot Table
The final step this week is to actual pivot the table so that we transform our rows into columns. This is done by using a Rows to Columns pivot where we have the Item as our Pivoted Fields (the columns) and Sum Number of Items as the Rows.
As a result of the pivot we have transformed the table to look like our output:
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @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!
