ASSIGNMENT DETAILS:
The Bibitor, LLC CFO wants some additional Sales analysis using Tableau. She is interested in differences between wine and spirits across the entire organization. She wants to know the differences in Sales Dollars and Quantities between the two categories and popular bottle sizes for wine and spirits. She is also interested in certain information at the Store level of detail.
Required:
Start by connecting to the Data Source attached to this Assignment (Tableau considers a CSV file as a text file). Please note that, once unzipped, this is a fairly large file. Once loaded in Tableau, processing time should be minimal I have limited the Assignment to one months worth of data in order to leverage the smallest usable file size.
Assist the CFO in creating Worksheets and Dashboards to answer these questions:
- What is the total Sales Dollars and percentage breakdown of wine and spirits?
- Lets start by creating a Pie Chart by Sales Dollars. Use the Classification attribute (1=Spirits and 2=Wine) and the Sales Dollars to separate the sales based on wine and spirits. Convert the data into a Pie Chart (Show Me Tab -> Pie Chart)
- It will be hard for the CFO to understand what the 1 and 2 represent for the Classification attribute. To make it easier, lets create an Alias for each value. Click on the drop-down arrow for the Classification pill in the Dimensions section. Click on Aliases In the pop-up window, next to the Member of 1, click in the Value (Alias) column and type Spirits (no quote marks). Next to the Member of 2, type Wine. Click OK
- The Pie Chart looks nice, but its not clear what the dollar value is for each slice lets turn on the Label Mark. The easiest way to do that is to click on Label in the Marks Card, then put a check mark in the box for Show mark labels
- Those labels may be unnecessarily difficult to read lets format them so the numbers show a) as currency and b) rounded off to the nearest thousand. Right-click on SUM(sales) {either one} in the Marks card. On the left, you will see a Format window appear. Toward the top of that window, you have an option to click on Axis (default) or Pane click on Pane. In the Default section, click the icon next to the Numbers field in order to get the Numbers formatting pop-up. Currency (custom) will allow you to show a $ sign, remove decimals, and change the Display Units to Thousands
- Your Pie Chart is probably a bit small and hard to see; the Labels also might overlap the pie slices, making them difficult to read use Ctrl + Shift + B (PC) / CMD + Shift + B (Mac) several times to make the pie chart bigger (notice the labels also move off the pie slices and are easier to read too). An alternate is to click the drop-down arrow next to the word Standard in the ribbon choose the Entire View option instead
- Update the Tab name with something meaningful (this will update your visualization Header as well)
- Hide the Show Me options (if they are visible, just click on the words Show Me), so you can see your legend for the pie chart
- TAKE A SCREENSHOT and label is BIB2-A; paste it into a Word doc that you will use to submit this assignment
- Before we leave this worksheet, lets use the Analysis Tab (menu option) to convert the sales data into percentages. Click on Analysis, hover over Percentage of and select Column. This will give the CFO a clear sense of the relative proportion of Spirits vs. Wine sales
- TAKE A SCREENSHOT and label is BIB2-B; paste it into a Word doc that you will use to submit this assignment.
- Below both screenshots, affirmatively answer the CFOs question what is the total Sales Dollars and percentage allocation of wine vs. spirits?
- What is the most popular Size for wine and for spirits based on Sales Dollars and Quantity?
- Create a new worksheet for this new visualization
- You are focused on Classification and package Size, so start by dragging these two Dimensions to the Columns shelf. Order matters!
- Lets start with the Sales Dollars question drag the Sales Dollars Measure to the Rows shelf. Be sure you are calculating the sum of Sales Dollars
- Consider what would be the most effective chart type to visualize Sales Dollars by product type and size. If your visualization was not automatically generated to reflect your chosen visualization, be sure to change it via the Show Me options
- The order of marks in your visualization may not serve to highlight the largest values it probably needs a sort. Sorting can be a bit tricky in Tableau when you have more than one Dimension featured in your visualization.
- Start by selecting the Descending Sort icon in the ribbon (just above the Columns shelf). Check out the Spirits classification and the Wine classification it doesnt really look like either one is in descending order. Thats because, when using the Descending Sort icon, it considers the TOTAL across dimensions for the measure being sorted.
- Thats not really what we want, though, because it makes it hard to answer our CFOs question. Instead, go to the Sales Dollars label along your Y-axis (dont have Sales Dollars on the Y-axis? You may want to revisit earlier steps). Just above the label (when you hover over it), you will see a little quick sort icon (looks like a graph) click on that. Your sort order for each classification should make more sense now.
- You may have quite a bit of distracting information in your chart related to clearly inconsequential package sizes. It would be great to help the CFO focus on the Top 5 package sizes within each product classification. If you did not have two different product classifications, it would be simple enough to create a filter on Sales Dollars and set the filter for Top 5. Unfortunately, that will achieve the desired effect here. We will accomplish this filtering a little differently:
- From the Analysis Tab (menu item), select Create Calculated Field. Rename the field by replacing the Calculation1 name with the words Top Size. In the space below the light grey horizontal line, add the following formula: Rank(sum([Sales Price])). Then click OK
- Youll find your new field in the Measures list. Drag it to the Filters shelf
- From the Filter pop-up, click the At Most option and set the upper limit to 5
- Now click OK and see the results of your Top 5 filter by Classification. Hmm if you ended up with a total of 5 marks across the two Classifications, thats ok. We can fix it.
- Hover over the Top Size Pill in the Filters shelf, then click on the drop-down arrow. Next hover over the Compute Using menu item, then select Pane (across) pop-out option
- Follow the process above to select At Most and set the upper limit to 5, click OK, and check your result
- Be sure to rename the worksheet tab to something meaningful
- TAKE A SCREENSHOT and label is BIB2-C; paste it into a Word doc that you will use to submit this assignment. Be sure to take note of the total Sales Dollars for the bottle size with the largest value in each Classification
- Now lets address the Quantity question drag the Sales Quantity measure to the Rows shelf. Drag the Sales Dollars pill OFF the Rows shelf. You may have to click the quick sort icon again next to Sales Quantity on the Y-axis
- TAKE A SCREENSHOT and label it BIB2-D; paste it into a Word doc that you will use to submit this assignment. Be sure to take note of the total Sales Quantity for the bottle size with the largest value in each Classification
- Report out the largest bottle size by Classification for Sales Dollars and for Sales Quantity; report the value/quantity for each. Also provide the CFO with some reasons why this information is important for managing the business
- Which Stores have the highest and lowest average sales price for wine and spirits?
- Create a new worksheet for this new visualization
- Start by creating a Calculated Field go to the Analysis Tab (menu item) and select Create Calculated Field. Label the field Average Sales Price in the pop-up window. Just below the light grey horizontal line, drag in the Sales Dollars pill, then type in the / symbol, then drag in the Quantity pill
- You should now see the Average Sales Price pill in the Measures listing. Drag the Average Sales Price pill into the Columns shelf and convert it from SUM to AVG by using the drop-down menu on the Average Sales Price Pill (change Measure(Sum) to Measure(Avg))
- Now drag the Classification dimension to the Rows shelf
- We need to look at Store for this analysis, but it currently exists as a Measure. We need it to be a Dimension. So drag the Store pill to the Dimensions listing.
- Once Store is in your Dimensions listing, drag it to the Rows shelf order is important here and it should appear AFTER the Classification pill
- This visualization will best serve your purposes as a horizontal bar chart if it doesnt appear this way, work with the Show Me tab to generate such a chart (a vertical bar chart would be acceptable, as well)
- Be sure to use the quick sort on the axis that has the Avg. Average Sales Price to sort in descending order
- Again we have a lot of distracting data in our visualization we cant even see the Wine classification! Use an approach similar to that used in creating the Top Size calculated field and filter above.
- This time, create a Top Avg Sales Price field using the Rank function, embedding within it the average of Average Sales Price
- Drag your new Top Avg Sales Price field to the Filters shelf
- Close out of the Filter pop-up menu for the moment
- Click on the drop-down arrow of the Top Avg Sales Price pill in the Filters shelf, select the Compute Using option of Pane (down) (or Pane (across) if you are using the vertical bar chart)
- Now from the Filter pop-up menu, select At Most 10 stores, then click OK
- Youre just about done! Change the worksheet tab name to something meaningful and then
- TAKE A SCREENSHOT and label it BIB2-E; paste it into a Word doc that you will use to submit this assignment. Be sure to indicate which store had the highest average sales price for Spirits, which one had the highest average for Wine, and include the average amount for each.
- The CFO has asked you, in your opinion, what other data/variables would you want to collect to improve the business and why?
<