cs 385 exam 3
Insert a slicer for the Discipline field.
Analyze tab, insert slicer, click discipline, click ok.
With cell B5 selected, show the book sales values as % of row total.
analyse tab, field settings, show value as arrow and click % of row total, click ok.
Change the Field Settings for the Total Book Sales field in the PivotTable by applying Accounting format with zero decimal places. Close the Value Field Settings dialog box.
analyze, field settings, number format, accounting, 0 decimals, ok
Create a clustered column PivotChart from the PivotTable (2) sheet.
analyze, pivotchart
Collapse the table to show the grand totals only.
click 1 at top left corner
Expand the table to show the grand and discipline totals.
click 2 at top left corner
Create a PivotTable using the Sum of Total Book Sales by Discipline recommended PivotTable.
click cell A5, insert tab, recommended pivotables, click sum of total book sales by discipline, click ok.
In cell B1 in the Books Data sheet, enter the GETPIVOTDATA function to cell F14 in the PivotTable sheet.
click cell B1, type =, click pivottable sheet, click cell F14
Click cell B5 and use the Value Field Settings dialog box to type the custom name Sales by Discipline for the values. Apply Accounting Number Format with zero decimal places.
click cell B5, analyze tab, field settings, type Sales by Discipline in the name box, number format, accounting, type 0 in decimal places, click ok, ok.
Create a calculated field named Author Royalties that multiplies the Total Book Sales field by .1.
click cell C5, analyze tab, click calculated field, in the name box type Author Royalties, click total book sales, in the formula box type *.1, click add, click ok.
Refresh the PivotTable.
click pivottable, analyze, refresh
Expand the Aging/Death discipline to display copyright years and book titles in the PivotTable.
click the aging/death expand (+) button on row 5
Use the Auto Outline feature to group the columns.
data tab, group arrow, select auto outline, click ok.
Create a relationship between the BOOKS table using the Discipline Code field and the DISCIPLINE table using the Disc Code field. Close the Manage Relationships dialog box.
data tab, in tool group select relationships, click new, fill in data, ok, close
Sort the data by Discipline and then by Area, both in alphabetical order.
data tab, sort, sort by discipline, add level, sort by area, click ok
Leave the existing subtotal values and add a second-level subtotal for Area.
data tab, subtotal, click at each change in: select area, unselect replace current subtotals, click ok
Use the Subtotal feature to sum the number of Wholesale Sales, Retail Sales, and Total Book Sales by Discipline.
data tab, subtotal, click the listed ones, click ok
Apply banded columns to the PivotTable.
design tab, click banded columns checkbox
Change the data labels from Values to Percentages and then close the Format Data Labels task pane.
double click label, select percentage, unselect value, close
Add the Copyright field to display as a row between the Discipline and Book Title fields in the Rows area.
drag copyright in between discipline and book title in the rows area
Use the Edition field as a filter for the PivotTable and display only 1st and 2nd editions.
drag the edition to the filter section, click the edition filter in cell B1, select the list, ok.
Display all tables within the PivotTable Fields List and select the Total Book Sales from the BOOKS table.
in fields pane click all, click on BOOKS, click total book sales
Drag the Edition field to the Columns area in the PivotTable.
in the field pane drag the edition to the columns area
From within the dataset on the Books sheet, create a PivotTable to add the data to the Data Model.
insert, click PivoTable, click add this data to the data model, click ok
Display two columns of buttons in the slicer, set the button width to 1.5 inches, and set the slicer height to 2 inches. Close any open task panes.
options tab, change column spin box to 2, change width to 1.5, change height to 2.
Change the Retail Price Rate to 125% in cell J1 in the Books Data worksheet.
type 125, click enter
