Ch 11 excel exercise

Open the Problem_Data_CH11.xlsx EXCEL workbook. Review the four worksheets, then a create table for the data in each worksheet (Hint: Insert > Tables > Table. Name each table the same as the worksheet. )

Then, establish relationships between Sales and the other three worksheets (Hint: Data > Data Tools > Relationships). Create relationships. Note that the table with the foreign key should be listed first and the table with the primary key is listed as the Related Lookup Table.

Summarize the data with a pivot table using the workbooks data model (Hint: Design > Tools > Summarize with PivotTable; select the box related to use of data model on the dialog box).

Drag ChannelName to the Rows box and SalesAmount to the Values box. Format Sum of SalesAmount as currency. Change the custom name to Channel Sales.

Click on the Sum of SalesAmount in the Values area and select Value Field Settings from the dropdown menu. Click on the number format button to format the field as currency. Close the number format window. Replace Sum of SalesAmount with Channel Sales in the Custom Name box.

Add a PivotChart. Accept the default clustered column chart. Click Analyze > Tools > PivotChart.

Change the title of the pivot chart

Change the name of the worksheet to Problem 1.

In the same spreadsheet after completing problem 1, return to the Sales worksheet.

Summarize the data with a pivot table using the workbooks data model (Hint: Design > Tools > Summarize with PivotTable; select the box related to use of data model on the dialog box).

Drag ChannelName to the Rows box and SalesAmount to the Values box. Go to Value Field Settings. Select the Show Values As tab in the middle of the dialog box, use the pull-down menu to select Percent of Column Total. Close the Value Field Settings dialog box.

Add a PivotChart. Select the Pie Chart option. Change the title to Percent of Sales by Channel and add data labels to the outside end of each slice (Hint: Design > Chart Layouts > Add Chart Element; select data labels > outside end).

Save spreadsheet.

Now go to Tableau. Import the original Ch 11 Excel file. Drag the sheets to the canvas and make sure they connect as shown

Adjust the Connections if needed. Click on the double-circle icon to examine the Join properties. The default is an Inner join showing records where there is matching information in both tables. Customer Number in Orders is correctly linked to Customer Number in Customers. If not, you can select the appropriate fields to join the two tables by clicking on the field name and selecting the correct field from the popup list.

First visualization: Drag Store Location to the Rows shelf and Sale Amount to the Columns shelf. Note that dimensions display as blue pills and measures display as green pills. By default, Sale Amount is summed. The axis is adjusted here, but your chart should start at 0.

The Show Me button on the toolbar displays the visualization options for the selected fields (hold the CTRL key and select various dimensions and measures to see options).

Click the Color icon in the Marks shelf to change colors in your visualization, change opacity, and add borders.

Click on the Size icon to change the size of the bars.

Click on the Label icon (or click on the T icon on the ribbon bar) to add mark labels.

Select the Analytics Tab above the field list and add analytics

Insert a new Dashboard to display multiple worksheet visualizations. Hover over sheet names to see previous visualizations.

Try to repeat the summaries you did in Excel sales by store, by channel, by product.

Extract your completed charts and submit.