13  Power BI Essentials for Accounting Analytics

Learning Objectives

After completing this chapter, you will be able to:

1. Explain what a reporting tool such as Power BI adds to the spreadsheet and the query, and distinguish Power BI Desktop, the Power BI service, a report, and a dashboard.
2. Load Charles River tables into Power BI Desktop with Power Query, profile their columns over the entire data set, and relate the tables in the model.
3. Build cards, charts, matrices, and slicers, and explain how a visual summarizes a column and how the visuals on a page filter one another.
4. Reconcile a report to control totals before anyone relies on it.
5. Match a visual to the task its reader performs, and recognize and correct visuals that mislead through their axes, their scales, or the period they show.
6. Design report pages that more readers can use, with sufficient contrast, meaning that never rests on color alone, and alternative text.

Chapter slides View slides Download PowerPoint

Opening Scenario

It is spring 2027, and the fiscal 2026 statements are final. You are still the controller’s staff accountant, and the requests that reach you have taken on a pattern. Every month the sales manager asks for revenue by customer segment, the marketing manager asks what the promotions cost, and the product managers ask how their item groups are doing. Each request produces another workbook, built from the same data and copied, filtered, and charted a little differently. Last month two of those workbooks reached the same meeting with different totals for the Furniture discounts, and nobody could say at once which one was right.

The controller wants to stop producing answers one at a time. She asks you for a single report that the managers can open and explore for themselves: revenue, list prices, and promotional discounts, by item group, product type, customer segment, region, and period. “Two things before anyone sees it,” she says. “It has to tie to the numbers we reported, the ones in your Chapter 6 workbook. And it can’t mislead anyone. A chart that makes a flat year look like a crisis is worse than no chart at all.” The adjustments proposed in the Part III case are not in the workbook, so the report starts from the unchanged data and Chapter 6’s control totals. This chapter teaches you to build that report in Microsoft Power BI. You will load the Charles River sales tables, relate them, reconcile them, and build a first report page. Then you will examine three charts that tell the truth in a misleading way and correct them.

13.1 Why a Reporting Tool

Parts II and III gave you two ways to answer an accounting question. A workbook holds an analysis and its evidence together, and a reviewer can trace every number to its formula. A saved query extracts exactly the rows an analysis needs, from tables of any size, and runs again on new data. Both produce an answer to the question that was asked. A workbook’s PivotTables and slicers let its author explore further, but neither is built as a shared report for a reader who wants to ask the next question: the sales manager who sees the Furniture discounts and wants them by region, then by month, then for one customer segment. Each of those questions would return to you as a new request.

A business intelligence tool, which Chapter 1 introduced, closes that gap. It holds the data in a model of related tables, puts interactive visuals on top of the model, and lets the reader change what the visuals show by clicking. Microsoft Power BI is the business intelligence platform this book uses. Power BI Desktop is a free Windows application in which you build a report: you load and prepare the data, relate the tables, define calculations, and lay out the visuals. The Power BI service is the website, at app.powerbi.com, where reports are published, shared, and opened in a browser. A report built in Desktop can be published to the service, where readers explore it without needing Desktop or a copy of the data. Every tutorial in this Part works in Desktop alone; publishing and sharing, which need a Power BI license, are the subject of the appendix on publishing and security.

Power BI uses two words that are easy to confuse. A report is one or more pages of interactive visuals built on one semantic model, the tables, relationships, and calculations that the report reads, which Chapter 1 called a data model. A dashboard, in Power BI’s sense, is a single page in the Power BI service that holds tiles pinned from one or more reports, so that a reader can watch the main figures in one place and click through to the report behind each (Microsoft, 2025b). Dashboards exist only in the service. Everything you build in this Part starts as a report. In everyday speech, many people call any report page with charts a dashboard, the sense in which Chapter 1 and the front matter use the word; this Part uses it only in Power BI’s sense.

Figure 13.1 shows the path from the data to a published report, and which chapters of this Part cover each step. Much of it is familiar. You loaded and prepared data with Power Query in Part II, and Power BI uses the same Power Query, with the same editor, the same applied steps, and the same M formulas. You related tables through their keys in Chapter 3 and merged them in Chapter 5. And the questions a report answers are the descriptive and diagnostic questions of Chapter 6.

Figure 13.1: From the Data to a Published Report.

Table 13.1 maps the Excel features of Part II to their Power BI counterparts. Most of the ideas carry over, and the main change is where they live. In a workbook, you merged tables into one wide table and summarized it with a PivotTable. In Power BI, the tables stay separate, a relationship passes a filter from one table to another where the merge combined their rows, and every visual on a page summarizes the same model.

Table 13.1: Excel features of Part II and their Power BI counterparts

In Excel (Part II)	In Power BI (Part IV)	Chapter
Get Data and the Power Query Editor	Get data and the same Power Query Editor	13
Merging Tables, or XLOOKUP	A relationship, which passes filters between tables kept separate	13, 14
PivotTable	Matrix visual	13
PivotChart	A chart that filters, and is filtered by, the rest of the page	13
Slicer	Slicer, filtering the other visuals on its page through the relationships	13
Calculated field	Measure, written in DAX	14
Documentation worksheet	An About page and a description on every measure	14, 15
Workbook shared as a file	Report published to the Power BI service	Appendix

NoteConnecting the Dots

Table 1.2 in Chapter 1 described Power BI as the tool for interactive reporting to audiences who explore the data themselves, and placed it in the analyze, visualize, and communicate stages of the workflow. That description sets the limits of this Part as well. Power BI is not where the supporting schedules, assumptions, and evidence of an accounting judgment are kept. Those stay with the workbook and the query, and the report documents the definitions it uses. Power BI is where agreed results are shown to the people who use them, in a form they can explore without changing.

13.2 The Power BI Desktop Window

Figure 13.2 shows the window of Power BI Desktop with a report open. The ribbon across the top holds the commands, grouped on tabs as in Excel. A narrow bar down the left side switches between the views of the file. Report view is where you build and arrange visuals on the canvas, the page in the middle of the window, with tabs for the report’s pages below it. Table view shows the rows of the loaded tables, much like a worksheet. Model view draws the tables and the relationships between them. DAX query view, which Chapter 14 uses, is where you write and test calculations. TMDL view shows the model’s definition as text; only one exercise, in the appendix on publishing and security, uses it.

Figure 13.2: The Power BI Desktop Window.

Three panes on the right side of Report view do most of the work. The Data pane lists the tables of the model and their columns. The Visualizations pane holds a gallery of visual types and, for the selected visual, the wells into which you drag columns, such as the axis of a chart and the values it shows, together with the visual’s format settings. The Filters pane holds filters that apply to one visual, to a page, or to every page of the report. Each of them can be collapsed to give the canvas more room. If your window shows separate Build and Format panes instead of the Visualizations pane, a preview feature called on-object interaction is turned on. The tutorials describe the default panes, and you can turn the preview off under File > Options and settings > Options > Preview features.

TipIn Practice

Power BI Desktop runs only on Windows. If you use a Mac, you can follow the Power BI chapters in a Windows environment, such as a campus computer lab or a virtual machine, or in the Power BI service, in a supported browser, by signing in with your school account. In the service you build an equivalent model and report, with some commands in other places. The appendix on Power BI in the browser rebuilds this chapter’s model and its first page there, and lists what changes in the rest of Part IV. Whether the browser is open to you depends on your license and your institution’s settings, so check with your instructor before you rely on it.

13.3 Getting the Data

Power BI reads data from many kinds of sources, including workbooks, text files, databases, and online services. The tutorials in this Part read the same CharlesRiver.xlsx workbook that Part II used, whose worksheets each hold one Charles River table as an Excel Table named Tn_Table, such as T17_SalesInvoiceLine. Get data on the Home tab opens the connector for a source, and for a workbook it opens the Navigator, which lists every worksheet and every Table in the file. You select the Tables a report needs, and Transform Data opens them in the Power Query Editor. There you keep the columns you need, filter the rows, set data types, and add custom columns, as you did in Chapters 4 and 5. Close & Apply runs the queries and loads their results into the model.

Loading works differently from Excel in one respect. Power BI does not place the rows on worksheets. It stores them inside the report file in a compressed form designed for summarizing columns quickly, so a model of hundreds of thousands of rows stays responsive. The queries are kept with the file, and Refresh runs them again against the source, so a report built today can show next month’s data without being rebuilt. The workbook itself is never changed. Like the Part II tutorials, these never edit CharlesRiver.xlsx.

WarningWatch out

The Charles River data is also offered as a CSV package, one file per table, which loads faster than a large workbook. If you use it, check the numbers after loading. A text file stores numbers as characters, and Power Query reads them by the conventions of your computer’s language and region. On a computer set to a region that writes decimals with a comma, an amount written as 688.42 can be read as 68,842 or rejected as an error. The workbook avoids this, because Excel stores numbers as numbers. The locale used for a file is set under the regional settings of Power BI’s Options. The same choice, Home > Get data > Text/CSV, loads a result that you exported from DB Browser in Part III, such as the monthly measure of Chapter 11, as a table like any other; exporting the view again over the same file and refreshing brings the report up to date.

