Formatting pivot table

Enabling advanced pivot tables

1. In Vizpads, select the required pivot table and navigate to Configuration and enable the "Use Advanced Pivot" toggle.

2. The new pivot table displays data in a tree-like hierarchical structure that supports unlimited nesting levels. You can create hierarchies like Region → State → City → Product, with each level independently expandable.

3. Click expand/collapse arrows to show or hide detail levels. Your expansion choices remain intact during filtering and sorting.

4. The advanced pivot automatically calculates rollup totals at each hierarchy level (so you can see regional performance alongside state-level detail). Row totals show aggregations for parent categories, while column totals provide summaries across time periods or metric dimensions.

Column management

Click on the header column (the hamburger menu) of any pivot table and the following menu will be displayed.

• Pin Column: Pin critical columns to the left or right side so they remain visible during horizontal scrolling.

• Use "Collapse All" to collapse all the branches and "Expand all" to expand every branch.

• Use "Autosize This Column" for individual column width adjustments or "Autosize All Columns" for all the columns adjustments.

Advanced Conditional Formatting

Select a pivot table and navigate Formatting → Conditional Formatting → Add Formatting, the following window will be displayed.

When section:

• Column: Shows all available fields from current pivot table.

• Min/Max value display: Shows data range (minimum and maximum) to help set appropriate thresholds.

• Condition operators: Greater than, Less than, Equal to, Between etc.

• Value type: Toggle between "Column" (dynamic comparison) and "Fixed value" (static number).

If using Column type, select from Viz Config fields or underlying Business View (BV) columns.

Viz Config

In Viz Config, you’ll only see the fields actively used in your current pivot—specifically, the metrics and dimensions you’ve selected for that chart or table.

BV Columns

The BV Columns tab shows all available fields from the underlying Business View or dataset, regardless of whether they're currently being used in your chart or table.

Aggregation control: Change aggregation method (Sum, Avg, Count) for the selected column.

Apply Formatting To section:

• Target column selection: Choose which field receives the visual formatting

• Granularity options: Define comparison scope with "Lowest Granularity" toggle

• Dimension selector: When granularity disabled, choose specific dimensions for grouped comparisons

In the above example:

• Automatically selects: region, state_name, Device_Type, Date_order

• Calculates average Revenue across all combinations of these dimensions

• Compares each cell against the overall dataset average at the most granular level. Ideal when you want to compare against the entire dataset average

Example scenario: "Highlight any revenue value that's above the overall average across all regions, states, devices, and time periods"

Formatting Settings panel:

• Choose Background Color and Text Color as required.

• Right-side Preview panel shows the real-time preview showing how formatting will appear after applying changes.

Ways to leverage conditional formatting

Executive Dashboards: Automatically surface exceptions and outliers without manual monitoring. Revenue performance, customer engagement anomalies, and operational efficiency issues become immediately visible.

Territory Management: Compare rep performance against regional peers rather than global top performers.

Financial Analysis: Highlight budget variances, profit margin exceptions, and growth acceleration/deceleration patterns across business units and time periods.

Cross-column threshold formatting

Format one metric based on conditions in another metric. This is essential for highlighting relationships between different business measures. Quickly identify high-value segments with engagement issues that need immediate attention.

Example: Highlight bounce rates when revenue exceeds $1,000

1. When: Revenue (avg) > 1000 (fixed value)

2. Apply formatting to: Bounce Rate

3. Result: Red background on bounce rates for high-revenue segments

Relative value formatting with granularity

Compare performance against appropriate peer groups rather than overall averages. This addresses the common problem where high-performing regions dominate global comparisons.

Example:

1. Select the metric to evaluate (e.g., Revenue)

2. Choose "Greater than" condition

3. Set Value Type to "Column" and select the same metric

4. Disable "Lowest Granularity" to enable dimension-specific comparisons

5. Select granularity dimensions (Region, Product Category, etc.)

6. Apply formatting to highlight outperformers

A state might have lower revenue than California but significantly outperform other states in its region. This formatting highlights such relative success.

Time-based trend formatting

Automatically highlight period-over-period changes without manual calculation. Tellius compares each period to its immediate predecessor based on your data's time resolution (if "Monthly" resolution is selected, automatically compares month-to-month, compares day-to-day if "Daily" is selected and so on)

Example applications:

• Month-over-month growth: Green highlighting for improving metrics

• Quarterly decline detection: Red highlighting for deteriorating performance

• Seasonal pattern recognition: Consistent formatting reveals cyclical trends

Layering multiple rules

Tellius applies multiple formatting rules additively, creating rich visual hierarchies. For example,

Rule 1: Revenue > Regional_Average → Green background  
Rule 2: Bounce_Rate > 15% → Red text color
Rule 3: Month_over_month_decline → Bold font weight

Later rules override conflicting properties from earlier rules, but complementary properties stack (background + text color + font weight).