Term 8 · Module 3 of 3

Building Visualisations with Power BI

Data Visualisation for Business Decisions

Practise this module in Claude, ChatGPT or Gemini

Set it up free
Mind map

3.1 Getting Started with Power BI

3.1.1 Getting Started with Power BI

Power BI Desktop is Microsoft's free Windows application for creating interactive reports. Its online service supports cloud sharing. In Power BI terminology, a report contains one or more pages of interactive visuals; a dashboard is a single screen assembled by pinning report visuals in the online service. The module builds a report from a fictional US retailer, Superstore, using four years of data (2015–2018).

The example contains about 9,000 order lines and about $2.2 million in sales. Each order line represents one product within an order, so an order ID can repeat. Four source files hold different parts of the story:

File/table Grain and useful fields
Orders/products One row per product in an order; order date, customer ID, product ID, net sales, quantity, profit and discount.
Customer lookup Customer ID, name, segment, city, state and region. Segments are consumer, corporate and home office.
Product lookup Product ID, category, subcategory and product name. Categories are technology, office supplies and furniture.
Shipping Order ID, shipping date and shipping mode.

The business questions are where performance is strong and where it is weak. Because operational data is split across departmental files, an analyst must bring the records together before building useful views.

The workflow links preparation to exploration:

  1. 1Load source tables
  2. 2Clean fields in Power Query
  3. 3Connect tables in the data model
  4. 4Create measures and hierarchies
  5. 5Build and format visuals
  6. 6Assemble interactive report

3.1.2 Loading and Transforming the Data (Part 1)

Power BI's main workspace has three practical panes: Data lists tables and fields, Visualizations selects and formats charts, and the canvas is where visuals are arranged. The left-side views include Report (interactive pages), Table (spreadsheet-like data inspection) and Model (table relationships). DAX and TMDL views are more advanced and are not needed for this workflow.

Use Get Data to connect to sources such as Excel, CSV or SQL Server. The example loads Orders_Products and Shipping worksheets from one workbook, then Customer Lookup and Product Lookup from separate workbooks. A workbook's worksheets appear in the Navigator and can be selected for loading. Numeric fields show a sigma icon; date fields show a calendar. These icons help distinguish fields from measures at a glance.

Inspect data in Table view before charting. The postal code was imported as a number, but postal codes are identifiers and should be text. Country contained only “United States,” so it adds no information in this data set. Product Lookup initially displayed generic column names because its first row contains the true headers. These are data-preparation issues to correct before analysis.

3.1.3 Loading and Transforming the Data (Part 2)

Power Query Editor provides repeatable transformations before data is used in reports. Its applied-steps list records changes such as promoting headers, removing columns, adding custom columns, keeping or deleting rows, and removing duplicates.

For this data, use the first row of Product Lookup as headers; remove the constant Country column from Customer Lookup; and create a capitalized customer-name column using Add Column from Examples. Power Query infers the transformation from sample entries and applies it to the remaining rows. Close & Apply commits the transformations and returns to Power BI; Apply can keep the editor open while more tables are changed.

The data model makes fields from separate tables usable together. Tables connect through a match key: a field whose values identify corresponding records. Product ID links Orders_Products to Product Lookup, and Order ID links Orders_Products to Shipping. Customer ID is named cust_id in Customer Lookup, so that relationship must be created manually by matching it to Customer ID in Orders_Products. The lookup side has one row per key; the order side can have many, so the relationship is one-to-many (or many-to-one when viewed from the order table).

Lookup keys should be unique. If a key occurs multiple times on the lookup side, the model cannot unambiguously map a transaction to one lookup record. Check that the intended columns and relationship cardinality are correct when saving the relationship.

3.1.4 Loading and Transforming the Data (Part 3)

A hierarchy arranges related fields from broad to detailed so readers can investigate patterns at successive levels. Power BI automatically provides a date hierarchy: year → quarter → month → day. A product hierarchy must be defined manually: category → subcategory → product. Adding fields in that order lets users drill from an overview into the contributing products. Hiding the individual hierarchy fields from the field list reduces accidental use; the fields remain available through the hierarchy.

Two kinds of calculated fields serve different purposes:

Field When it is calculated Best used for
Calculated column Once per row and stored in the table, using memory. Row-specific values, filtering, or chart axes. Example: a capitalized customer name.
Measure At query time and recalculated for the current filters, slicers and grouping. Aggregations and context-dependent results such as total sales, profitability or quarterly sales.

A quick choice: if a result must respond to report selections, use a measure. If it is a row value needed as a filter or axis, use a column. Measures use DAX (Data Analysis Expressions). In the example, the measures are:

Total net sales=SUM⁡(Net sales)\text{Total net sales}=\operatorname{SUM}(\text{Net sales})

