Learn how to add row counts to each group in a pivot chart.
If you want row-counts in a pivot chart, such as for counting how many trips ran lights and sirens versus how many did not, go to the sidebar, switch to the "Columns" tab, find the very first column (usually named "Dispatch ID" or "Employee ID" or some other record ID), drag the column to the "Values" box, and then click it to change its aggregation function to COUNT. You will then see the count of all rows in each pivot category.
ID columns are best for this purpose because all their values are unique, and they are conveniently placed at the top of the columns list.
Example Walkthrough
To create a pivot report with chart showing how often your crews run lights and sirens, based on dispatch complaint, do this:
- This example requires the Dispatches-Overview and Dispatches-Runtimes datasets.
- From the sidebar, switch to the "Filters" tab, find the "Execution Status" column, and filter out any "Delegated to Affiliate" rows.
- While you're in the "Filters" tab, possibly you will want to filter the "Complaint" column to exclude "Transfer / Palliative Care" trips, as well as the "Service Level Requested" column to exclude car, wheelchair, and gurney transports.
- From the sidebar, switch to the "Columns" tab, and activate the "Pivot Mode" switch at the top of the sidebar.
- From the "Columns" tab, find the "Complaint" column, and drag it to the "Row Groups" box.
- Find the "Dispatch ID" column, drag it to the "Values" box, and click it to change its aggregation function to COUNT.
- Find the "Transport Mode To Scene - Sirens" column (in the Dispatches-Runtimes column set) and drag it to the "Column Labels" box.
- In the sidebar, click the "Columns" tab to hide the sidebar.
- In the grid, the "Group" column -- which shows the complaints -- will be at the right side of the grid. Drag its column header to the far left, where it will be easier to read.
- In the grid, click and drag to select all rows and columns.
- In the grid, right-click the selected area, select "Pivot Chart", then "Column", then "Stacked". Your chart will appear.
- Move and resize the chart to your liking, and change its colors or layout as needed.