13.3.1 Profiling the Columns

Before you rely on a table, you profile it, the data profile of Chapter 10, which applied Chapter 2’s tests of data quality with queries. The Power Query Editor can profile every column for you. On its View tab, Column quality shows the share of each column’s values that are valid, errors, and empty. Column distribution draws the count of distinct and unique values under each heading, and Column profile shows the statistics and value distribution of the selected column in a pane below the preview.

These profiles come with one trap. By default, the editor profiles only the first 1,000 rows of each query, which keeps it fast (Microsoft, 2025c), and it says so in the status bar at the bottom of the window: “Column profiling based on top 1000 rows”. Clicking that message lets you switch to the entire data set. The difference can change the conclusion entirely. The invoice lines are stored in the order they were billed, so the first 1,000 rows are the lines of the first two months of fiscal 2024, before Charles River ran any promotion. In those rows, PromotionID is empty on every line and Discount holds a single value, zero. Profiled over the entire table, both columns tell a different story: about two thousand lines carry a promotion, and Discount holds four rates. Figure 13.3 shows the two profiles side by side.

Figure 13.3: The Same Column Profiled on the First 1,000 Rows and on the Entire Data Set.

A profile of a sample describes the sample. Before you draw a conclusion from one, switch to the entire data set, and remember that the setting applies to the editor and is easily forgotten. The control is the same one you have used throughout the book: know what a result describes before you rely on it.

WarningWatch out

Two of Power BI Desktop’s default settings work against an analyst who wants to know exactly what the model contains. Auto date/time adds hidden date tables behind the model’s date columns, tables you cannot see or control. Autodetect new relationships creates relationships by guessing from column names after data is loaded, and a guess between two columns that merely share a name produces wrong totals without any warning. Both are turned off in Tutorial 13.1, under File > Options and settings > Options > Current File > Data Load, so that you create the relationships yourself and, in Chapter 14, a single date table that every visual shares.

13.4 Relationships in Brief

In Chapter 5, you merged the invoice, item, and customer details into the invoice lines and analyzed one wide table. Power BI works the other way. The tables stay as they are, and a relationship between two of them records which column of one matches which column of the other. Chapter 3 described these keys: SalesInvoiceLine.ItemID is a foreign key that refers to Item.ItemID, the primary key of the item table. A relationship on that pair lets a visual show the revenue of the invoice lines by the ItemGroup of the item table, without ItemGroup ever being copied onto the lines.

Most relationships in an accounting model are one-to-many, the cardinality that Chapter 3 introduced: one item, many invoice lines. A relationship also has a direction, which is the direction in which a filter travels. By default, a filter flows from the one side to the many side. Selecting Furniture in the Item table keeps only the invoice lines of Furniture items, because each line can look up its item. The filter does not flow back from the lines to the items, a property that will matter in Tutorial 13.2. Figure 13.4 shows the four tables of this chapter in Model view. Power BI marks the one side of each relationship with 1 and the many side with an asterisk, and an arrow on the line shows the direction of the filter.

Figure 13.4: The Sales Tables in Model View.

The workbook’s Tables carry no relationships, and the Charles River database declares no foreign keys either, as Chapter 3 found, so Power BI has none to read from the source, and you define them yourself. You should then test them, as Chapter 3 recommended for any logical key. A line whose ItemID has no match in the item table does not disappear from a visual. Power BI shows its amount against a blank item group, labeled (Blank), so a blank category in a visual is a signal to investigate, not a category to explain away. Some blanks are meaningful, in Chapter 5’s term, such as the empty PromotionID of a line sold without a promotion; others are keys that found no match. Chapter 14 covers relationships in depth, including the settings this chapter leaves at their defaults.

13.5 Guided Tutorial 13.1: Loading, Relating, and Reconciling the Sales Tables

Context and objective. In this tutorial, you will start the report that you will build on throughout the Power BI chapters. You will load four Charles River Tables with Power Query, add the same kinds of custom columns you added in Chapters 5 and 6, and profile the invoice lines over the entire data set. You will then relate the tables in the model and reconcile the result to the control totals of Chapter 6 on a page that stays in the report as its validation page. The workbook is never changed, and the report can be refreshed whenever the data is.

Prerequisites. You need Power BI Desktop, which is free, installed on a Windows computer. Install it from the Microsoft Store or from the Power BI website. The tutorials in this Part use the September 2026 release (version 2.158), and Help > About shows the version you have. Later releases may move a button or rename a setting. You also need CharlesRiver.xlsx, which Power BI reads directly, so you do not need to open it.

Step-by-step instructions.

Step 1. Start the report and turn off two defaults. As this chapter explained, you will create the relationships and the date table yourself.

1. Start Power BI Desktop, and choose Blank report on the start screen.
2. Choose File > Options and settings > Options.
3. Under Current File, select Data Load.
4. Clear Auto date/time and Autodetect new relationships after data is loaded, and click OK.
5. Choose File > Save as, and save the report as Charles River Reports.pbix in the folder that holds CharlesRiver.xlsx.

The two settings now apply to this report. The Global section of the same dialog sets the defaults for every new report, so clearing Auto date/time for new files there saves you this step next time.

Step 2. Choose the Tables in the Navigator.

1. Choose Home > Get data > Excel workbook, select CharlesRiver.xlsx, and click Open.
2. In the Navigator, select the check boxes of four Tables, not the worksheets of the same names: T4_Customer, T16_SalesInvoice, T17_SalesInvoiceLine, and T44_Item.
3. Click Transform Data.

The Navigator lists the worksheets and the Tables of the workbook, as Excel’s did in Tutorial 4.1 (Figure 4.2). The Power Query Editor opens with one query for each Table you selected.

Step 3. Rename the queries, correct two types, and keep the columns the report needs. A query’s name becomes the name of its table in the model.

1. In the Queries pane, rename the queries Customer, SalesInvoice, SalesInvoiceLine, and Item by double-clicking each name and typing the new one.
2. Select SalesInvoiceLine. If the icon on the Discount header is the whole number icon, 123, click it, choose Decimal Number, and click Replace current when Power Query asks whether to replace the current conversion.
3. Set PromotionID to Whole Number in the same way.
4. Select Customer, choose Home > Choose Columns, and keep CustomerID, CustomerName, CustomerSegment, and Region.
5. In SalesInvoice, keep SalesInvoiceID, InvoiceNumber, InvoiceDate, and CustomerID.
6. In SalesInvoiceLine, keep SalesInvoiceLineID, SalesInvoiceID, ItemID, Quantity, BaseListPrice, UnitPrice, Discount, LineTotal, PromotionID, and PricingMethod.
7. In Item, keep ItemID, ItemCode, ItemName, ItemGroup, StandardCost, and ListPrice.

Power Query typed each column by looking at only its first 200 rows, as Chapter 4 explained. Those invoice lines carry no promotion, so Discount may be a whole number, which rounds every rate to 0, and PromotionID gets no type, though it should match the key it will be related to in Chapter 15. Replace current corrects both before any value is rounded. Each query now ends with a Removed Other Columns step in its Applied Steps list.

Step 4. Keep the items Charles River sells, and set the date type. As in Tutorial 4.1, the items with a blank ListPrice are ones Charles River buys, not sells.

1. Select the Item query, click the filter button on the ListPrice column header, clear (null), and click OK.
2. Select the SalesInvoice query and look at the icon on the InvoiceDate column header. If the column shows a time as well as a date, such as 12:00:00 AM, click the icon and choose Date.

The Item preview now holds only items with a list price. Step 11 confirms that no invoice line refers to any of the items you removed.

Step 5. Add the custom columns. The custom columns in Table 13.2 give each invoice its fiscal year, quarter, and month, each invoice line its amount at list price and its discount, and each item its product type, the same columns you calculated in Chapters 5 and 6.

1. Select the query named in the first row of the table, and choose Add Column > Custom Column.
2. Type the name in New column name and the formula in Custom column formula, and click OK.
3. Repeat the two actions for each of the other rows.
4. Set each new column’s data type with the icon at the left of its header: Whole Number for FiscalYear and FiscalQuarter, Text for Period, Month, and ProductType, and Decimal Number for ListAmount and DiscountAmount.

Table 13.2: Custom columns for the Charles River Reports queries

Query	New column name	Custom column formula
SalesInvoice	FiscalYear	Date.Year([InvoiceDate])
SalesInvoice	FiscalQuarter	Date.QuarterOfYear([InvoiceDate])
SalesInvoice	Period	Text.From([FiscalYear]) & "-Q" & Text.From([FiscalQuarter])
SalesInvoice	Month	Date.ToText([InvoiceDate], "yyyy-MM")
SalesInvoiceLine	ListAmount	[Quantity] * [BaseListPrice]
SalesInvoiceLine	DiscountAmount	[Quantity] * [UnitPrice] * [Discount]
Item	ProductType	Text.Middle([ItemCode], 4, 3)

