Combination layouts using PivotTable
Combination Layouts The PivotTable drop-down in the PowerPivot Window offers eight choices: If you plan to convert the pivot table to values to reuse it, choose a flattened pivot table which is only a few clicks along the way. A combination report offers one or more pivot tables or charts. Note that for each chart on your layout, Microsoft inserted a new worksheet to hold the actual pivot table for the chart The following steps show how to replicate the layouts shown in the blogs and press: 7 1. Open the Excel file BlogsAndPress. Link it to PowerPivot Window. (Hint: Please review the section on adding calculated columns using DAX. You have to add two new columns – Month and Year to Table1.) 2. Once you added two columns Month and Year to your linked data table, you need to select Four Chart option from Pivot Table menu in PowerPivot window. 3. Select New Worksheet instead of Existing Worksheet in the dialog box. 4. If it does not work well the first time, choose a location rather than letting them default. Choose a spot on Row 5 of the new worksheet (Sheet1 probably). You can always drag a chart structure to wherever you want on the worksheet later as well. 5. Go back to Power Pivot window and select Four Chart option for Existing Worksheet, but Sheet1 location data should show in the text box. 6. Add as many slicers as possible to the top and left of the chart. 7. Look into the charts image on the next page. Simple bar chart on top of the pie chart shows the values of Year and Month. Since these two variables are on the same level, you need to move them into the same Axis field as shown in the field list. The bars should represent the Count of Source. Feel free to use the cross button to change the chart element and the brush button to change the bar color as you like. 8. The pie chart shows Count of Type values by Source. 9. For Media Type, you need to choose Stacked Area from chart models to use. It is created by selecting Type, Month and Year from the Field list. The formula is Type * Year and Month. Legend of the chart should use Type as well. 10. The last chart uses the Stacked Bar Chart model. Webinar belongs to Type. The 3 Rd Party is a Source. Thus, it should be created using Type (on Vertical Axis) by Source (as Values), using Source as legend. You may still need to fine tune each chart to make it look identical to the one in the image. 8 11. Now you are ready to make the data display look better. Make Row 1 very tall, perhaps 270 to 300 points tall. Use Insert, Screenshot to add an interesting graphic to Row 1. 12. Add an interesting graphic below the charts to balance the graphic on top of the charts. 13. Go to file, Options, advanced, Display Options for This Worksheet. Clear the Gridlines check box. If you want to go all out, scroll up and clear the scroll bars, sheet tabs, and formula bars. 14. Minimize the ribbon. 15. Add a fill color behind the whole worksheet. 16. Although the pivot table is active, click on the bounding box around each slicer. Right-click on the border. Select properties. Select Move and /Size with cells. 17. Click away from the pivot table. You’ve reached the end of this long assignment. If you ever changed any data in a table, the changes may not be reflected in the pivot table immediately. However, in the top part of the PowerPivot Field List, you may see a warning message to refresh the relevant pivot table and please do it accordingly. If you see the values shown in a pivot table different from those in a given image, examine the measure(s) used and the relationship between the tables in PowerPivot window. They might be the sources of errors. 9 When you complete this assignment, please save both data files, each using the original file name and your name initials as the new file name (the pattern should be ). Submit both files to the relevant assignment submission dropbox in Learn 9. _______________________ *: This assignment is adapted from Chapter 10 in Pivot Table Data Crunching: Microsoft Excel 2010 written by Bill Jelen, Michael Alexander, 2013, Que Publishing, Indianapolis, IN, USA.