Pivot Tables
Master the drag-and-drop pivot table interface to analyze your data from every angle.
What is a Pivot Table?
A pivot table is a powerful tool that summarizes large datasets into meaningful insights. It lets you group, filter, and aggregate data by simply dragging fields to different areas.
Example: If you have 10,000 sales transactions, a pivot table can instantly show you total sales by region, broken down by product category, for each quarter — all with a few drag-and-drop actions.
Pivot Table Interface
The pivot table editor consists of:
Field List (Left Panel)
Shows all available fields from your data source. Fields are organized by type:
- Dimensions — text, dates (for grouping)
- Measures — numbers (for calculations)
Drop Zones (Layout Area)
Where you place fields to build your analysis:
- Rows — row labels
- Columns — column headers
- Values — aggregated numbers
- Filters — data filtering
Pivot Grid (Center)
The actual pivot table showing your summarized data with row/column totals and subtotals.
Properties Panel (Right)
Configure field settings, formatting, sorting, and other options.
Rows & Columns
Adding Fields
- Drag and drop — from field list to drop zone
- Double-click — automatically adds to the most appropriate zone
- Right-click — context menu with add options
Reordering Fields
Drag fields within a zone to reorder them. The order affects how data is grouped:
Rows: Region → Product = Products grouped under each region
Rows: Product → Region = Regions grouped under each product
Removing Fields
- Drag the field out of the drop zone
- Click the ✕ button on the field chip
- Right-click and select "Remove"
Moving Between Zones
Drag a field from one zone to another to change its role. For example, drag a field from Rows to Columns to pivot your data orientation.
Aggregations
When you add a field to Values, it's aggregated (summarized) across your grouped data. DATIRA supports many aggregation functions:
| Function | Description | Example |
|---|---|---|
| Sum | Total of all values | Total sales: $125,000 |
| Count | Number of records | Order count: 1,234 |
| Average | Mean value | Avg order: $101.38 |
| Min | Smallest value | Min price: $5.99 |
| Max | Largest value | Max price: $999.99 |
| Count Unique | Distinct values | Unique customers: 892 |
| Median | Middle value | Median age: 34 |
| Std Dev | Standard deviation | StdDev: 15.2 |
| Variance | Statistical variance | Variance: 231.04 |
To change aggregation: Click on the field in the Values zone → Select "Aggregation" → Choose your function.
Calculated Fields
Create new fields based on calculations using existing fields.
Creating a Calculated Field
- Click + Add Calculated Field in the field list
- Enter a name for your new field
- Build your formula using fields and operators
- Click Create
Example Formulas
Sorting
Control the order of values in your pivot table rows and columns.
Sort by Label
Alphabetical (A→Z) or reverse (Z→A). Click column or row header to toggle.
Sort by Value
Sort by a measure value (e.g., highest sales first). Right-click → Sort by Value.
Custom Sort
Define a specific order (e.g., Jan, Feb, Mar instead of alphabetical).
Top/Bottom N
Show only the top 10 products, bottom 5 performers, etc.
Totals & Subtotals
DATIRA automatically calculates grand totals and subtotals for your pivot table.
- Grand Totals — Show/hide row and column totals in table settings
- Subtotals — Appear when you have multiple fields in Rows or Columns
- Position — Choose to show subtotals at top or bottom of each group
- Multiple Measures — Each value field gets its own total row/column
To configure: Click the ⚙️ settings icon in the toolbar → Totals tab
Drill-Down
Click on any cell in the pivot table to see the underlying detail data.
How to Drill Down
- Click any data cell in the pivot table
- A detail panel opens showing all source rows that contribute to that value
- Use the search and filter options to explore the detail data
- Export the detail data to CSV if needed
Example: If a cell shows "$50,000 in East Region Q4", clicking it shows all individual transactions that sum to that $50,000.
Formatting
Customize how values appear in your pivot table.
Number Format
- Currency ($1,234.56)
- Percentage (45.2%)
- Decimal places (1,234 vs 1,234.00)
- Thousands separator
Date Format
- Short date (1/15/24)
- Long date (January 15, 2024)
- Month-Year (Jan 2024)
- Custom formats
Conditional Formatting
- Color scales (red-yellow-green)
- Data bars
- Icon sets
- Custom rules
Text Formatting
- Column width
- Text alignment
- Font weight (bold)
- Cell background
Keyboard Shortcuts
| Shortcut | Action |
|---|---|
| ⌘ + S | Save report |
| ⌘ + Z | Undo |
| ⌘ + ⇧ + Z | Redo |
| ⌘ + E | Export data |
| ⌘ + F | Find in data |
| Esc | Close panel / cancel |