Date.ToText writes the date in the format given, so Month reads like 2026-09, which sorts in calendar order as text. The amounts are left unrounded, as in Chapter 6, so that their totals match the analysis workbook.

Step 6. Profile the invoice lines over the entire data set.

1. Select the SalesInvoiceLine query.
2. On the View tab, select Column quality and Column distribution.
3. Read the bars under the PromotionID and Discount headers.
4. Click Column profiling based on top 1000 rows in the status bar at the bottom of the editor, and choose Column profiling based on entire data set.
5. Read the two columns again.
6. Record the share of PromotionID that is empty and the number of distinct values of Discount, on both bases.

On the first 1,000 rows, PromotionID is empty everywhere and Discount has a single value. On the entire data set, part of PromotionID is filled and Discount has four values, as Figure 13.3 shows. Profiling the whole population takes longer, but it is the profile that describes every row.

Step 7. Load the data.

1. Choose Home > Close & Apply. Loading takes a minute or more, because Power BI reads each Table from a large workbook.
2. Select Table view in the bar on the left, and select SalesInvoiceLine in the Data pane.
3. Read the number of rows in the status bar.

The invoice lines appear in a grid, and the status bar reports the number of rows, the same number of invoice lines that the workbook’s SalesInvoiceLine Table holds. If loading stops with an error, return to the Power Query Editor with Home > Transform data and look for a step that fails.

Step 8. Relate the tables in Model view.

1. Select Model view in the bar on the left.
2. Drag SalesInvoiceID from the SalesInvoiceLine table onto SalesInvoiceID in the SalesInvoice table.
3. Drag ItemID from SalesInvoiceLine onto ItemID in Item.
4. Drag CustomerID from SalesInvoice onto CustomerID in Customer.

Each drag creates a relationship, drawn as a line with 1 at the table that holds the key and an asterisk at the table that refers to it. If a dialog opens instead, check that Cardinality reads Many to one (*:1) and Cross filter direction reads Single, and confirm it. Drag the tables into the arrangement of Figure 13.4, with SalesInvoiceLine in the middle, so that the relationships are easy to check.

Step 9. Set how the year and the amounts are shown. Power BI would otherwise sum the years and show the amounts without separators.

1. Select Table view, select SalesInvoice in the Data pane, and click the FiscalYear column header.
2. On the Column tools tab, set Summarization to Don’t summarize.
3. Do the same for FiscalQuarter.
4. Select SalesInvoiceLine, click the LineTotal header, and on Column tools select the thousands separator button and set the decimal places to 2.
5. Format ListAmount and DiscountAmount in the same way.

FiscalYear and FiscalQuarter now act as categories in every visual, and the three amounts appear as 1,234.56. The keys keep their default, Sum, for now: this chapter’s tutorials only count them, choosing Count or Count (Distinct) in the visual, and Tutorial 14.1 sets the keys that connect its tables to Don’t summarize when it hides them.

Step 10. Reconcile the model to Chapter 6. A report is reconciled before anyone reads it.

1. Select Report view, double-click the page tab at the bottom, and rename the page Validation.
2. Select Table in the Visualizations pane, and drag FiscalYear, SalesInvoiceLineID, LineTotal, ListAmount, and DiscountAmount into its Columns well.
3. Click the arrow beside SalesInvoiceLineID in the well and choose Count.
4. Compare the fiscal 2026 row with the invoice line count and line total of fiscal 2026 in your Chapter 6 analysis workbook.

The fiscal 2026 row should match Chapter 6 to the cent, and its list amount and discounts should match the gross-to-net figures of the Part II case.

Step 11. Check for a blank item group. A blank category would mean that some invoice lines found no item.

1. Add a second table visual with ItemGroup from Item and LineTotal.
2. Look for a row labeled (Blank).
3. Save the report.

Figure 13.5 shows the finished page. The item group table should have five rows and no (Blank) row, which confirms that every invoice line found its item after the filter of Step 4.

Figure 13.5: The Validation Page of the Charles River Reports File.

Checkpoint. Before you build on the model, confirm the following:

• The model holds four tables, Customer, SalesInvoice, SalesInvoiceLine, and Item, joined by three one-to-many relationships whose filters flow toward the invoice lines.
• The profile on the entire data set differs from the profile on the first 1,000 rows, and you recorded both.
• The fiscal 2026 row of the Validation page matches the invoice line count and line total of Chapter 6.
• No item group appears as (Blank), and FiscalYear is shown, not summed.

A model that reproduces the totals you already trust is ready for its first report page. The Validation page stays in the file, so that anyone who opens the report later can see that it was reconciled, and to what.

13.6 Visuals and How They Summarize

A visual is built by choosing its type in the Visualizations pane and dragging columns from the Data pane into its wells. Whatever you drag into a value well, Power BI summarizes. A numeric column is summed by default, so LineTotal in the values of a column chart becomes Sum of LineTotal, totaled for each column of the chart. The summary can be changed in the visual, from the arrow beside the field in its well, to an average, a minimum or maximum, a count, a distinct count, or another statistic. A summary chosen this way is called an implicit measure, because Power BI creates the calculation for you. Each column also has a default summarization, which you set once for the model on the Column tools tab.

Defaults are convenient, and three of them mislead often enough that you should check every visual for them. The first is summing a number that is not a quantity. Chapter 3 warned that account numbers and keys are identifiers, not amounts, yet Power BI will sum a CustomerID or a FiscalYear as readily as an amount. Set the default summarization of every key, code, and year column to Don’t summarize, and they then act as categories. The second is counting the wrong thing. Count on SalesInvoiceID in the invoice lines counts lines, one for each line that has an invoice number, while Count (Distinct) counts invoices. In fiscal 2026 the first is about a third larger than the second. The third is averaging a rate. Average of Discount averages the rate of each line, each line weighted equally whatever its size. The share of the price given up to discounts is a different number: discount dollars divided by the dollars before discount, the quantity times the unit price. For fiscal 2026, the average of the rate understates that share by more than a quarter. A ratio of totals needs a calculation of its own, a measure, which Chapter 14 teaches, just as Chapter 6’s margins needed calculated fields. Table 13.3 lists these defaults and others that can mislead, with what to do about each.

Table 13.3: Power BI defaults that can mislead

Default	What can go wrong	What to do
A numeric column is summed	Keys, codes, and years are added up	Set their summarization to Don’t summarize
Count counts the rows with a value	Invoice lines are counted when invoices are meant	Use Count (Distinct) on the key of the invoices
Average weights every row equally	A rate is averaged line by line	Divide totals with a measure (Chapter 14)
A line chart’s value axis fits the data	Small changes look large	Start the axis at zero, or say that it does not
A combo chart’s line can get an axis of its own	Two scales suggest a relationship	Use one axis, or two charts
Filters pane entries are not on the canvas	Readers cannot see what was excluded	State the filter in the title or on the page

13.6.1 Choosing a Visual

The choice of visual follows the question, as Table 6.1 in Chapter 6 set out, and Power BI offers the same charts under similar names. A card shows a single number, such as revenue for the year, in large type. Clustered column and clustered bar charts compare categories, line charts show change over time, and stacked charts show composition. A table visual lists rows and their values, and a matrix visual is Power BI’s counterpart of a PivotTable, with categories on rows and columns, subtotals, and the ability to expand a level into the one below it. Matrices and tables suit readers who need exact values, and charts suit readers who need to see a pattern, a distinction the section on honest visuals returns to.

A slicer filters the other visuals on its page, as the slicers of Chapter 4 filtered a Table, but only those that its filter reaches through the relationships, as Tutorial 13.2 shows. Power BI offers slicers as a list of values, as a drop-down list, or as a row of buttons, and a slicer on a date column can show a range to select. The reader can see every slicer’s selection, which makes a slicer the right place for a filter the reader is expected to change.

13.6.2 How Visuals Filter One Another

The visuals on a page are connected. Clicking a bar in one chart filters the other visuals on the page to that bar’s category, wherever the relationships carry the filter, and clicking it again removes the filter. By default, a column or bar chart that is filtered this way highlights the selected part of each bar against the faded whole, which shows the part in relation to the total, while a card or a line chart simply shows the filtered value. This is called cross-filtering and cross-highlighting, and Edit interactions on the Format tab sets, for each pair of visuals, whether one filters the other, highlights it, or leaves it alone. A slicer for the fiscal year, for example, should filter every visual on the page, while a chart that compares the years should be left alone by it.