Total profit=SUM⁡(Profit)\text{Total profit}=\operatorname{SUM}(\text{Profit})

Profitability=Total profitTotal net sales\text{Profitability}=\frac{\text{Total profit}}{\text{Total net sales}}

Total discount=SUM⁡(Discount)\text{Total discount}=\operatorname{SUM}(\text{Discount})

Discount percentage=Total discountTotal net sales+Total discount\text{Discount percentage}=\frac{\text{Total discount}}{\text{Total net sales}+\text{Total discount}}

The discount percentage uses gross sales in the denominator: net sales has already had discount removed. Format profitability and discount percentage as percentages. Measures are reusable, but a measure is not a stored row-level column for filtering.

Key takeaways

  • Start from the grain and contents of each source file; the orders table has one row per product line, not one row per order.
  • Clean types and headers in Power Query, then verify the model's match keys and unique lookup rows.
  • Date hierarchies are automatic; product hierarchies can be created to support drill-down.
  • Use columns for stored row attributes and measures for calculations that respond to filter context.

3.2 Building Visuals in Power BI

3.2.1 Measures and DAX

Save the report as a .pbix file. Existing fields such as profit, quantity, discount and net sales can be aggregated, but measures are needed for derived results and totals that must change with user selections. A measure is evaluated on the fly; a column is calculated for every row and stored.

DAX includes aggregation, counting, division, conditional and date functions. Build measures from reusable building blocks. In this example, create total net sales and total profit with SUM, then profitability with DIVIDE(total profit, total net sales). DIVIDE safely handles a zero denominator rather than raising a division error. Format profitability as a percentage; measure formatting supplies a default that an individual visual can override.

3.2.2 Theme of the Dashboard

A theme sets shared colours, text and visual properties, saving repeated formatting and keeping a report consistent. The example starts with the accessible default palette, then sets sentiment colours (negative red, positive green, neutral amber), general text to 16 points, titles to 18 points, a white visual background, light border with rounded corners and shadow, a 1920 × 1080 page, and a very light grey canvas. The theme is saved as a JSON file for reuse.

A theme does not necessarily control every visual detail. Axis labels, chart titles and axis titles may need per-visual formatting. Check legibility and the design needs of the audience instead of assuming every theme setting will fit every chart.

3.2.3 Building Tables in Power BI

A table is useful when readers need detail by one category. The example lists subcategories alongside total net sales, total profit and profitability. Columns can be sorted, totals can be shown or hidden, and a scrollbar appears when the rows do not fit.

Formatting separates visual-specific settings from general settings such as size, title and border. Table styles provide presets; column-level options control alignment, display units and decimal places. The example displays sales in thousands with zero decimal places, profit in thousands with one decimal place so a small value remains visible, and profitability as a percentage with one decimal place.

Conditional formatting encodes values visually. Icons can flag low, middle and high values; data bars can show positive and negative profit; font colour can mark negative profitability in red. Rules can apply to values, totals, or both. These rules should reflect a meaningful business threshold: a loss rule uses zero, while a business with a minimum acceptable margin could use that threshold instead. Too many simultaneous encodings compete for attention; highlight the column most important to the user. Sparklines can add within-row trends (the module defers their implementation to a later module).

3.2.4 Donut Charts and Interactions in Power BI

Pie and donut charts show part-to-whole contributions when there are only a few categories. The example compares net sales across four regions and three customer segments. A donut can show the overall total in its centre; labels can include category, sales and percentage, avoiding a separate legend lookup. The example finds the West contributes about one-third of sales, while the South is the smallest region; consumer sales are about half, corporate about 30%, and home office about 19%.

Power BI can switch an existing visual type while retaining its fields. Hover tooltips display the values used in the visual. Clicking a slice filters or highlights other visuals by default. Edit interactions lets the report designer choose which visuals respond and whether they filter, highlight or remain unaffected. Control-click can select multiple segments; clicking blank space clears a selection. Interactions should support the user's investigation without creating confusing cross-effects.

3.2.5 Column Charts in Power BI

Column charts compare values across a small number of categories. People compare bar lengths more accurately than pie-slice angles. The example uses category on the X-axis and net sales on the Y-axis; technology has the highest sales, followed by furniture and office supplies, which are close.

Add data labels when exact values matter, especially when a chart will be static in a slide and cannot rely on hover tooltips. If labels make the Y-axis redundant, hide the axis values. Format category labels, bar spacing and the title for readability. Keep the visual's scale visible or provide labels so magnitude is not guessed from bar height alone.

3.2.6 Bar charts in Power BI

A horizontal bar chart is easier to read than a column chart when there are many categories or long names. Reuse the category chart's formatting, swap category for subcategory, then change to horizontal bars. Add profitability to the tooltip so users can inspect both sales and margin without crowding the bars.

