A few weeks ago I had this problem: I needed to create a dynamic table that showed the last 12 full months of sales for a particular channel/category of the business but I also needed to show the month-over-month and year-over-year totals. I didn’t know how to do it right away so I asked the Workout Wednesday team and they responded with many solutions. So I changed this week to match that exact problem. I’m also including some information on financial reporting since I wasn’t familiar until recently. We’ll be calculating basis points. The basic formula is (%a -%b)*10000. Where a and b are just two percentages. This means 1 bps = 0.01% and 100 bps = 1.00%.
Click image to view on Tableau Public
- Format: 1100px by 400px
- Create a table that shows the following metrics for the last 12 full months by category:
- Percent of total sales that are from the Consumer segment.
- Month-over-month change in % Consumer sales using basis points (bps). (Current Month % – Prior Month %) * 10000
- Year-over-year change in % Consumer sales using basis points (bps). (Current Month % – Same month prior year %) * 10000
- Add a filter so that you can toggle dates. Note the months will only show when data is for a complete month of data.
- Match formatting border and fill formatting.
- ├ and └ will be useful.
DatasetThis week uses the superstore dataset for Tableau 2019.1. You can get it here at data.world
After you finish your workout, share on Twitter using the hashtag #WorkoutWednesday2019 and tag @AnnUJackson, @LukeStanke, @lorna_eden, and @curtisharris_!
Track your progress
Also, don’t forget to track your progress using this Workout Wednesday form.
When you publish your solution on Tableau Public make sure to take the time and include a link to the original inspiration.
2 thoughts on “Week 17: Can you build an automated monthly report”
it seems to me that the small image you linked here and the workbook in tableau public are different -> the public version shows wrong percentages… i’m dying tring to verify calculations?
Could you please fix it?
Here’s my shot – i tried an elegant way using duplicated DS – but my total’s are’nt exacly your – but it has internal consistency.
Nice Workout – thanks