The Filters pane adds filters that the reader does not see on the canvas, at the level of one visual, one page, or every page. They are useful for restrictions that belong to the report rather than to the reader’s question, such as keeping only the sellable items. They are also a way to mislead, because a reader who does not open the pane cannot tell that anything was excluded. A filter that changes the meaning of a number belongs in a slicer the reader can see, or in the title of the visual.

NoteConnecting the Dots

Implicit measures are Power BI’s version of a problem you met with PivotTables in Chapter 6. A PivotTable sums a column by default, and a calculated field is needed to divide one total by another, because the average of ratios is not the ratio of totals. The same reasoning applies to every visual in this Part. Before you read a number in a visual, check which summary produced it and over which rows.

13.7 Guided Tutorial 13.2: Building the Sales and Discounts Page

Context and objective. In this tutorial, you will build the page the controller asked for: what Charles River billed, what it would have billed at list price, and what the promotions gave away, by period, item group, product type, customer segment, and region. You will add cards, a line chart, a bar chart, a matrix, and slicers, explore the page by clicking, and find the place where a filter stops.

Prerequisites. You need Charles River Reports.pbix from Tutorial 13.1, with its four related tables and the Validation page. If you start here, download Charles River Reports as it stands at the end of Tutorial 13.1 from Companion Files: a Power BI project that you point at your copy of CharlesRiver.xlsx, refresh, and save as Charles River Reports.pbix, as that page explains.

Step-by-step instructions.

Step 1. Add the page.

1. Click the plus sign beside the page tabs at the bottom of Report view to add a page.
2. Double-click its tab and rename it Sales and Discounts.
3. Drag the tab to the left of Validation, so that it is the first page.

The report now has two pages, and the new one is empty.

Step 2. Add a slicer for the fiscal year. The year slicer comes first because every other visual on the page depends on it.

1. Select Slicer in the Visualizations pane, and drag FiscalYear from the SalesInvoice table into its Field well.
2. Select Format visual, and under Slicer settings set Style to Tile and turn on Single select.
3. Click 2026 in the slicer, and place it across the top of the page.

The slicer shows the three fiscal years as buttons, with 2026 selected, and every visual you add from now on shows fiscal 2026.

Step 3. Add the cards. A card visual can show several values, one card for each.

1. Select Card, and drag LineTotal, ListAmount, and DiscountAmount from SalesInvoiceLine into its Values well.
2. Drag SalesInvoiceID from the SalesInvoiceLine table, not from SalesInvoice, into Values, and choose Count (Distinct) from the arrow beside it.
3. Double-click each field in the well and rename it for this visual: Revenue, At list price, Discounts, and Invoices.
4. In Format visual, expand Callout values and set Display units to None.

The four cards show fiscal 2026 in full, rather than abbreviated as millions. Revenue, the list amount, and the discounts match the fiscal 2026 row of the Validation page. The invoice card counts invoices, not lines, so it is lower than the page’s count of invoice lines, because an invoice can have several lines. The gap between the list amount and revenue is the price reduction that Chapter 6 analyzed.

Step 4. Chart the discounts by month and by item group.

1. Select Line chart, drag Month from SalesInvoice into X-axis and DiscountAmount into Y-axis, and place the chart below the cards on the left.
2. Select Clustered bar chart, drag ItemGroup from Item into Y-axis and DiscountAmount into X-axis, and place it on the right.
3. In Format visual > General > Title for each chart, type a title that says what it shows: Promotional discounts by month and Promotional discounts by item group.

The line is close to zero through August, then rises sharply in September and October, the months of the Furniture Seasonal Promotion, before falling back. The bar chart shows that almost all of the year’s discounts were on Furniture.

Step 5. Add the matrix of revenue by product type.

1. Select Matrix, drag ItemGroup and then ProductType from Item into Rows, Period from SalesInvoice into Columns, and LineTotal into Values.
2. Click the plus sign beside Furniture in the matrix to show its product types.
3. Place the matrix across the bottom of the page.

The matrix shows each item group’s revenue by quarter, with totals, and the Furniture rows expand into the eight product types of Chapter 5.

Step 6. Add slicers for segment and region.

1. Add a Slicer with CustomerSegment from Customer, and set its Style to Dropdown.
2. Add a second Slicer with Region, also as a drop-down.
3. Place both beside the year slicer.

Figure 13.6 shows the finished page. The page now answers the controller’s question at a glance and lets a manager narrow it to one segment or region.

Figure 13.6: The Sales and Discounts Page.

Step 7. Explore the page by clicking.

1. Click the Furniture bar in the bar chart, and read the cards, the line chart, and the matrix.
2. Click the bar again to clear the selection.
3. Choose Design Trade in the segment slicer, and read the bar chart, then set the slicer back to all segments.

With Furniture selected, the cards show Furniture’s revenue, list amount, discounts, and invoices, and the line follows almost the same path as before, because nearly all the discounts were on Furniture. With Design Trade selected, the discount line peaks in November, the month of the Design Trade Customer Promotion, while the Furniture promotion still shows in September and October.

Step 8. Find where a filter stops. The direction of a relationship decides which tables a filter reaches.

1. Drag SalesInvoiceID from the SalesInvoice table into the card’s Values well, and choose Count (Distinct).
2. Click the Furniture bar again, and compare the two invoice counts.
3. Remove the field you added in this step from the well, and clear the selection.

The count taken from SalesInvoiceLine falls to the invoices that contain a Furniture line. The count taken from SalesInvoice does not change at all. The Furniture selection filters the Item table, and the filter flows from Item to the invoice lines, but not from the invoice lines back to the invoices. A count that ignores a filter on the page looks plausible and is wrong, which is why the card in Step 3 counts invoices from the lines. Chapter 14 explains the direction of filters in full.

Step 9. Save the report. Save Charles River Reports.pbix, which the next tutorial and the chapters after it extend.

Checkpoint. Before you move on, confirm that your page does the following:

• With 2026 selected, the revenue, list amount, and discount cards match the Validation page, and the invoice card shows fewer invoices than the page shows invoice lines.
• The discount line rises sharply in September and October, and the bar chart shows Furniture far ahead of the other groups.
• Selecting Furniture changes the invoice count taken from the invoice lines but not one taken from the SalesInvoice table.

The page already answers part of the controller’s question: the discounts were concentrated in Furniture in the autumn. What it cannot show is what the discounts cost in gross margin percentage, which divides total gross margin by total revenue, because the report has no measure for a ratio of totals yet.

13.8 Honest Visuals

Chapter 6 gave the basic rules for an honest chart: bars that start at zero, sorted categories, titles that say what the chart shows, no decoration, and color that carries meaning only with a label. Those rules apply in Power BI too. This section adds what an interactive report needs: why some visuals are read more accurately than others, and three ways a chart can mislead while every number in it is correct.

13.8.1 Matching the Visual to the Task

People read some visual encodings more accurately than others. Cleveland and McGill (1984) ranked the judgments a reader makes in decoding a graph, from the most accurate to the least. Position along a common scale, as in a bar or column chart whose bars share a baseline, comes first. Length without a common baseline and angle, as in a pie chart, share a lower rank. Area, as in a bubble chart, comes below them, and color shading comes last. They drew the ranking from psychophysics and their own reasoning, and tested its top in experiments in which readers judged what percentage one value was of another: readers judged position along a common scale more accurately than length, and about twice as accurately as angle. The ranking explains why a bar chart is usually better than a pie chart. The same values that a bar chart makes easy to compare become much harder to compare when they are drawn as slices. Figure 13.7 draws the revenue of the five item groups in fiscal 2026 in four of these encodings.

Figure 13.7: The Same Revenue in Four Visual Encodings.

The reader’s task matters as much as the encoding. Vessey (1991) explained the long-standing mixed evidence on graphs against tables with the theory of cognitive fit. Graphs emphasize spatial information, such as trends and relationships, while tables emphasize symbolic information, the exact values. A reader performs best when the representation fits the task: a graph for a task such as seeing whether revenue is trending down, and a table for a task such as finding the discount on one invoice. A report for accountants needs both, often for the same number, and Power BI’s tooltips, which show exact values when the pointer rests on a data point, and its table and matrix visuals are the way to give readers both. Dilla et al. (2010) reviewed the research on interactive visualization of accounting information, which extends these ideas to visuals the reader can filter and drill into. They organized it around the fit between the visualization and the task, the characteristics of the reader, and the effects on the reader’s decisions.

13.8.2 Axes and Scales

Power BI draws the value axis of a column or bar chart from zero, because the length of a bar represents its value. The value axis of a line chart is different. By default it fits the range of the data, so it starts just below the smallest value. That suits a line whose purpose is to show how a value moves within its range, but it also makes a small change look large. Charles River’s monthly revenue stayed within a fairly narrow band through fiscal 2026, as Chapter 7 found before forecasting it. On the default axis, the ordinary ups and downs of that band fill the whole height of the chart and look like volatility. Starting the axis at zero puts them in proportion, as Tutorial 13.3 shows. If a chart needs a narrow axis to make a small change visible, as the bridge of Chapter 6 did, the chart should say so.

