Power BI Advanced Series Β· Report Design & Visuals Β· by Raushan Ranjan, MCT
You're not alone in feeling confused about choosing the right visual and understanding where to place columns (fields) in Power BI visuals like X-Axis, Y-Axis, Values, Tooltips, etc. This is a very common hurdle for new Power BI users. This one-page guide will simplify the process, helping you choose the right chart and understand exactly where to drag your data fields.
π Start by asking: What story do you want to tell?
The first step in effective visualization is to define your goal. What insight are you trying to convey?
| What You Want to Show | Use This Visual | X-Axis / Category | Y-Axis / Values | Optional Fields (e.g., Legend, Size, Tooltip) |
|---|---|---|---|---|
| Compare values between different categories (e.g., sales by product, revenue by region) | Bar / Column Chart | `Product`, `Region`, `Department` | `Sales`, `Quantity`, `Revenue` | Legend (e.g., `Year`, `Type` to stack/cluster bars) |
| Show trends or changes over time (e.g., monthly revenue, website visitors over a year) | Line Chart | `Date`, `Month`, `Year` (Time hierarchy) | `Revenue`, `Growth %`, `Visitors` | Legend (e.g., `Product`, `Region` to show multiple trends) |
| Show part-to-whole relationships (percentage share) (e.g., market share by product, revenue breakdown by category) | Pie / Donut Chart | (Goes in Legend) `Product`, `Region`, `Category` | `Sales`, `Quantity`, `Count of Items` | Details (e.g., Tooltip info, `Product ID`) |
| Highlight a single, important summary number (KPI) (e.g., Total Sales, Average Rating) | Card / KPI Visual | β (Not applicable) | `Total Sales`, `Avg Rating`, `Count of Customers` | Target (for KPI visual), Goal, Indicator |
| Compare a measure across two categories (spot concentrations) (e.g., product popularity in cities) | Matrix Table (with Conditional Formatting for Heatmap effect) | Rows: `Region`, `Category1` | Values: `Revenue`, `Count`, `Sales` | Columns: `Product`, `Category2` (for the second dimension) |
| Identify outliers and understand data distribution (e.g., price distribution, salary ranges) | Box Plot (Custom Visual from AppSource) | Category (e.g., `City`, `Department`) | Value to analyze (e.g., `Age`, `Salary`, `Price`) | β |
| Find relationships or correlations between 2 numbers (e.g., age vs. salary, quantity vs. sales) | Scatter Plot | Numeric X (e.g., `Age`, `Quantity`) | Numeric Y (e.g., `Salary`, `Sales`) | Size (e.g., `Profit`), Color (e.g., `Department`), Details (e.g., `Employee ID`) |
| Show performance against a goal or threshold | Gauge / KPI Visual | β | Actual value (e.g., `Revenue`, `Sales YTD`) | Target, Max Value, Trend Axis |
π§ Field Type Quick Rules
Understanding the type of data your column holds helps you know where it generally belongs in a visual.
| Field Type | Usually Goes Into | Example |
|---|---|---|
| Category (Text, Discrete) | X-Axis, Legend, Rows, Columns | `Product`, `City`, `Gender`, `Month Name` |
| Numeric (Aggregatable) | Y-Axis, Values, Size (for Scatter) | `Sales`, `Quantity`, `Revenue`, `Age` (when aggregated) |
| Date/Time (Continuous) | X-Axis (especially for Line Charts), Slicers | `Order Date`, `Join Date`, `Transaction Time` |
| Group/Type (for breakdown) | Legend, Color Saturation (for Heatmaps) | `Product Type`, `Region`, `Customer Segment` |
| Details (Extra info on hover) | Tooltip, Drill Down | `Customer Name`, `Order ID`, `Specific Product Description` |
π‘ Tips to Avoid Confusion
- β If you are primarily comparing categories (e.g., sales across different products), put those categories on the X-axis (for column charts) or the Y-axis (for bar charts).
- β If you are showing a measured value (e.g., total sales, average rating), put it on the Y-axis (for column charts) or the X-axis (for bar charts).
- β If you want to group your data by types (e.g., by year, gender, product type) and see separate series or colors, drag that field to the Legend well.
- β If you have two numbers and want to see if they are related or form clusters, always use a Scatter Plot.
- β For showing percentages or parts of a whole, use a Pie/Donut Chart, but try not to overuse them, especially with many categories.
- β Always check the data type of your fields in Power Query Editor or the Model view before dragging them to visuals. Incorrect data types are a common source of visual errors.
β Example Breakdown: Column Chart
Letβs apply these rules to a common scenario: you want to see the total sales for each product category.
You have:
- `Product Category` (Text/Categorical)
- `Total Sales` (Number/Aggregatable)
| Field | Goes into |
|---|---|
| `Product Category` | X-Axis (because it's the category you're comparing) |
| `Total Sales` | Y-Axis (because it's the numeric value you're measuring) |
Result: You will get a column chart with a bar for each product category, and the height of each bar will represent the total sales for that category.
Quick Knowledge Check
Q1. A stakeholder wants to show the percentage contribution of each product category to total sales. Which single Power BI visual best communicates this without DAX?
Show Answer
A donut chart or pie chart. Both show part-to-whole relationships as proportional segments. Donut charts display the percentage on hover; you can also show the percentage directly in the detail labels. No DAX is needed β the visual automatically calculates each category's share of the total. For more than 7-8 categories, a 100% stacked bar chart is clearer because a donut with many slices becomes hard to read.
Q2. When should you choose a matrix over a table visual in Power BI?
- A) Always β matrices are strictly superior to tables
- B) When you need row and column hierarchies, subtotals, and conditional formatting across both dimensions
- C) When you want a flat list of records with no aggregation
- D) Only when the dataset has more than 10,000 rows
Show Answer
B. Use a matrix when you need a pivot-style layout with row and column hierarchies, drill-down, and cross-tab subtotals (e.g., Sales by Year Γ Region with sub-totals). Use a table for flat, non-aggregated row-level detail (e.g., a transaction log). A table has only Columns and Values; a matrix has Rows, Columns, and Values with hierarchical drill capabilities.
Q3. A report page has a clustered bar chart and a line chart. A user clicks a bar in the bar chart. What happens to the line chart by default?
Show Answer
The line chart is cross-filtered β it updates to show only data for the selected bar's category. Power BI's default cross-filtering means clicking any data point in one visual filters all other visuals on the page to the same context. To change this behaviour: Format pane β Edit interactions β select either Filter (subset), Highlight (dim other data), or None (no interaction) between any two visuals. Cross-filtering is automatic and bidirectional by default.
5 Things to Remember
- Donut/pie for part-to-whole (7 categories max) β proportional segments communicate share of total. Switch to 100% stacked bar for many categories.
- Matrix = pivot table; Table = flat list β matrix supports row/column hierarchies, drill-down, and subtotals. Table is for non-aggregated row-level detail.
- Line chart for trends; bar/column for comparisons β use line for time-series patterns; use bar/column for comparing discrete categories side by side.
- Cross-filtering is on by default β clicking a visual filters all others. Control interaction type (Filter/Highlight/None) per visual pair via Format β Edit interactions.
- Scatter chart for correlation; KPI/Card for single metrics β scatter reveals the relationship between two measures; KPI card communicates progress against a goal with a single glanceable number.
Quick Knowledge Check
Q1. A stakeholder wants to show the percentage contribution of each product category to total sales. Which single Power BI visual best communicates this without DAX?
Show Answer
A donut chart or pie chart. Both show part-to-whole relationships as proportional segments. Donut charts display the percentage on hover; you can also show the percentage directly in the detail labels. No DAX is needed β the visual automatically calculates each category's share of the total. For more than 7-8 categories, a 100% stacked bar chart is clearer because a donut with many slices becomes hard to read.
Q2. When should you choose a matrix over a table visual in Power BI?
- A) Always β matrices are strictly superior to tables
- B) When you need row and column hierarchies, subtotals, and conditional formatting across both dimensions
- C) When you want a flat list of records with no aggregation
- D) Only when the dataset has more than 10,000 rows
Show Answer
B. Use a matrix when you need a pivot-style layout with row and column hierarchies, drill-down, and cross-tab subtotals (e.g., Sales by Year Γ Region with sub-totals). Use a table for flat, non-aggregated row-level detail (e.g., a transaction log). A table has only Columns and Values; a matrix has Rows, Columns, and Values with hierarchical drill capabilities.
Q3. A report page has a clustered bar chart and a line chart. A user clicks a bar in the bar chart. What happens to the line chart by default?
Show Answer
The line chart is cross-filtered β it updates to show only data for the selected bar's category. Power BI's default cross-filtering means clicking any data point in one visual filters all other visuals on the page to the same context. To change this behaviour: Format pane β Edit interactions β select either Filter (subset), Highlight (dim other data), or None (no interaction) between any two visuals. Cross-filtering is automatic and bidirectional by default.
- Donut/pie for part-to-whole (7 categories max) β proportional segments communicate share of total. Switch to 100% stacked bar for many categories.
- Matrix = pivot table; Table = flat list β matrix supports row/column hierarchies, drill-down, and subtotals. Table is for non-aggregated row-level detail.
- Line chart for trends; bar/column for comparisons β use line for time-series patterns; use bar/column for comparing discrete categories side by side.
- Cross-filtering is on by default β clicking a visual filters all others. Control interaction type (Filter/Highlight/None) per visual pair via Format β Edit interactions.
- Scatter chart for correlation; KPI/Card for single metrics β scatter reveals the relationship between two measures; KPI card communicates progress against a goal with a single glanceable number.