Overview
The Pivot Table calculator creates a cross-tabulation view that organizes and summarizes your process data by multiple dimensions simultaneously. This powerful analytical tool allows you to pivot case or event attributes across columns while aggregating metrics such as counts, sums, averages, or other statistical measures. Unlike the Breakdown by Categories calculator which shows one or two categorical breakdowns, the Pivot Table creates a traditional spreadsheet-style pivot table with rows and columns.
The calculator transforms raw process data into an organized summary table where you can analyze relationships between different categorical dimensions and understand how various metrics distribute across those dimensions.
Common Uses
- Create financial summary reports showing costs or revenues by department and time period
- Analyze resource workload distribution across different activities and organizational units
- Compare performance metrics across multiple categorical dimensions
- Generate compliance reports showing conformance rates by category and subcategory
- Build executive dashboards with multi-dimensional process metrics
- Perform cross-tabulation analysis to identify patterns across multiple process attributes
Settings
Row Attribute: Select the case or event attribute that will form the rows of your pivot table. Each unique value of this attribute becomes a row in the output table.
Column Attribute (optional): Select a second attribute that will form the columns of your pivot table. Each unique value creates a separate column. If not specified, the pivot table displays a single-column summary.
Aggregate Function: Choose how to aggregate the data at each row-column intersection:
| Function | Description | Use When |
|---|---|---|
| Count | Counts the number of cases or events | You want to know how many items fall into each category |
| Sum | Adds up values of a selected attribute | You need totals (e.g., total costs, total revenue) |
| Average | Calculates the mean of a selected attribute | You want typical values (e.g., average processing time) |
| Minimum | Finds the smallest value | You need to identify lowest values in each category |
| Maximum | Finds the largest value | You need to identify highest values in each category |
| Median | Calculates the middle value | You want the typical value less affected by outliers |
Value Attribute: For aggregate functions other than Count (such as Sum or Average), select the numerical attribute you want to aggregate. This field is not needed for Count operations.
Sort Order: Choose whether to sort rows:
- Ascending: Sort alphabetically or numerically from lowest to highest
- Descending: Sort from highest to lowest (useful for identifying top categories)
Max Rows: Specify the maximum number of rows to display. This is useful when you have many unique values but only want to see the top N categories. Leave empty to show all rows.
Examples
Example 1: Resource Activity Summary
Scenario: You want to create a summary table showing how many times each resource performed each activity in your process. This helps you understand workload distribution and identify resource specialization patterns.
Settings:
- Row Attribute: Resource
- Column Attribute: Activity Name
- Aggregate Function: Count
- Max Rows: 20
Output:
The calculator produces a pivot table with resources as rows and activities as columns. Each cell shows the number of times that resource performed that activity:
| Resource | Create Order | Approve Order | Process Payment | Ship Items | Close Order | Total |
|---|---|---|---|---|---|---|
| Alice Johnson | 245 | 189 | 0 | 0 | 234 | 668 |
| Bob Smith | 0 | 0 | 456 | 0 | 0 | 456 |
| Carol White | 123 | 145 | 234 | 0 | 198 | 700 |
| David Brown | 0 | 0 | 0 | 567 | 0 | 567 |
| Emma Davis | 189 | 234 | 123 | 0 | 212 | 758 |
Insights: This pivot table reveals clear activity specialization patterns. Bob Smith exclusively handles payment processing (456 events), while David Brown specializes in shipping operations (567 events). Alice, Carol, and Emma handle multiple activities, suggesting they are generalists. Emma has the highest total activity count (758), indicating she is your most active resource. The pivot format makes it easy to identify workload imbalances - for example, only three resources handle order approval (Alice, Carol, Emma), which could be a bottleneck. You can click on any cell to drill down into the specific cases where that resource performed that activity.
Example 2: Department Cost Analysis by Month
Scenario: You need to create a financial report showing total costs by department across different months, helping you track spending patterns and identify departments with unusual cost variations.
Settings:
- Row Attribute: Department
- Column Attribute: Case Start Month
- Aggregate Function: Sum
- Value Attribute: Total Cost
- Sort Order: Descending
Output:
The calculator creates a pivot table with departments as rows and months as columns, showing total costs for each combination:
| Department | January | February | March | April | May | Row Total |
|---|---|---|---|---|---|---|
| Operations | $456,789 | $478,234 | $502,456 | $489,123 | $495,678 | $2,422,280 |
| Sales | $234,567 | $245,678 | $256,789 | $267,890 | $278,901 | $1,283,825 |
| IT | $189,234 | $195,678 | $198,234 | $201,456 | $205,678 | $990,280 |
| HR | $89,234 | $92,345 | $87,234 | $91,456 | $93,678 | $453,947 |
| Finance | $67,890 | $71,234 | $69,345 | $72,456 | $74,567 | $355,492 |
Insights: The pivot table provides a clear financial overview. Operations has the highest total costs at $2.4M over five months, which is expected for the largest department. Operations shows a steady upward trend from $456K in January to $495K in May, indicating either growing activity or increasing costs per case. Sales also shows consistent month-over-month growth ($234K to $278K), possibly reflecting business expansion. IT costs are relatively stable around $195-205K per month. HR shows slight variation (dip in March to $87K), which might warrant investigation. This pivot format makes it easy to spot both total spending by department (row totals) and monthly trends across all departments (column totals).
Example 3: Conformance Analysis by Variant and Department
Scenario: You want to understand which process variants have conformance issues and how this varies across different departments. This helps you target process improvement efforts.
Settings:
- Row Attribute: Process Variant
- Column Attribute: Department
- Aggregate Function: Count
- Max Rows: 10
Output:
| Variant | Sales | Operations | Customer Service | Total |
|---|---|---|---|---|
| Standard Path | 1,234 | 2,456 | 1,789 | 5,479 |
| Skip Approval | 234 | 67 | 456 | 757 |
| Rework Loop | 123 | 345 | 234 | 702 |
| Express Processing | 456 | 189 | 234 | 879 |
| Manual Override | 89 | 234 | 123 | 446 |
Insights: The Standard Path is most common across all departments (5,479 cases total), which is positive. However, the "Skip Approval" variant appears 757 times across all departments, with the highest occurrence in Customer Service (456 cases). This suggests Customer Service may be under pressure to bypass approval steps. The "Rework Loop" variant appears 702 times, with Operations showing the highest count (345), indicating possible quality issues in that department. Sales shows high usage of "Express Processing" (456 cases), which may be legitimate expedited handling or could indicate shortcuts. By clicking on specific cells, you can drill down to investigate individual cases and understand why these non-standard variants are occurring.
Example 4: Average Case Duration by Activity Combination
Scenario: You want to identify which combinations of first and last activities result in the longest average case durations, helping you understand which process paths are slowest.
Settings:
- Row Attribute: First Activity
- Column Attribute: Last Activity
- Aggregate Function: Average
- Value Attribute: Case Duration (Days)
- Sort Order: Descending
Output:
| First Activity | Standard Close | Manual Close | Cancelled | Exception Close | Average |
|---|---|---|---|---|---|
| Manual Entry | 12.5 days | 18.7 days | 8.3 days | 23.4 days | 15.7 days |
| Auto Import | 6.2 days | 14.3 days | 5.1 days | 19.8 days | 11.4 days |
| Web Submission | 5.8 days | 13.9 days | 4.8 days | 18.2 days | 10.7 days |
| Email Receipt | 8.9 days | 16.5 days | 6.7 days | 21.3 days | 13.4 days |
Insights: Cases starting with Manual Entry take the longest on average (15.7 days), suggesting this entry method may introduce complexity or errors. The "Exception Close" path is consistently the slowest regardless of how the case starts (ranging from 18.2 to 23.4 days), which makes sense as exceptions require special handling. Manual Entry cases that end in Exception Close have the worst combination at 23.4 days average duration. Auto Import and Web Submission cases are fastest overall (11.4 and 10.7 days average), indicating these digital channels may have better data quality. The pivot table format makes it easy to compare all combinations and identify the highest-impact improvement opportunities - focusing on reducing Manual Entry cases or streamlining Exception Close handling could significantly improve average durations.
Example 5: Resource Performance Scorecard
Scenario: You need to create a performance scorecard showing average case duration for each resource across different case complexity categories.
Settings:
- Row Attribute: Resource
- Column Attribute: Case Complexity
- Aggregate Function: Average
- Value Attribute: Case Duration (Hours)
- Sort Order: Ascending
- Max Rows: 15
Output:
| Resource | Simple | Medium | Complex | Very Complex | Overall Average |
|---|---|---|---|---|---|
| Alice Chen | 2.3 hrs | 5.7 hrs | 12.4 hrs | 28.5 hrs | 12.2 hrs |
| Bob Martinez | 2.8 hrs | 6.2 hrs | 13.1 hrs | 31.2 hrs | 13.3 hrs |
| Carol Taylor | 2.1 hrs | 5.4 hrs | 11.8 hrs | 26.7 hrs | 11.5 hrs |
| David Wilson | 3.1 hrs | 6.8 hrs | 14.5 hrs | 34.2 hrs | 14.7 hrs |
| Emma Johnson | 2.2 hrs | 5.6 hrs | 12.1 hrs | 27.8 hrs | 11.9 hrs |
Insights: Carol Taylor shows the best performance across all complexity categories with an overall average of 11.5 hours, while David Wilson takes the longest at 14.7 hours. The consistency of Carol's performance across all complexity levels (2.1 to 26.7 hours) suggests strong skills regardless of case difficulty. All resources show appropriate duration scaling from Simple to Very Complex cases, which validates that the complexity categorization is meaningful. David's performance on Very Complex cases (34.2 hours) is notably higher than Carol's (26.7 hours), suggesting he might benefit from additional training or support on complex cases. This pivot table format makes it easy to identify both top performers (Carol, Emma, Alice) and those who might need additional support (David), while also showing that the team appropriately allocates more time to complex cases.
Example 6: Quality Metrics Dashboard
Scenario: You want to create a quality dashboard showing the percentage of cases with quality issues across different product lines and customer segments.
Settings:
- Row Attribute: Product Line
- Column Attribute: Customer Segment
- Aggregate Function: Average
- Value Attribute: Quality Score (0-100)
Output:
| Product Line | Enterprise | Mid-Market | Small Business | Consumer | Average |
|---|---|---|---|---|---|
| Premium | 94.5 | 92.3 | 89.7 | 87.2 | 90.9 |
| Standard | 89.2 | 87.6 | 84.3 | 82.1 | 85.8 |
| Economy | 85.7 | 83.4 | 79.8 | 77.5 | 81.6 |
| Custom | 96.2 | 94.8 | 91.2 | 88.9 | 92.8 |
Insights: Custom products show the highest quality scores across all customer segments (92.8 average), likely due to personalized attention and quality control. Premium products also perform well (90.9 average). There is a clear quality gradient across customer segments - Enterprise customers receive higher quality (94.5 for Premium) compared to Consumer customers (87.2 for Premium), which might reflect different service levels or SLAs. Economy products show the lowest quality scores overall (81.6 average), particularly for Consumer segment (77.5), which is approaching concerning levels. The 17.5 point gap between Custom/Enterprise (96.2) and Economy/Consumer (77.5) suggests significant process differences. This pivot table helps you prioritize improvement efforts - focus on Economy products for Consumer and Small Business segments where quality is lowest.
Output
The Pivot Table calculator displays a spreadsheet-style table with rows corresponding to unique values of your row attribute and columns corresponding to unique values of your column attribute (if specified). Each cell contains the aggregated value for that row-column combination.
Row Totals: When column attributes are specified, the calculator typically includes a row total column showing the aggregate across all columns for each row.
Column Totals: A total row at the bottom shows the aggregate across all rows for each column.
Interactive Features: Click on any cell value to drill down and view the specific cases that contribute to that cell's value. This enables detailed investigation of any category combination.
Export Options: Export the pivot table to Excel or CSV for further analysis, reporting, or sharing with stakeholders.
Visualization: Depending on your data, the pivot table can be visualized as a heat map with color coding to highlight high and low values, making patterns easier to spot visually.
Large Tables: For pivot tables with many rows or columns, use horizontal and vertical scrolling to navigate the full table. Consider using the Max Rows setting to focus on top categories.
This documentation is part of the mindzie Studio process mining platform.