13.8.3 Two Axes on One Chart

A combo chart, such as Power BI’s line and clustered column chart, draws columns and a line on the same horizontal axis. When the values of the line and the columns lie in ranges that barely overlap, Power BI gives the line a second value axis on the right (Microsoft, 2025a), which makes it a dual-axis chart. Each axis then fits its own series, so the line and the columns appear to move together, or apart, at whatever relative scale the two axes happen to produce. Charles River’s promotional discounts are a small fraction of its revenue. Drawn on a second axis beside the revenue columns, the discount line of the autumn promotions rises to the height of the columns, and the chart suggests that discounts became comparable to revenue. On one shared axis, the discounts appear at their true size. Few (2008), after using such charts in earlier books, concluded that two scales on one axis invite readers to compare magnitudes that mean nothing, and recommended two charts placed close together, each with its own scale, or, for series over time, expressing each series as a percentage of its value at a common starting point.

13.8.4 Choosing the Period

Every chart over time starts somewhere, and the starting point can carry the message. Charles River began recording sales in January 2024 with a start-up month of about half the usual invoices, the month that Chapter 7 found distorting its trend. Measured from that month, revenue in December 2026 is close to four-fifths higher, a true statement that suggests strong growth. Measured from February 2024, the first ordinary month, the increase is a small fraction of that, and fiscal 2026 as a whole was barely above fiscal 2025. A fair comparison uses complete and comparable periods: whole fiscal years against whole fiscal years, or the same months in each year, and a chart whose first period is unusual should say why it starts there. Choosing the period is also a matter of grain. Monthly revenue moves with the number of invoices billed each month, which a quarterly or yearly view averages out. A reader who needs the trend should be shown a grain at which the trend is visible.

TipIn Practice

Misleading graphs are not only a classroom concern. Beattie and Jones (1992) examined the annual reports of 240 large UK companies and found that companies with good performance were more likely to include graphs of their key financial figures. About 30% of those graphs distorted the figures by more than 5%, more often exaggerating them than understating them, and across all of the graphs the change in the figures was exaggerated by about 11% on average. Selective presentation of this kind, meant to create a favorable impression, is known as impression management. In a later experiment, Beattie and Jones (2002) concluded that a graph should not distort its figures by more than about 10% if readers are to perceive them correctly, and that readers with less financial understanding appear most at risk. A report built for managers deserves the same care as a published annual report, because its readers act on what they see.

13.9 Accessible Report Design

A report has many readers, and some of them read it in ways its author does not. Some cannot tell certain colors apart, some use a screen reader or a keyboard instead of a mouse, and a report is often printed or viewed on a projector that washes out pale colors. The Web Content Accessibility Guidelines of the World Wide Web Consortium (W3C, 2023) are a widely used accessibility standard, and Power BI includes settings that help a report meet it (Microsoft, 2026). Four practices are where an accounting report starts:

• Contrast. Text should stand out from its background with a contrast ratio of at least 4.5 to 1, the guideline’s threshold for ordinary text. Pale gray labels on white and white labels on pale fills fall short.
• Color with a second cue. A meaning carried by color, such as unfavorable variances in red, should also be carried by a label, a sign, a marker, or a position, so that a reader who cannot see the color does not lose it.
• Alternative text. Every visual has an alternative text setting, under General in its format settings, which a screen reader announces. It should say what the visual shows and what it means, not merely its type.
• A consistent theme. A report theme, chosen from the Themes gallery on the Design tab, sets the default colors and fonts of every visual at once, so a report stays consistent as it grows. Some of Power BI’s built-in themes and color palettes are designed for accessibility, but the result should still be checked against the threshold above.

These practices also make a report clearer for every reader. A title that states the finding, a label on the series that matters, and a color used for one purpose only help the reader who sees perfectly as much as the one who does not. The figures in this book are drawn to the same rules: their text meets the 4.5 to 1 threshold against its background, and a color that carries a meaning comes with a label or another cue. The practices do not make a page accessible by themselves. Microsoft’s guidance also sets the tab order in which the keyboard moves through the visuals, and only a test with the keyboard and a screen reader shows whether a page works for the readers who rely on them.

13.10 Guided Tutorial 13.3: Correcting Three Misleading Charts

Context and objective. Before the report goes to the managers, the controller asks you to review the charts a colleague drafted for the next sales meeting. Each one is built from correct numbers, and each one invites a wrong conclusion. In this tutorial, you will rebuild the three charts on a review page, see how each misleads, correct it, and describe the corrected charts for readers who use a screen reader.

Prerequisites. You need Charles River Reports.pbix from Tutorials 13.1 and 13.2. If you start here, download Charles River Reports as it stands at the end of Tutorial 13.2 from Companion Files: a Power BI project that you point at your copy of CharlesRiver.xlsx, refresh, and save as Charles River Reports.pbix, as that page explains.

Step-by-step instructions.

Step 1. Chart fiscal 2026 revenue by month. The first draft chart shows a month-by-month view of the year.

1. Add a page and rename it Review.
2. Add a Line chart with Month from SalesInvoice in X-axis and LineTotal in Y-axis.
3. Drag FiscalYear into the Filters on this visual section of the Filters pane, and select 2026.
4. Read the value axis: the lowest and highest values it shows.

The line swings from the bottom of the chart to the top and back again, as if revenue lurched from month to month. The axis begins just below the weakest month, so the chart’s whole height covers the gap between the weakest and the strongest month, which is a small fraction of a month’s revenue, the narrow band of Chapter 7.

Step 2. Start the axis at zero.

1. With the chart selected, select Format visual, and expand Y-axis and then Range.
2. Type 0 in Minimum.
3. Set the title under General > Title to Monthly revenue, fiscal 2026.

Figure 13.8 shows the chart before and after. From zero, the same line runs almost level across the year, which is a faithful picture of a year in which no month was far from the average.

Figure 13.8: The Same Monthly Revenue on Two Value Axes.

Step 3. Check the starting point of a growth claim. The second draft chart states that revenue grew by almost 80% between January 2024 and December 2026.

1. Add a Table visual with Month and LineTotal.
2. Read the revenue of January 2024, February 2024, and December 2026.
3. Record the increase from January 2024 to December 2026, and from February 2024 to December 2026.

The draft’s claim is arithmetically true, but January 2024 was the company’s first month of recorded sales, with about half the usual number of invoices, the start-up month of Chapter 7. A comparison that starts there measures the growth from a start-up month, not the growth of an established business. Measured from February 2024, the increase is a small fraction of the draft’s.

Step 4. Compare whole years instead.

1. Add a Clustered column chart with FiscalYear in X-axis and LineTotal in Y-axis.
2. Turn on Data labels in Format visual.
3. Record the change from fiscal 2025 to fiscal 2026.

The three columns show growth from fiscal 2024, a year that still includes the start-up month, to fiscal 2025, and almost none from fiscal 2025 to fiscal 2026. A whole fiscal year against a whole fiscal year is the comparison the reader can rely on. Delete the table visual from Step 3 when you have recorded its values.

Step 5. Build the third draft chart: revenue and discounts on two axes.

1. Add a Line and clustered column chart with Month in X-axis, LineTotal in Column y-axis, and DiscountAmount in Line y-axis.
2. Filter the visual to fiscal 2026, as in Step 1.
3. Read the two value axes, on the left and on the right.

Power BI gives the discounts their own axis on the right, which starts at zero and ends just above the largest month’s discount. The discount line rises to the height of the revenue columns in September and October, as if discounts that autumn rivaled revenue, as in the upper half of Figure 13.9.

Step 6. Put both series on one axis.

1. With the chart selected, select Line chart in the Visualizations pane.
2. Check the wells: both LineTotal and DiscountAmount must be in Y-axis. If DiscountAmount is in Secondary y-axis, drag it into Y-axis, below LineTotal.
3. Set the title to Revenue and promotional discounts by month, fiscal 2026.

On one axis, the discounts appear at their true size: a line close to the bottom of the chart, rising a little in the autumn. Even in October, the month of the largest discounts, they were a small fraction of revenue.

Step 7. Give the discounts a chart of their own. A reader who needs to see the pattern of the discounts should see it on its own scale, labeled as such.

1. Copy the line chart with Ctrl+C and paste it with Ctrl+V.
2. In the copy, remove LineTotal from the Y-axis well.
3. Set the copy’s title to Promotional discounts by month, fiscal 2026, and turn on the Y-axis title.
4. Place the copy directly below the chart from Step 6, at the same width.

The lower half of Figure 13.9 shows the corrected pair: revenue and discounts at their true relative size above, and the pattern of the discounts on their own labeled scale below.

Figure 13.9: Revenue and Discounts on Two Axes and Corrected.

