Creating Percentage Parent Row Totals in Excel PivotTables with Grouped Dates

Creating Percentage Parent Row Totals in Excel PivotTables Grouped by Dates

In this article, we will discuss how to create a PivotTable in Excel with columns grouped by dates and one column calculating percentage parent row totals. This is a useful technique for analyzing data and identifying trends over time.

Grouping Dates in a PivotTable

To group dates in a PivotTable, follow these steps:

1. Create a PivotTable with the desired data.
2. Drag the field containing the dates to the "Rows" area of the PivotTable.
3. Right-click on one of the dates in the PivotTable and select "Group" from the context menu.
4. In the "Grouping" dialog box, specify the start and end dates and the grouping interval (e.g. months, quarters, years).

Calculating Percentage Parent Row Totals

To calculate percentage parent row totals in a PivotTable, follow these steps:

1. Right-click on a cell in the PivotTable and select "Show Values As" > "Percentage of Parent Row Total" from the context menu.
2. The PivotTable will now display the values as a percentage of the parent row total.

Example

Let's say we have a dataset containing sales data for a company, with columns for the date of the sale, the region where the sale was made, and the sales amount. We want to create a PivotTable that groups the sales by month and calculates the percentage of total sales for each region.

First, we would create a PivotTable with the date field in the rows area, the region field in the columns area, and the sales amount field in the values area. Then, we would group the dates by month and calculate the percentage of parent row total for the sales amount field.

Sub CreatePivotTableWithPercentageParentRowTotals()

    'Create a new PivotTable
    Dim pt As PivotTable
    Set pt = Worksheets("Sheet1").PivotTableWizard

    'Add the date field to the rows area
    pt.PivotFields("Date").Orientation = xlRowField

    'Add the region field to the columns area
    pt.PivotFields("Region").Orientation = xlColumnField

    'Add the sales amount field to the values area
    pt.PivotFields("SalesAmount").Orientation = xlDataField

    'Group the dates by month
    pt.PivotFields("Date").Group StartDate:= _
        DateSerial(Year(Date), Month(Date) - 1, 1), EndDate:=DateSerial(Year(Date), Month(Date), 0)

    'Calculate the percentage of parent row total for the sales amount field
    pt.PivotFields("SalesAmount").Calculation = xlPercentOfParentRowTotal

End Sub

In this article, we have discussed how to create a PivotTable in Excel with columns grouped by dates and one column calculating percentage parent row totals. This is a useful technique for analyzing data and identifying trends over time. We have also provided an example VBA code to automate the process.

References

• Group dates in a PivotTable report
• Calculate percentage of parent row or column in a PivotTable