Conditional bar colour can identify loss-making subcategories: set bars red when profitability is below zero, and keep the default colour otherwise. Explain the encoding in a subtitle (for example, “Red bars indicate losses”). The example highlights tables, bookcases and supplies as loss-making. Selecting a category in the category chart filters/highlights its subcategories in the bar chart; clear the selection by clicking blank space.

3.2.7 Hierarchy in Charts

A hierarchy allows a single visual to serve several investigative questions. With category → subcategory → product name, one bar chart can begin at category level and drill into a selected bar. This answers “which products contribute to the loss?” without requiring separate charts for every level. In the example, drilling into furniture and then tables shows that most of the displayed table products are loss-making; drilling into technology and then phones shows most are profitable, with one exception.

Power BI provides several drill controls:

Control Result
Drill on a selected data point Go down one level for that specific category; drill back up to its parent.
Drill mode Click a specific bar to move into its next level.
Expand all one level Show the next hierarchy level across all categories.
Expand to the next level Display parent and child groupings together.

Sort by category/subcategory when grouped parent-child comparisons are more useful than sorting all bars by sales. The saved report opens at the level last saved, so leave it at the level most useful to regular users. The chart title can be made generic (such as Product Performance), with a subtitle retaining the meaning of red bars.

3.2.8 Help Tooltip in Charts

Use a subtitle for short guidance that should always be visible, such as the meaning of a colour. A help tooltip is opened from a question-mark icon and can hold longer instructions, drill guidance, data limitations or the last refresh information without cluttering the chart. The example enables the help icon and explains that red bars are losses and that users can drill down or up on bars. A text box is another option for instructions, though it is not built in this unit.

3.2.9 Line Charts in Power BI

A line chart shows change over time. Put date on the X-axis and a measure such as net sales on the Y-axis. The example shows a slight sales dip from 2015 to 2016, followed by growth. The date hierarchy allows a trend to be viewed at yearly, quarterly, monthly or daily detail.

Use Format Painter to copy formatting from an existing chart and maintain consistency. Data labels and a Y-axis are alternatives: if labels show the values clearly, the axis may be unnecessary; otherwise turn labels off and make the axis readable. Smooth interpolation softens the line; linear interpolation connects the points directly. Markers identify the observed time points. Adding category to the legend produces one line per category: the 2015–2016 dip appears to come from technology and office supplies, while furniture grows; technology accelerates after 2016 and becomes the largest category in the displayed trend.

3.2.10 Drill Down Option in Line Charts

Keep one line chart at year level for a stable category comparison, and use another when viewers need to investigate timing. With a date hierarchy, drilling into a selected year shows its quarters; drilling again into a selected quarter shows months, and then days. The forked expand control can show quarters within years together, which is more useful for that question than showing all Q1 values across all years. Daily detail can become too dense.

A copied format can carry over conditional colours and help text as well as fonts. Review and edit inherited elements: in the example, points with negative profitability appear as red dots, so the help text must say “dots” rather than “bars.” Dynamic subtitles can reflect the current hierarchy level. When shrinking charts, Power BI's responsive sizing may shrink labels; turn off responsiveness when fixed-size labels are needed.

3.2.11 KPI Cards in Power BI

KPI cards put a few essential values where the eye lands first, commonly near the top or left of a report. The modern multi-card visual displays total net sales, total profit and profitability together. Its layout can be tiles or table, horizontal or vertical, with adjustable gaps. Individual cards can be formatted separately or as a group; card backgrounds, borders, accent bars, shadows, call-out value size and labels are configurable.

Conditional formatting can colour profitability red when it falls below zero. The example reports approximately 2.2millionnetsales,2.2 million net sales, 273,000 profit and 12.4% average profitability. Cards communicate the headline; trends and breakdowns explain what is driving it.

3.2.12 Slicers in Power BI

A slicer is a visible, user-operated filter on a report page. Region slicers let a regional manager focus the report; a date-range slicer lets users choose a period. Slicers affect other visuals on the page by default, but Edit Interactions can disable their effect on selected visuals.

A region slicer can use a vertical list, tiles or a compact dropdown; multi-select and Select All are configurable. A date slicer can use a between range, before/after date, relative date or dropdown. Give slicers clear headers such as “Select Region” and “Select Date Range,” and use background/border styling where needed for separation. Power BI may shrink slicer text responsively; turn off responsiveness if the labels become difficult to read.

3.2.13 Scatter Plots in Power BI

A scatter plot examines whether two numeric measures move together. In the example, each bubble represents a subcategory; X is net sales and Y is profitability. Colour represents product category, and bubble size represents total discount. A tooltip identifies the subcategory; category labels can show those names directly.