Step 8. Describe the charts and check their titles.

1. Select each chart in turn, and under Format visual > General, type alternative text in Alt text that says what the chart shows and what it means, such as Monthly revenue in fiscal 2026 on an axis from zero. Revenue varied little from month to month.
2. Check that every title says what the chart shows and over which period.
3. Save the report.

Each chart now has a description that a screen reader can announce, and every title names its period.

Checkpoint. The review page should now hold the corrections of the three draft charts: four charts. Confirm the following:

• The monthly revenue chart starts at zero, and its title names the period.
• You recorded the growth from January 2024 and from February 2024, and the change from fiscal 2025 to fiscal 2026, and can explain why they differ.
• Revenue and discounts share one axis, and the discounts also have a chart of their own on a labeled scale.
• Every chart has alternative text that states what it shows.

None of the draft charts contained a wrong number. Each misled through a choice its author made by default or by habit: an axis, a starting point, a second scale. Those are the choices a reviewer checks first.

13.11 Looking Ahead

This chapter has introduced Power BI through the report the controller asked for. You learned how Power BI Desktop, the Power BI service, reports, and dashboards fit together, and how the Excel features of Part II carry over. You loaded the Charles River sales tables with Power Query, profiled their columns over the entire data set rather than the first 1,000 rows, and related the tables in the model, defining by hand the relationships that the workbook does not carry. You reconciled the model to the control totals of Chapter 6 before building anything on it. You built cards, charts, a matrix, and slicers, saw how a visual summarizes a column and how the visuals on a page filter one another, recognized the defaults that sum keys, count lines as invoices, and average rates, and chose the summary each visual needs. Finally, you examined how encodings, axes, scales, and periods shape what a reader sees, and made the page easier to use with descriptive titles, alternative text, contrast, and cues that do not rest on color. Most of this work was descriptive analytics, in the terms of Table 1.1, with the first step toward diagnosis that interaction allows: a reader who clicks Furniture sees where the discounts went.

The report still cannot answer the controller’s central question. It shows revenue, list prices, and discounts, all of them sums, but not the gross margin percentage, which divides total gross margin by total revenue, and not the share of the price given up to discounts, which Average of Discount misstates. Its periods come from columns added to the invoice table, so it can show a month beside the same month of the year before but cannot calculate the change between them. Answering those questions requires calculations written for the purpose and a model designed around them. Chapter 14 builds that model, with a date table that every visual shares, and introduces DAX, the language of Power BI’s measures.

13.12 Chapter Summary

A business intelligence tool serves readers who want to ask the next question themselves. Power BI Desktop, a free Windows application, is where a report is built: data is loaded and prepared with Power Query, the tables are related in a semantic model, and interactive visuals are laid out on report pages. The Power BI service is where reports are published, shared, and read in a browser, and where dashboards, single pages of tiles pinned from reports, can be built. Most Excel features of Part II have a Power BI counterpart. A matrix visual plays the part of a PivotTable, a slicer filters the other visuals on its page that the relationships reach, and a relationship passes filters between tables kept separate, where a merge combined their rows.

Power BI reads the Charles River Tables of CharlesRiver.xlsx through Get data and the Navigator, and the Power Query Editor shapes them with the steps of Chapters 4 and 5 before Close & Apply loads them into the model. The editor’s column quality, distribution, and profile describe each column, but by default they describe only the first 1,000 rows. For the invoice lines, those rows predate every promotion, so a profile of the sample suggests that no line ever carried one. Switched to the entire data set, the profile describes every row, although it still cannot show that the rows are complete or correct. Two defaults are turned off at the start of a report: Auto date/time, which adds hidden date tables, and the autodetection of relationships, which guesses keys from column names.

The tables of a model stay separate and are joined by relationships, which you define yourself, because the workbook’s Tables carry none. Most relationships are one-to-many, and a filter flows from the one side to the many side, so a selection on Item reaches the invoice lines but not the invoices. A model is reconciled before it is used. The Validation page of the tutorials reproduces the invoice line count and the line total of Chapter 6 to the cent, and shows no item group as (Blank).

A visual summarizes whatever is placed in its value wells, which creates an implicit measure. Its defaults sum keys, codes, and years, count lines when invoices are meant, and average rates line by line. Years are set to Don’t summarize, the keys are counted in each visual with Count or Count (Distinct) until Chapter 14 changes their defaults, invoices are counted with a distinct count on the lines, and ratios wait for the measures of Chapter 14. Cards, charts, matrices, and slicers make up a report page, and clicking a data point filters or highlights the other visuals on the page. Edit interactions changes that behavior, and a filter that changes the meaning of a number belongs where the reader can see it, not hidden in the Filters pane.

An honest visual matches its encoding to its reader’s task. Position along a common scale is read more accurately than length or angle, which rank above area and shading, and graphs suit questions about patterns while tables suit questions about exact values. Three choices often mislead with correct numbers. A line chart’s axis fitted to the data magnifies small changes, a second value axis on a combo chart invites comparisons between scales that mean nothing, and a comparison that starts at an unusual period, such as Charles River’s start-up month of January 2024, overstates growth. A report designed for accessibility gives text enough contrast, never lets color carry meaning alone, describes every visual in its alternative text, and uses a consistent theme, and a test with the keyboard and a screen reader shows whether it works for the readers who rely on them.

13.13 Key Terms

Alternative text. A short description attached to a visual or image that a screen reader announces in its place. In Power BI it is set under General in a visual’s format settings, and it should state what the visual shows and what it means.

Cognitive fit. The match between the way information is presented and the task its reader performs. Graphs fit tasks that depend on patterns and relationships, and tables fit tasks that depend on exact values. Performance is best when the two match.

Column profiling. Power Query’s summaries of the values in each column, shown as column quality (valid, error, and empty values), column distribution (distinct and unique values), and column profile (statistics and a value distribution). By default they describe only the first 1,000 rows of a query.

Cross-filtering. The way a selection in one visual filters, or highlights, the other visuals on a Power BI report page. Edit interactions sets, for each pair of visuals, whether one filters the other, highlights it, or leaves it alone.

Dashboard. In Power BI, a single page in the Power BI service that holds tiles pinned from one or more reports, so that a reader can watch the main figures in one place. Dashboards cannot be built in Power BI Desktop.

Dual-axis chart. A chart that draws two series against two value scales, one on each side. Because each scale fits its own series, the relative position of the two series reflects the choice of scales, and readers are invited to compare magnitudes that are not comparable.

Implicit measure. A calculation that Power BI creates when a column is placed in the value well of a visual, such as Sum of LineTotal or Count of SalesInvoiceID. Its summary can be changed in the visual, but it cannot divide one total by another.

Impression management. The selective presentation of information to create a favorable impression, for example by graphing only the figures that improved or by drawing a graph that exaggerates a favorable trend.

Matrix visual. A Power BI visual that summarizes values by categories on its rows and columns, with subtotals and levels that expand, much like a PivotTable.

Power BI Desktop. Microsoft’s free Windows application for building Power BI reports: loading and preparing data, relating tables in a semantic model, writing calculations, and laying out visuals.

Power BI service. The website at app.powerbi.com where Power BI reports are published, shared, and read in a browser, and where dashboards are built.

Relationship. In a Power BI model, a link between a column of one table and the matching column of another, such as SalesInvoiceLine.ItemID and Item.ItemID. A relationship has a cardinality and a direction in which filters flow, and it lets a visual combine columns from both tables without merging them.

Report. In Power BI, one or more pages of interactive visuals built on a single semantic model, created in Power BI Desktop or the Power BI service.

Semantic model. The tables, relationships, and calculations that a Power BI report reads. Power BI formerly called it a dataset.

Summarization. The default way Power BI summarizes a column placed in a visual, such as Sum for an amount. A key, code, or year column is set to Don’t summarize so that it acts as a category.

Visual encoding. The way a visual turns a value into something the eye can judge, such as the position of a point, the length of a bar, the angle of a slice, the area of a circle, or a shade of color. Some encodings are judged far more accurately than others.

13.14 Multiple Choice Questions

1. Each month, the sales manager asks for revenue by region, then by month, then for one customer segment, and each question becomes a new request. Which deliverable best fits this reader?

1. A SQL script that the manager runs against the database
2. A Power BI report that the manager can filter and explore
3. A monthly workbook with one worksheet for each question asked
4. A PivotTable pasted as values into the monthly management memo

2. In Power BI’s own terms, what is a dashboard?

1. Any report page that holds more than one chart and a slicer
2. The canvas of Power BI Desktop on which visuals are arranged
3. A report file that has been saved with the .pbix extension
4. A single service page of tiles pinned from reports

3. In the Power Query Editor, Column quality shows that PromotionID is empty on every row of the SalesInvoiceLine query. What should you do before concluding that no invoice line carries a promotion?

1. Remove the column, because it holds no usable information
2. Change the column’s data type from whole number to text
3. Profile the entire data set, not the first 1,000 rows
4. Merge the query with PromotionProgram and expand the promotion name

4. Why does Tutorial 13.1 turn off Autodetect new relationships after data is loaded?

1. A guessed relationship can join the wrong columns and silently give wrong totals
2. Power BI cannot create relationships between Tables that come from an Excel workbook
3. Detected relationships are deleted and created again each time the report is refreshed
4. The option creates many-to-many relationships between every pair of tables

5. A table visual shows one very large number in place of the fiscal years 2024, 2025, and 2026. What corrects it?

1. Formatting FiscalYear with a thousands separator and no decimals
2. Setting the summarization of FiscalYear to Don’t summarize
3. Sorting the table visual by FiscalYear in ascending order
4. Moving FiscalYear from the SalesInvoice table to SalesInvoiceLine

6. In the model of this chapter, Item filters SalesInvoiceLine, and SalesInvoice filters SalesInvoiceLine. A card shows Count (Distinct) of SalesInvoiceID taken from the SalesInvoice table. What happens to the card when Furniture is selected in an item group slicer?

1. It shows only the invoices that contain a Furniture line
2. It shows (Blank), because the two tables are not related
3. It shows an error until the relationship is set to Both
4. It does not change, as the filter stops at the lines

7. For fiscal 2026, Average of Discount on the invoice lines is lower than discount dollars divided by the dollars before discount. Why do the two differ?

1. The average weights every line equally, whatever its size
2. Power BI rounds each rate to two decimals before averaging
3. Lines without a promotion are left out of the average
4. The discount dollars include the freight on each invoice

8. According to Cleveland and McGill (1984), readers judge the relative size of two values most accurately when the values are encoded as which of the following?

1. The areas of two circles in a bubble chart
2. Two shades of one color
3. Positions along a common scale
4. The angles of two slices in a pie chart

9. An auditor needs the discount given on one particular invoice. Under the theory of cognitive fit, which representation suits that task best?

1. A line chart of discounts by month for the year
2. A pie chart of discounts by item group for the year
3. A bar chart of discounts by promotion, sorted by value
4. A table that lists invoices and the discount on each

10. A Power BI line chart of fiscal 2026 monthly revenue swings from the bottom of the chart to the top, although no month was far from the average. What is the most likely reason?

1. Power BI draws line charts on a logarithmic scale by default
2. The value axis fits the data instead of starting at zero
3. The chart sums the months of each quarter before drawing the line
4. Cross-highlighting fades the lower part of the line on the page

11. A combo chart shows fiscal 2026 revenue as columns and promotional discounts as a line, and the discount line climbs as high as the revenue columns in October. Which correction is best?

1. Add data labels to the discount line and color it bright red
2. Sort the months by discount, from the largest to the smallest
3. Use one shared axis, or two charts with labeled scales
4. Replace the columns with a stacked area chart of the two series

12. A draft states that revenue grew by almost 80% from January 2024 to December 2026. What makes the statement misleading?

1. January 2024 was a start-up month with about half the usual invoices
2. December is the largest month of every year because of the holidays
3. The statement leaves out the revenue of the design services lines
4. The growth is calculated from list prices rather than invoiced prices

13. A variance report shows favorable variances in green and unfavorable ones in red. Which change makes it usable by a reader who cannot tell red from green?

1. Using brighter shades of the same red and green
2. Adding a legend that names the two colors used
3. Enlarging the visual so that the colors stand out
4. Adding a sign or label to each variance

14. What did Beattie and Jones (1992) find in the annual reports of large UK companies?

1. Companies with poor results used more graphs, to explain the decline
2. Good performers graphed more, and many graphs exaggerated
3. Almost every graph was drawn to scale, with axes that started at zero
4. Auditors reviewed every graph against the financial statements

15. Which of the following is most likely to mislead the reader of a published report?

1. A Filters pane filter that excludes one segment, mentioned nowhere on the page
2. A slicer for the fiscal year placed at the top of the page, with the current year selected
3. A filter applied by clicking a bar, which the other visuals on the page then reflect
4. A visual title that names the period, the measure, and the unit of the values shown

13.15 Applied Exercises

Work on these exercises in a copy of Charles River Reports.pbix, saved with File > Save as after you finish this chapter’s tutorials, such as Ch13 Exercises.pbix. Build each exercise on its own report page, named after it (such as Ex 13.1), and do not change the queries, relationships, or pages the tutorials created. When an exercise needs a Table the tutorials trimmed or filtered, import it again under the query name the exercise gives. Turn off Auto date/time and the autodetection of relationships in the copy, as in Tutorial 13.1, if your copy does not already have them off.

13.15.1 Financial Accounting Exercises

Exercise 13.1: Revenue Control Totals by Year and Item Group

Table group: Order-to-Cash, with the Item table from Master Data

Scenario. Before the report is shared, the controller wants a second set of control totals beside the Validation page: revenue by item group for each fiscal year, and a check, year by year, that the invoice lines add up to the invoices they belong to.

Requirements.

1. Build a matrix with FiscalYear on rows, ItemGroup on columns, and LineTotal as values, with row and column totals. Compare the fiscal 2026 row, item group by item group, with the revenue by item group you calculated in Chapter 6, and its total with the fiscal 2026 line total of the Validation page.

2. Import T16_SalesInvoice again as a query named InvoiceHeaders, keep SalesInvoiceID, InvoiceDate, SubTotal, FreightAmount, TaxAmount, and GrandTotal, and add a FiscalYear custom column. Before you choose the columns, make sure FreightAmount has the decimal number type, as Discount does in Step 3 of Tutorial 13.1. After loading, set the new FiscalYear to Don’t summarize, as Step 9 of Tutorial 13.1 did for the years of SalesInvoice. Do not relate it to the other tables. Build a table visual of SubTotal by fiscal year from InvoiceHeaders, and compare it with the line totals by fiscal year.

3. Add FreightAmount, TaxAmount, and GrandTotal to the table visual. Explain why the line totals reconcile to SubTotal but not to GrandTotal, and which of the four amounts is revenue from the sale of goods and services.

4. Explain why InvoiceHeaders should not be related to SalesInvoiceLine in this model, given that SalesInvoice already is.

Deliverable. The matrix and the table visual on the Ex 13.1 page, with a short note of the comparisons.

Exercise 13.2: Customer Concentration by Name or by Key

Table group: Order-to-Cash, with the Customer table from Master Data

Scenario. The controller is preparing a note on customer concentration for the audit committee and asks which customers produced the most revenue in fiscal 2026 and what share the largest ten held. Company policy treats any customer above 5% of annual revenue as a concentration to be reported.

Requirements.

1. Build a table visual of CustomerName and LineTotal for fiscal 2026, sorted from the largest, and filtered to the top 10 customers with the Top N filter type in the Filters pane.

2. Build a second table visual with CustomerID, CustomerName, and LineTotal for fiscal 2026, and set CustomerID to Don’t summarize in the visual, as Step 9 of Tutorial 13.1 set the years. Filter it to the top 10 with the Top N filter on CustomerID, not on CustomerName, so that it ranks customers by key. Compare the two lists and explain any difference.

3. Find every customer name that belongs to more than one CustomerID, and state how much revenue each of those customers produced in fiscal 2026.

4. Calculate the share of fiscal 2026 revenue held by the largest customer and by the top ten, and state whether any customer exceeds the policy’s threshold.

5. Explain why the Top N filter should be stated on the page, where the reader can see it, or in the visual’s title, and write the title you would give the corrected visual.

Deliverable. Two table visuals and a half-page note to the controller.

13.15.2 Managerial Accounting Exercises

Exercise 13.3: Promotions in the Report

Table group: Order-to-Cash, with the Item table from Master Data

Scenario. The marketing manager asks for the promotions to appear in the report by name, with what each one gave away, month by month, so that the 2027 calendar can be discussed with the facts on the page.

Requirements.

1. Import T7_PromotionProgram as a query named PromotionProgram, keep PromotionID, PromotionCode, PromotionName, ScopeType, DiscountPct, EffectiveStartDate, and EffectiveEndDate, and relate it to SalesInvoiceLine on PromotionID.

2. Build a matrix with PromotionName on rows, Month on columns, and DiscountAmount and the count of SalesInvoiceLineID as values, for fiscal 2026. Explain the row labeled (Blank): what it holds, and whether it signals a problem.

3. Add EffectiveStartDate and EffectiveEndDate to the rows, and identify, for each promotion with lines in fiscal 2026, the months in which lines were invoiced after the promotion’s end date. Recall what Chapter 5 found about the orders behind those lines.

4. One promotion’s end date is earlier than its start date. Identify it, and explain how its invoice lines can still carry the promotion.

5. Add a second matrix of fiscal 2026 discounts with PromotionName on rows and ItemGroup in place of Month. Then write a short memo to the marketing manager stating what the promotions of fiscal 2026 gave away, when, and on which item groups, and what the report cannot yet show about whether they paid off.

