The PivotTable object has two properties PivotColumnAxis and PivotRowAxis of type PivotAxis, containing all lines on columns or rows of the pivot. However, this is more complicated as it seams, especially in the ColumnRange area. On cell level we could use PivotCell.PivotCellType = xlPivotCellGrandTotal (value 3). So we need other properties to determine if a cell or range is a Grand Total. Unfortunately there are no ranges like GrandTotalsRange, RowGrandTotalsRange or ColumnGrandTotalsRange. (In this post Jon Peltier gave a useful visualization of these ranges.) Table 1 gives an overview of all range properties that are available in the PivotTable object and it’s building blocks: Table 1: Range properties of the PivotTable Also, we can have no Grand Totals at all, when we set the properties PivotTable.RowGrand and PivotTable.ColumnGrand to false. So, we can have one, two or even more Grand Total lines on rows or columns. Note that when we move the Values from the Columns to the Rows section we will have two Grand Totals on each column, like so: Fig 3: The same pivot with the Values on rows The pivottable has two Grand Totals on rows, because there are two measures in the Values area. This pivottable (based on data from AdventureWorks) has the following definition: Fig 2: PivotTable Fields of the pivottable in Fig 1 Let’s look at an example of a pivottable: Fig 1: A Pivottable’s Grand Totals range So I developed a generic solution that I want to share with you. Solutions I found on the Internet were for specific cases. Unfortunately the PivotTable object has no property for this. Drag a second copy of Measure Values to Color on the Marks card.In a recent project I needed the range of the Grand Totals in any pivottable in Excel.Drag Measure Vales to Label on the Marks card.Drag a second copy of Measure Names to the Columns shelf.In the Filters dialog, check only all of the calculations created in steps 1-3 and click OK.Drag Measure Names to the Filters shelf.Remove Sales from both Label and Color.Remove YEAR(Order Date) from the Columns shelf.Repeat steps 1-2 for every year in the view.In this example, the calculated field is named "2011 Sales" In the Calculated Field dialog box that opens, do the following, and then click OK:.Select Analysis > Create Calculated Field.The example workbook "Segmented Grand Totals.twbx" downloadable from the right-hand pane of this article uses the sample data set Superstore to create a separate calculated field to isolate each year's sales. Option 3: Replace the year dimension with multiple calculated fields When the end user hides Category then they will see the year totals.Replace Category on the Columns shelf with Category or blank.In the formula field, create a calculation similar to the following:.In this example, the calculated field is named "Category or blank" Right-click Toggle Category in the Data pane and select Show Parameter Control.In this example I will call it "Toggle Category" In the Create Parameter dialog, do the following and click OK:.Click the down arrow next to Dimensions in the data pane and select Create Parameter….The example workbook "Segmented Grand Totals.twbx" downloadable from the right-hand pane of this article, uses the sample data set Superstore to create a parameter to either hide or show the Category. Option 2: Allow the user to hide Category to see Year totals In the Edit Tooltip dialog, click Insert > Sheets > sales per year.In this example, that is the "sales per year" worksheet, navigate to the "sales per year" worksheet to see detailed directions on how it was created. Create a worksheet to show the desired totals.The workbook contains detailed directions for how to create the "Original" worksheet. These directions start from the "Original" worksheet. For more information on visualizations in a tooltip, see Viz in tooltip. The workbook demonstrates how to add a visualization in a tooltip to allow the end user to see the trend across years in the tooltip. The example workbook "Segmented Grand Totals.twbx" downloadable from the right-hand pane of this article, uses the sample data set Superstore. QuestionHow to segment grand totals to create a more detailed summary.įor example, if the highlight table shows a column for every year's sales within each category, we may want to segment grand total by year so that there is a total of sales across all categories for each year.Īnswer Option 1: Use a viz in tooltip to show each year's total in relation to all years
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |