Power BI Matrix Visual
Enterprise guide to row/column grouping, conditional formatting, subtotals, expand/collapse, cross-highlighting, and performance optimization for matrix visuals.
What Is a Matrix Visual in Power BI?
How do you use the matrix visual in Power BI? The Power BI matrix visual is the equivalent of an Excel pivot table. It groups data by row headers and column headers, displaying aggregated measure values at each intersection. Drag dimension fields into Rows and Columns wells, then drag measures into Values. The matrix supports hierarchical row grouping with expand/collapse, subtotals at every level, conditional formatting (color scales, data bars, icons), stepped layout for compact hierarchy display, and cross-highlighting with other visuals. For enterprise dashboards, the matrix is the go-to visual for multi-dimensional analysis — comparing metrics across regions, time periods, product lines, and organizational hierarchies simultaneously.
The matrix visual is one of the most powerful yet underutilized visuals in Power BI. While most report builders default to bar charts and cards, the matrix provides the structured, multi-dimensional analysis that finance teams, operations managers, and executives actually need — the ability to see exact numbers across multiple dimensions with drill-down capability.
Think of the matrix as a dynamic pivot table connected to a live data model. Unlike a static Excel pivot table that requires manual refresh, the Power BI matrix updates automatically when the underlying data refreshes, respects row-level security, and interacts with every other visual on the page through cross-highlighting and cross-filtering.
EPC Group has implemented Power BI solutions for Fortune 500 organizations where the matrix visual serves as the primary analytical interface — financial consolidation reports, sales performance dashboards, operational KPI trackers, and regulatory compliance views. This guide covers every aspect of the matrix visual at enterprise scale.
Matrix Visual vs Table Visual
Understanding when to use a matrix versus a table is critical for effective report design. The matrix excels at cross-tabulated analysis while the table excels at detail-level data display.
| Feature | Matrix Visual | Table Visual | Better For |
|---|---|---|---|
| Data Grouping | Groups by row and column headers | No grouping — flat rows | Matrix |
| Hierarchical Rows | Multiple fields create drillable hierarchy | No hierarchy support | Matrix |
| Column Pivoting | Drag fields to Columns well for pivot | No column pivoting | Matrix |
| Subtotals/Grand Totals | Built-in subtotals at every level | Grand total only (limited) | Matrix |
| Expand/Collapse | Full expand/collapse with +/- icons | Not available | Matrix |
| Detail-Level Data | Shows aggregated intersections | Shows individual records | Table |
| Sorting | Sort by any column or measure | Sort by any column | Tie |
| Conditional Formatting | Full support (background, font, data bars, icons) | Full support | Tie |
| Export to Excel | Exports with hierarchy intact | Exports flat rows | Tie |
| Performance (Large Data) | Slower — calculates every intersection | Faster — renders flat rows | Table |
Row and Column Grouping
The matrix visual's power comes from its ability to group data along two axes simultaneously. Rows define the vertical hierarchy (what you are analyzing), while columns define the horizontal dimension (how you are slicing the analysis).
Row Grouping (Hierarchies)
Drag multiple fields into the Rows well to create a drill-down hierarchy. The matrix displays the top level by default with expand/collapse controls for each row.
- Region → Country → City → Store
- Department → Team → Employee
- Product Category → Subcategory → Product
- Year → Quarter → Month → Week
Column Grouping (Pivoting)
Drag a field into the Columns well to pivot values horizontally. This creates a cross-tabulated view identical to an Excel pivot table column area.
- Fiscal Quarter (Q1, Q2, Q3, Q4)
- Product Line (side-by-side comparison)
- Status (Active, Closed, Pending)
- Channel (Online, Retail, Wholesale)
For enterprise financial reports, a common configuration is: Rows = Business Unit → Cost Center → GL Account, Columns = Fiscal Month, Values = Actual, Budget, Variance. This single matrix replaces dozens of static Excel pivot tables and updates automatically with each data refresh.
Subtotals and Grand Totals
Subtotals are essential for hierarchical matrices where users need to see aggregated values at each grouping level. Power BI provides granular control over subtotal placement and calculation.
Row Subtotals
Display aggregated values for each parent row. For a Region → Country hierarchy, row subtotals show the total for each Region. Position subtotals at the top (before child rows) or bottom (after child rows) of each group.
Column Subtotals
Display aggregated values for each column group. When columns represent quarters, column subtotals can show half-year or annual totals. Enable per-column-level control to show subtotals only at specific hierarchy levels.
Grand Totals
The overall total across all rows and columns. Grand totals appear in the last row and/or last column. Customize the label from "Total" to any text (e.g., "Company Total" or "All Regions").
Custom Subtotal Logic
Use ISINSCOPE() and HASONEVALUE() DAX functions to change calculations at subtotal rows. For example, show SUM at detail level but AVERAGE at the subtotal level, or display weighted averages instead of simple sums for percentage metrics.
Enterprise Tip: Percentage measures (margin %, conversion rate) should never use default SUM subtotals — the subtotal of percentages is mathematically incorrect. Use DAX with ISINSCOPE to calculate the correct weighted percentage at the subtotal level. EPC Group fixes this error in nearly every enterprise Power BI audit we perform.
Stepped Layout for Compact Hierarchies
Stepped layout is a matrix formatting option that displays hierarchical row fields in a single column with indentation, rather than spreading each level across separate columns. This saves significant horizontal space and reads more naturally.
Without Stepped Layout
Each hierarchy level gets its own column, consuming horizontal space:
With Stepped Layout
All levels in one column with indentation — compact and readable:
Enable stepped layout in Format > Row headers > Stepped layout. Adjust indentation pixel size to match your report theme. Stepped layout works best with 2-4 hierarchy levels. For deeper hierarchies (5+ levels), combine stepped layout with expand/collapse so users see only 2-3 levels at a time and drill into detail on demand.
Conditional Formatting in Matrix Visuals
Conditional formatting transforms a matrix from a wall of numbers into an instantly scannable decision tool. Apply color scales, data bars, icons, and rules to highlight what matters.
Background Color Scales
Apply gradient color scales to cells based on value. Use diverging scales (red → white → green) for variance analysis, sequential scales (light → dark blue) for magnitude, and custom scales for brand alignment. Configure independently per measure.
Example: Revenue cells: light green (low) to dark green (high)
Font Color Rules
Change text color based on rules or gradients. Common pattern: red font for negative values, green for positive. Combine with background formatting for maximum readability — dark background requires light font colors.
Example: Variance %: red text if negative, green if positive
Data Bars
Display horizontal bars within cells showing relative magnitude. Data bars provide instant visual comparison without requiring separate chart visuals. Configure bar color, direction (left-to-right or bidirectional for negative values), and maximum value.
Example: Sales figures: blue bars proportional to value
Icon Sets
Add icons (traffic lights, arrows, flags, stars) to cells based on value thresholds. Icons are immediately scannable in dense matrices. Configure three or five icon thresholds (e.g., red/yellow/green traffic lights at 0-60%, 60-80%, 80-100%).
Example: KPI status: red/yellow/green circle icons
Rules-Based Formatting
Define explicit rules: if value is between X and Y, apply specific formatting. Rules override gradient formatting for precise control. Create multiple rules that evaluate sequentially — first matching rule wins.
Example: If budget variance > 10%, red background
Field Value Formatting
Reference a column in your data model that contains color hex codes. This enables dynamic formatting driven by business logic in your data source — the database determines the color, not the report. Powerful for compliance RAG statuses managed externally.
Example: Color column in data model: #FF0000 for critical
For enterprise conditional formatting best practices, apply formatting sparingly — a matrix where every cell is color-coded becomes unreadable. Focus conditional formatting on the 1-2 measures that require immediate attention (e.g., variance to budget, risk score) and leave supporting measures unformatted for clean contrast.
Expand/Collapse and Cross-Highlighting
Two features that make the matrix visual truly interactive: expand/collapse for hierarchical exploration and cross-highlighting for multi-visual analysis.
Expand/Collapse
- Click +/- icons on individual rows to drill into specific items
- Set default expansion level in Format > Row headers
- Use "Expand all down one level" button in the visual header
- Create bookmarks for preset expansion states (collapsed executive view, expanded detail view)
- Combine with drill-through pages for deep-dive analysis on individual rows
- Performance tip: collapsed rows do not query the database — keep the matrix collapsed by default for large datasets
Cross-Highlighting
- Click any cell to cross-highlight related data in all other visuals
- Click a row header to highlight all values for that dimension
- Click a column header to highlight all values for that time period or category
- Configure interaction type per visual: cross-highlight, cross-filter, or none
- Cross-filter mode hides non-selected data entirely — more aggressive than highlighting
- Ctrl+click to select multiple rows or columns for multi-selection cross-highlighting
Enterprise dashboard design tip: place the matrix as the primary analytical visual on the page, with supporting chart visuals (bar, line, map) that respond to matrix selections. When a finance director clicks a business unit row in the matrix, the trend chart updates to show that business unit's performance over time, the map highlights that unit's geographic footprint, and the KPI cards update to show that unit's metrics. This interaction pattern replaces dozens of static reports with a single interactive page.
Performance Optimization for Large Matrices
Matrix visuals generate a query for every visible cell intersection. A matrix with 100 rows, 12 columns, and 3 measures generates 3,600 calculations. Performance optimization is critical at enterprise scale.
Limit Visible Rows
Use Top N filtering to display 50-100 rows. Let users drill into detail rather than rendering thousands of rows at once. A matrix with 10,000 rows is unusable anyway — users cannot scan that much data visually.
Reduce Measures
Each measure in the Values well multiplies the query count by the number of intersections. Start with 2-3 key measures and use drill-through or tooltips for supplementary measures rather than displaying them all.
Pre-Aggregate Data
Create summary tables in your data model for matrix visuals instead of letting the visual aggregate from detail-level fact tables. A pre-aggregated table with 5,000 rows renders instantly; the same aggregation over 50 million rows takes seconds.
Use Import Mode
Import mode stores data in the Power BI engine (VertiPaq) which is optimized for analytical queries. DirectQuery matrices query the source database for every interaction — adding network latency and database load to every click.
Simplify DAX
Avoid nested CALCULATE, complex FILTER, and row-by-row iteration in measures used in matrix Values. Each measure evaluates at every intersection — a 50ms measure in a 3,600-cell matrix takes 3 minutes. Simplify to sub-1ms evaluation.
Collapse by Default
Set the initial expand level to show only the top hierarchy level. Collapsed rows do not generate queries for child data. Users expand only the sections they need, dramatically reducing initial load time.
Enterprise Matrix Visual Best Practices
Design for the Executive Scan
Executives scan matrices top-to-bottom, left-to-right. Place the most important dimension as the first row field, the most critical measure as the first value column, and apply conditional formatting only to the primary KPI. Subtotals should appear at the top of each group so executives see summaries before details.
Use Stepped Layout for 2-3 Level Hierarchies
Stepped layout saves horizontal space and reads naturally. Enable it for matrices with 2-3 hierarchy levels. For 4+ levels, use expand/collapse with stepped layout so the matrix never displays more than 3 visible levels at once.
Apply Conditional Formatting Sparingly
Format 1-2 measures maximum. A matrix where every cell is color-coded becomes visual noise. Use background color scales for the primary metric (revenue, margin, risk score) and leave supporting metrics unformatted. This creates visual hierarchy that guides the eye.
Handle Percentages and Averages at Subtotals
Default SUM subtotals produce incorrect results for percentage and average measures. Use ISINSCOPE() or HASONEVALUE() in DAX to calculate weighted percentages and true averages at subtotal rows. This is the single most common error in enterprise matrix reports.
Combine Matrix with Drill-Through Pages
The matrix provides the summary view. Configure drill-through pages that open when users right-click a row — showing detailed visuals (trend charts, detail tables, maps) for that specific item. This keeps the matrix clean while providing unlimited analytical depth.
Test Performance at Full Data Volume
A matrix that loads in 1 second with test data may take 30 seconds with production data. Always test with the full production dataset and verify load times in the Performance Analyzer. Target under 3 seconds for initial render and under 1 second for expand/collapse interactions.
Use Bookmarks for Preset Views
Create bookmarks for common matrix configurations: fully collapsed (executive summary), expanded to region level (regional review), expanded to detail (operational analysis). Add buttons to the page that toggle between bookmarks, giving users one-click access to their preferred view.
Enable Row-Level Security
Matrix visuals automatically respect row-level security (RLS) filters. When a regional manager views the report, they see only their region in the matrix rows — no additional development required. Define RLS roles in the data model and assign users via the Power BI Service. Test RLS using the "View as" feature in Power BI Desktop.
Related Resources
Power BI Consulting Services
Enterprise Power BI implementation, optimization, and managed services from EPC Group.
Read morePower BI Conditional Formatting Guide
Complete guide to conditional formatting across all Power BI visuals — color scales, rules, data bars, and icons.
Read morePower BI Performance Optimization
DAX optimization, data model tuning, incremental refresh, and Premium capacity management.
Read moreFrequently Asked Questions
How do you use the matrix visual in Power BI?
The matrix visual in Power BI works like a pivot table. Drag dimension fields into the Rows and Columns wells, then drag numeric measures into the Values well. The matrix automatically groups data by row and column intersections, displaying aggregated values at each intersection. You can add multiple fields to Rows for hierarchical grouping (e.g., Region → Country → City), enable subtotals for each level, and use expand/collapse to let users drill into detail on demand. The matrix visual supports up to 100 columns and renders efficiently with proper data model design.
What is the difference between a matrix and a table in Power BI?
A table visual displays flat, row-by-row data — one row per record with no grouping or pivoting. A matrix visual groups data by row and column headers, showing aggregated values at each intersection — similar to a pivot table in Excel. Use a table when you need detail-level data (e.g., individual transactions or line items). Use a matrix when you need cross-tabulated analysis (e.g., revenue by region and product category). The matrix supports hierarchical row grouping, column pivoting, subtotals, and expand/collapse — none of which are available in the table visual.
How do you add conditional formatting to a Power BI matrix?
Select the matrix visual, go to Format > Cell elements, and enable Background color or Font color. Click the fx button to set rules-based or gradient formatting. Options include: 1) Color scale — gradient from low to high values (green to red for risk, red to green for revenue). 2) Rules — specific colors for specific value ranges (e.g., red if below target, green if above). 3) Field value — reference a column that contains color hex codes. 4) Data bars — horizontal bars within cells showing relative magnitude. You can apply different conditional formatting to each measure in the Values well independently.
How do you create subtotals and grand totals in a Power BI matrix?
In the Format pane, navigate to Row subtotals and Column subtotals. Toggle them on to display subtotals for each grouping level. Configure position (top or bottom for row subtotals), per-row-level toggles to show subtotals at specific hierarchy levels only, and custom subtotal labels. Grand totals appear automatically when subtotals are enabled. You can also customize which measures show subtotals — useful when some measures (like percentages or averages) should not be summed. Use HASONEVALUE or ISINSCOPE in DAX measures to display different calculations at subtotal vs. detail rows.
What is stepped layout in a Power BI matrix?
Stepped layout indents child rows under parent rows in a hierarchical matrix, displaying the hierarchy in a single column rather than spreading it across multiple columns. When enabled, Region, Country, and City all appear in one "Location" column with increasing indentation. This saves horizontal space and reads more naturally for hierarchical data. Toggle stepped layout in Format > Row headers > Stepped layout. You can control the indentation size. Stepped layout works best with 2-4 hierarchy levels — beyond that, consider using expand/collapse instead to keep the matrix readable.
How do you optimize Power BI matrix performance with large datasets?
Large matrices (100K+ rows) can be slow. Optimization strategies: 1) Limit visible rows — use Top N filtering to show only the top 50-100 items and let users drill for detail. 2) Reduce columns — fewer measures in Values means fewer calculations per intersection. 3) Use Import mode instead of DirectQuery for matrix visuals. 4) Pre-aggregate in the data model — create summary tables for matrix visuals instead of letting the visual aggregate millions of rows. 5) Avoid complex DAX in matrix values — nested CALCULATE functions evaluated at every intersection compound performance issues. 6) Enable progressive rendering in report settings for large matrices.
Can you expand and collapse rows in a Power BI matrix?
Yes. When the matrix has multiple fields in the Rows well (creating a hierarchy), expand/collapse icons (+/-) appear next to each parent row. Users can click to expand a specific row to see child details or collapse it to see only the summary. You can set the default expansion level in Format > Row headers > set the initial expand level. You can also control expand/collapse behavior programmatically using bookmarks — create a bookmark with the matrix fully expanded and another fully collapsed, then add buttons to toggle between views.
How does cross-highlighting work with matrix visuals?
When a user clicks a cell, row, or column in a matrix, all other visuals on the page update to reflect that selection — this is cross-highlighting. If a user clicks "West Region" in the matrix, a bar chart on the same page highlights the West Region contribution within each bar. You can change this behavior in Format > Edit interactions. Options are: cross-highlight (dim non-selected data), cross-filter (hide non-selected data), or no interaction. For enterprise dashboards, cross-filter typically provides clearer analysis, while cross-highlight preserves context.
How do you use a Power BI matrix as a pivot table?
The Power BI matrix visual replicates Excel pivot table functionality. To create a pivot-style analysis: 1) Drag your row categories into the Rows well (e.g., Product Category, Product Name). 2) Drag your column category into the Columns well (e.g., Quarter or Year). 3) Drag measures into Values (e.g., Revenue, Units Sold). 4) Enable subtotals and grand totals for row and column summations. 5) Add conditional formatting for visual emphasis. The result is a cross-tabulated view identical to an Excel pivot table — but connected to a live data model, refreshed automatically, and secured with row-level security.
Need Enterprise Power BI Dashboard Design?
EPC Group designs and builds Power BI dashboards for Fortune 500 organizations — matrix visuals, conditional formatting, drill-through navigation, and row-level security. 25+ years of Microsoft analytics expertise.