Deliverable. The two matrices on the Ex 13.3 page and a half-page memo.

Exercise 13.4: A Page for the Sales Manager

Table group: Order-to-Cash, with the Customer table from Master Data

Scenario. The sales manager wants a page for the sales team: revenue by customer segment and region, the discounts each segment received, and the ability to look at one region at a time. The manager reads the page in two ways: in meetings, to see at a glance which segments and regions matter most, and on calls with the regional representatives, to quote exact figures.

Requirements.

1. Using the theory of cognitive fit, decide which visual serves each of the manager’s two tasks, and explain your choice in two or three sentences.

2. Build the page for fiscal 2026: a clustered bar chart of revenue by segment, sorted from the largest, a matrix of revenue by segment and region with totals, and a card of the promotional discounts, which shows one segment’s discounts when its bar is selected. Add a slicer for Region that the manager can see.

3. Click each segment’s bar in turn and describe what happens to the matrix and the card. Then use Format > Edit interactions so that the bar chart filters the card but leaves the matrix unchanged, and explain when that behavior is preferable.

4. Explain why the region restriction belongs in a slicer rather than in the Filters on this page section, and name one restriction that would belong in the Filters pane instead.

5. Add a title to each visual that states what it shows and for which period, and alternative text to the bar chart.

Deliverable. The Ex 13.4 page and a short note on your design choices.

13.15.3 Auditing Exercises

Exercise 13.5: Profiling the Sales Tables on the Entire Data Set

Table group: Order-to-Cash, with the Item and Customer tables from Master Data

Scenario. An internal auditor reviewing the report asks you to document a profile of the four source Tables before anyone relies on the report, and to show how much the profile depends on the rows it is based on.

Requirements.

1. Import T17_SalesInvoiceLine, T16_SalesInvoice, T44_Item, and T4_Customer again as queries named Profile_Lines, Profile_Invoices, Profile_Items, and Profile_Customers, with every column. Correct Discount in Profile_Lines and FreightAmount in Profile_Invoices to the decimal number type, as in Step 3 of Tutorial 13.1. Clear Enable load on each, so that they stay in the Power Query Editor and do not enter the model.

2. With Column quality, Column distribution, and Column profile turned on, record for each query, on the top 1,000 rows, the share of empty values in every column that has any, and the number of distinct values of PricingMethod, Discount, and Status.

3. Switch to the entire data set and record the same figures. Identify every column whose profile changes materially. Explain why the first 1,000 rows of the invoice and invoice line Tables are not representative, and why the Item and Customer profiles do not change.

4. For three of the columns with empty values, decide whether the blanks are meaningful blanks (Chapter 5), data that should exist, or a matter for follow-up, and give your reason.

5. Write a one-page profile memo, in the form of Chapter 10’s data profile, that the auditor can file with the report, with the figures of (2) and (3) in a table attached to it.

Deliverable. A one-page profile memo, with the two sets of figures in an attached table.

Exercise 13.6: Reviewing a Draft Report Page

Table group: Order-to-Cash, with the Item and Customer tables from Master Data

Scenario. A colleague has drafted a page for the board. It has a pie chart of fiscal 2026 revenue by item group with five slices and no labels, a card titled “Average discount” showing Average of Discount for fiscal 2026 as a percentage, a line chart of revenue by month from January 2024 to December 2026 on the default axis, a line and clustered column chart of revenue and discounts by month of fiscal 2026, and, on that chart only, a filter in the Filters pane that excludes the Services item group, mentioned nowhere on the page. The controller asks you to review it before it goes out.

Requirements.

1. Rebuild the draft page in your copy of the report, exactly as described, on a page named Ex 13.6 Draft.

2. For each element, write a review note that states what a reader would conclude, why that conclusion is wrong or unsupported, and what the correct figure or picture is. Use the figures in your report to support each note.

3. Build a corrected page, Ex 13.6 Revised, that replaces each element with an honest equivalent. For the average discount, state what the card should show instead and why the report cannot yet calculate it correctly.

4. Apply the accessibility practices of this chapter to the revised page, and list what you changed.

Deliverable. The two pages and a set of review notes, one for each element of the draft.

13.16 Further Reading

Beattie, V., & Jones, M. J. (1992). The use and abuse of graphs in annual reports: Theoretical framework and empirical study. Accounting and Business Research, 22(88), 291–303. https://doi.org/10.1080/00014788.1992.9729446 A study of the graphs in the annual reports of 240 large UK companies. It shows that companies with good results were more likely to graph their key financial figures, and that many graphs distorted those figures in a favorable direction. It is the starting point of a long line of accounting research on graphs as a tool of impression management.

Cleveland, W. S., & McGill, R. (1984). Graphical perception: Theory, experimentation, and application to the development of graphical methods. Journal of the American Statistical Association, 79(387), 531–554. https://doi.org/10.1080/01621459.1984.10478080 The ranking of visual encodings used in this chapter, from position along a common scale, judged most accurately, to shading, judged least, with the experiments that tested its top: readers judged position more accurately than length or angle. It is why a sorted bar chart so often beats a pie chart.

Dilla, W., Janvrin, D. J., & Raschke, R. (2010). Interactive data visualization: New directions for accounting information systems research. Journal of Information Systems, 24(2), 1–37. https://doi.org/10.2308/jis.2010.24.2.1 A review of research on interactive visualization of accounting information, organized around the task, the decision maker, and the effect on decisions. It places tools such as Power BI in the research on how the format of accounting information shapes judgment.

Few, S. (2008, March). Dual-scaled axes in graphs: Are they ever the best solution? Visual Business Intelligence Newsletter. Perceptual Edge. https://www.perceptualedge.com/articles/visual_business_intelligence/dual-scaled_axes.pdf A short essay that works through the uses of a second value axis and rejects each in turn, then shows the alternatives this chapter recommends: separate charts placed close together, and series indexed to a common starting value.

Janvrin, D. J., Raschke, R. L., & Dilla, W. N. (2014). Making sense of complex data using interactive data visualization. Journal of Accounting Education, 32(4), 31–48. https://doi.org/10.1016/j.jaccedu.2014.09.003 A teaching case in which you act as a division controller and build an interactive visualization to support the chief executive’s decisions. It practices the same move as this chapter, from analysis to a report that a decision maker explores.

Vessey, I. (1991). Cognitive fit: A theory-based analysis of the graphs versus tables literature. Decision Sciences, 22(2), 219–240. https://doi.org/10.1111/j.1540-5915.1991.tb00344.x The theory of cognitive fit. It explains the mixed evidence on graphs and tables by the match between the representation and the task: graphs for spatial tasks such as trends, tables for symbolic tasks such as exact values.

13.17 References

Beattie, V., & Jones, M. J. (1992). The use and abuse of graphs in annual reports: Theoretical framework and empirical study. Accounting and Business Research, 22(88), 291–303. https://doi.org/10.1080/00014788.1992.9729446

Beattie, V., & Jones, M. J. (2002). Measurement distortion of graphs in corporate reports: An experimental study. Accounting, Auditing & Accountability Journal, 15(4), 546–564. https://doi.org/10.1108/09513570210440595

Cleveland, W. S., & McGill, R. (1984). Graphical perception: Theory, experimentation, and application to the development of graphical methods. Journal of the American Statistical Association, 79(387), 531–554. https://doi.org/10.1080/01621459.1984.10478080

Dilla, W., Janvrin, D. J., & Raschke, R. (2010). Interactive data visualization: New directions for accounting information systems research. Journal of Information Systems, 24(2), 1–37. https://doi.org/10.2308/jis.2010.24.2.1

Few, S. (2008, March). Dual-scaled axes in graphs: Are they ever the best solution? Visual Business Intelligence Newsletter. Perceptual Edge. https://www.perceptualedge.com/articles/visual_business_intelligence/dual-scaled_axes.pdf

Microsoft. (2025a). Combo chart in Power BI. Microsoft Learn. https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-combo-chart

Microsoft. (2025b). Intro to dashboards for Power BI designers. Microsoft Learn. https://learn.microsoft.com/en-us/power-bi/create-reports/service-dashboards

Microsoft. (2025c). Using the data profiling tools. Microsoft Learn. https://learn.microsoft.com/en-us/power-query/data-profiling-tools

Microsoft. (2026). Design Power BI reports for accessibility. Microsoft Learn. https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-accessibility-creating-reports

Vessey, I. (1991). Cognitive fit: A theory-based analysis of the graphs versus tables literature. Decision Sciences, 22(2), 219–240. https://doi.org/10.1111/j.1540-5915.1991.tb00344.x

World Wide Web Consortium. (2023). Web content accessibility guidelines (WCAG) 2.2. https://www.w3.org/TR/2023/REC-WCAG22-20231005/