90.2K

Verified Solution

Link Copied!

3. In the Revenue Report worksheet, in cell B4, create a PivotTable named Revenue by Month. Place the Date field from the Sales table in the Rows area. After Excel automatically groups the dates, remove the Date field from the Rows area, leaving only the Date (Month) field showing the names of the months. Change the PivotTable report layout to tabular form and do not display any grand totals in either the rows or the columns. 4. Place the Revenue field from the Sales table into the Values area four times and do the following to display sales revenue in different ways in your table: a. In the first column, Sum of Revenue, display the rank of the revenue values from largest to smallest and change the field label to Rank. b. In the second column, Sum of Revenue2, change the field label to Total Revenue. c. In the third column, Sum of Revenue3, track the increase in revenue over the year by display-ing the running total. Change the field label to Running Total. d. In the last column, Sum of Revenue4, display the revenue values as a percentage of the run-ning total. Change the field label to Percentage. 5. Examine how sales vary by region and agent. Go to the Region Report worksheet and create a PivotTable in cell B4 named Revenue by Region. Place the Sales Region and the Name fields from the Agents table in the Row area. Place the Revenue field from the Sales table in the Values area and change the field label from Sum of Revenue to Total Revenue. Change the layout to Outline form and add subtotals to the top of each region group. 6. Show the percentage of revenue generated by each agent and each region by placing the Revenue field a second time the Values area, displaying its values as a percent of the parent row total. Change the field label name to Percent. 7. To better view the top selling agents and regions, sort the items in the Sales Region column in descending order of the Percent field, and then sort the items in the Name field also in descend-ing order of Percent. 8. Determine how many hospitals bought a software license during the past years and from which agents. In the Hospital Sales worksheet, in cell B4, create a PivotTable named Clients. Set the report layout to tabular form, showing grand totals for both rows and columns. Place the Name field from the Agents table in the Rows area and place the Date (Month) field from the Sales table in the Columns area. 9. Place the Hospital field from the Sales table in the Values area. Show how many clients each agent had during the year by displaying the distinct count of the Hospital field. Change the field label to Clients. 10. In the PivotTable Options dialog box for the PivotTable, on the Layout & Format tab, enter 0 as the value to show for empty cells. 11. Calculate each agents total compensation from the past year. In the Sales Commissions work-sheet, create a PivotTable in cell B4 named Commissions. Set the PivotTable layout to tabular form with no grand totals. 12. Place the Name field from the Agents table in the Rows area. Place the Base Salary field from the Agents table in the Values area. Change the field label to Base Salary followed by a blank space. Place the Commission field from the Agents table in the Values area. Change the field label to Commission Rate. 13. Place the Revenue field from the Sales table in the Values area. Change the field label to Revenue Generated. Create a measured named Earned Commission in the Sales table with the description Commission earned by revenue generated above a minimum amount that calculates the amount of commission earned by a sales agent for revenue generated above a minimum level. Use the following DAX formula and format values returned by the measure as currency with no decimal places: =(SUM([Revenue]) - SUM(Agents[Sales Minimum]))*SUM(Agents[Commission]) (You can copy this formula from the Tables and Measures worksheet if you have trouble enter-ing it; be sure to insert an equal sign to start the formula and match all closing and opening parentheses.) 15. Create a measure named Total Compensation with the description Total compensation from base salary and earned commission that calculates the total compensation for each sales agent by adding the base salary and the earned commission. Use the following DAX formula and display total compensation in currency with no decimal places: =SUM(Agents[Base Salary])+[Earned Commission] 16. Make sure that the Earned Commission and Total Compensation measures are added to the PivotTable. 17. Sort the agent names in descending order of the Total Compensation column. 18. Save the workbook, and then close it.

Answer & Explanation Solved by verified expert

See Answer