The overall sales–profitability relationship looks unclear: profitability varies at similar sales levels. Colour segmentation adds context: office supplies cluster at lower sales and are often profitable; furniture includes several loss-making subcategories; technology ranges from mid to high sales with mixed profitability. Machines has high discounting and low profitability; phones have high sales without similarly large discounts; copiers remain highly profitable despite moderate discounting. A zero reference line separates losses from profits; a target line can be placed at another threshold. Shading below zero makes the loss region visible, but transparency must preserve the bubble colours.

Interpret bubble size carefully: total discount amount tends to be larger when sales are larger. The speaker notes that discount percentage may be a better comparison measure; this is proposed for later work, not calculated in this module.

Key takeaways

  • Match chart form to question: bars compare categories, lines show time, donuts show a few part-to-whole splits, and scatter plots examine relationships.
  • A hierarchy turns a high-level chart into an investigation path from category to product.
  • Make scales, labels, conditional colours and help text explicit so the visual works without guesswork.
  • Slicers and visual selections are user controls; designers decide which visuals respond.
  • Use themes and Format Painter for consistency, then inspect copied settings and per-visual details.

3.3 Assembling the Report in Power BI

3.3.1 Assembling the Report in Power BI

A report page assembles previously built visuals into a coherent view. Use View → Gridlines and Snap to Grid to align items. Add a text-box heading (“Performance Overview”), then copy in KPI cards, the three trend charts, and the product performance bar chart. Resize and position each visual for the available space. Reusing completed visuals preserves their fields and formatting and is faster than rebuilding them.

Add region, date-range and segment slicers. When pasting a slicer, syncing it makes the same selection persist across pages; this can be changed later in the Sync Slicers pane. Align and distribute related visuals, and keep trends close to the KPI each one explains. Page composition is a design decision guided by the users and available space.

3.3.2 Assembling the Report in Power BI (Part 2)

Users explore a report with slicers, selections on visuals and drill-down. Designers also have controls that shape or constrain those choices:

  • Filters pane: apply filters to a visual, a page or all pages. A page filter can produce a region-specific report; a report-level filter applies across the report. Filters can be locked or hidden when the author needs to prevent users from changing them.
  • Sync slicers: choose which pages share a slicer's selection and where the slicer is visible. Syncing preserves a selection across pages; visibility and syncing are independently controlled.
  • Selection pane: rename, show or hide visuals and other objects. Descriptive names make complex pages easier to manage.
  • Group and align: select related visuals and group them so they move together; ungroup when independent editing is needed. Use alignment tools to keep rows and columns tidy.

The example groups the KPI cards with their trend charts, names slicers and visuals, and saves the completed report. Together, filters and slicers set the scope, interactions support exploration, and the selection/grouping tools help authors maintain the page.

3.3.3 Additional Charts in Power BI

A matrix is a cross-tabulation: it places one dimension in rows, another in columns and a measure in the cells, with totals. The example puts category hierarchy in rows, region in columns and profitability in values. Furniture is about 2.33% profitable overall; Technology is about 17.66% overall, but its South result is only 6.27%. Drilling into furniture shows bookcases and tables with losses. Conditional font colour can mark negative profitability red, and formatting can be extended from values to totals.

A combo chart combines columns and a line when two measures have different scales. The example shows net sales as columns and profitability as a line over time, using primary and secondary Y-axes. In Q4 2018, sales are high while profitability dips, raising a question about whether discounting may have supported the sales. Label both axes clearly; the distinct scales can otherwise mislead.

A waterfall chart explains how components move a total between periods. For 2015–2016, furniture rose but office supplies and technology fell, so overall net sales declined slightly. All three categories rose in 2016–2017 and again in 2017–2018; technology's increase was notable in the former interval, while office supplies contributed more of the latter increase and furniture grew little. Check the axis: the displayed scale begins at $400,000, so apparent bar size should be interpreted against that truncated baseline.

A stacked area chart shows total magnitude and category mix changing over time. With segment as the legend, consumers remain the largest segment, while home office grows somewhat in recent years. Because a stacked area mixes changes in total size and composition, a 100% stacked area chart can isolate composition: the example shows consumer share coming down, corporate taking some of that share, and home office remaining reasonably stable.

Key takeaways

  • Assemble the page around a clear headline, headline KPIs, supporting trends, breakdowns and user controls.
  • Distinguish author controls (filters, selection, grouping and sync settings) from ways users explore the report.
  • Matrix compares two dimensions; combo charts pair different scales; waterfall charts explain change; area charts show changing mix.
  • Verify axis scales and label both axes when a chart combines measures with different units.
Revise before your examAll 1 exam tip from this module in one list
  1. A percentage measure is still numeric. Set a rule against the numeric threshold (for example, zero for losses), even though its display is formatted as a percentage.

Test yourself on this module

Questions from these notes, one at a time, with feedback. Your weak topics are saved.

Set it up free Takes about a minute. Free plans work.