13

Chapter 13 · BI tools

Power BI and Google Data Studio

Three tools, one job. By this point the theory is fixed and only the buttons change. What Chapter 13 really tests is whether you can take the principles from Modules 1 to 3 and land them in whichever tool your workplace happens to use.

1Google Data Studio, now Looker Studio

A web-based business intelligence tool. No installation, no licence, runs in the browser.

FeatureDetail
EnvironmentCloud, browser only. Needs a Google account
Best forSmall businesses and e-commerce, where data already lives online
StrengthConnects to live online data and stays current
OutputShareable reports, downloadable as PDF
Fig 13.1Connectors, and refresh frequency as a setting

Refresh frequency is configurable, from 15 minutes up to 12 hours. This is Chapter 5's data latency choice, exposed as a setting rather than an architecture decision. Templates exist in a gallery, and starting from one is faster than starting blank.
Preparing the data sourceWhy
Set the data typeText, number, date
Set a geo fieldMarking a state as "Country subdivision" is what enables maps
Set default aggregationSum, average, count
Add calculated fields and parametersMetrics the source does not carry

This is Chapter 11's metadata management under a different name. Theme and layout first: pick a preset theme and page orientation before placing anything, since changing it later moves everything. Then add a title, and add charts one at a time. Copy and paste an existing chart and swap its dimension, which is far faster than building each from scratch and keeps the formatting consistent.

Borders and shadows: common region, doing the work

Fig 13.2The same six visuals, with and without borders

Put a visual border on every element. Without borders, panels overlap visually and the reader cannot tell where one ends. Stated as a best practice in both tools, and it is Chapter 1's common region principle doing the work: things inside one boundary read as one group.

Setting up the Power BI canvas

Fig 13.3Desktop layout and the separate mobile layout view

Set the canvas ratio, usually 16:9, before placing visuals. Power BI has a separate mobile layout view, and executives read dashboards on phones, so check it. Name the page, add a background, and place the title first so you can see the space that is left. Treat the canvas as real estate: every square inch should be earning its place, which is Chapter 5's single-screen rule stated as a discipline.

Quick measures and new measures

Quick measure

Menu-driven

Pick an aggregation and fields, and no formula is written. Ready-made ones worth knowing: average per category, weighted average, time intelligence (rolling average, year-on-year change), and filtered-value comparisons. Time intelligence is the one to reach for with growth rates over a year.

New measure

You write the DAX

DAX is data analysis expressions, Microsoft's formula language. Sales per customer = SUM(Sales[Amount]) / DISTINCTCOUNT(Sales[CustomerID])

MeasureDefinition
Sales per customerSum of sales ÷ distinct count of customer ID
Profit per customerSum of profit ÷ distinct count of customer ID
Profit percentageSum of profit ÷ sum of sales
Quantity per customerSum of quantity ÷ distinct count of customer ID

Distinct count again. A plain count double-counts repeat buyers, so every per-customer metric would be wrong. Same trap as Tableau's COUNTD in Chapter 12.

The aggregation trap, again

Fig 13.4Third week running, same error

Summing a discount column gives a number with no meaning, because discount is a percentage, so change it to average. The lesson states the underlying rule plainly: to build an effective dashboard you must know what the data represents. This is the third week running that the same error appears, which is a fair signal of how often it bites.

The dashboard, live

Fig 13.5Every element in the Power BI build, wired together

Click a tree map block or a slicer row and everything else follows. Then switch on the fixed axis ranges and filter to a small region: the marks fall outside the range you hardcoded and the scatter goes blank. That is a real trap, and it is the one the lesson calls out. Switch off the state card and try to work out what is filtered.

Tree maps as filters

Fig 13.6Same interactivity, more information per unit of space

Tree map is preferred over pie here, since pie charts get hard to read with several categories at once. Their real job is acting as filters: clicking a region filters every other visual. A slicer would do the same filtering, but a slicer shows only the choices while a tree map shows the choices and their relative sizes at the same time. That trade is the interesting design decision of the week.

The scatter plot: where the analysis lives

Everything else on the dashboard is descriptive. The scatter plot carries the argument, and it holds five channels at once.

ChannelVariable
X axisAverage discount
Y axisProfit per customer
SizeSales per customer
Legend colourRegion
Shape or detailProduct category
Fig 13.7Average reference lines on both axes, and what each quadrant means

Set the label decimals and line style so the reference values read clearly. Each quadrant now has a business meaning without any explanation, which is Chapter 2's visual hierarchy applied: the same cloud of points, cut into named segments.

Why a fixed axis range breaks under filtering

Fig 13.8Hardcode the range, then filter

Fix the axis ranges so nothing is cut off while you are building, then set them back to auto once filtering is enabled, otherwise a filtered selection can fall outside your fixed range and disappear. A hardcoded axis range breaks the moment a reader filters, and the chart does not report an error, it just shows an empty plot area.

Small multiples, and removing the axis

Fig 13.9Region on the legend, then region as small multiples

Set the grid to 1 row by 4 columns for four regions. Turn on data labels inside the bars, then remove the x-axis entirely, because the labels carry the numbers so the axis is redundant. Keep the label font small enough to survive when a panel shrinks, and use a background per panel rather than a border to separate them more softly.

Filters, gathered in one place

Filtering touches everything in the build above, so here it is collected. Being able to say plainly what filters do, and why they are what makes a dashboard a dashboard, is worth more than knowing where the buttons are.

Fig 13.10The three scopes, and what each one reaches

The three scopes are the part worth naming, because they are what the question is really testing. Visual level applies to one chart, page level to every visual on that page, and report level to every page in the report.
The four ways a reader actually filters
  1. Slicers, on-canvas controls: a dropdown, a list, a date range. The reader sees the choices without opening any pane
  2. The filter pane on the right, which the author configures and can lock or hide per filter
  3. Cross-filtering by selection, clicking a bar or a region. This is the default and needs no setup
  4. Drill down, moving between levels of a hierarchy such as year to quarter to month
Why filters are what makes it a dashboard
  1. One page serves many readers. A national manager and a state manager read the same dashboard, and the slicer is what makes it theirs
  2. They cut clutter without cutting information. Build three charts and let the reader choose the region, rather than fifteen covering every one
  3. They enable root cause analysis, which is Chapter 5's three-layer idea, without anyone building that path in advance
  4. Always show the current selection. Filtered visuals look identical to unfiltered ones
The two traps, both already met above

A hardcoded axis range breaks the moment a filtered selection falls outside it, and applying filters in a different order can change the apparent conclusion, which is drill-down bias from Chapter 6.

The three tools compared

TableauPower BIData Studio
EnvironmentDesktop, Server, CloudDesktop plus serviceBrowser only
Cost to learn onPublic is freeDesktop is freeFree
StrengthVisual analysis and depthCorporate integration, DAX measuresLive online data, zero setup
Calculated fieldsCalculated fieldsQuick measures and DAXCalculated fields
Small multiplesRows and columns shelvesSmall multiples settingFaceting by dimension
MobileDevice layoutsMobile layout viewResponsive

What carries across every tool

  1. Know what the data represents, and pick the right aggregation from that.
  2. Headline numbers as simple text, top-left.
  3. One consistent colour scheme across every visual.
  4. Border every visual.
  5. Remove redundant axes and legends.
  6. Interactivity, plus a card saying what is selected.
  7. Check it on the device the audience uses.

The principles do not change. Only the menus differ.

Key points

  1. What makes Google Data Studio different from Tableau and Power BI.
  2. Native against partner connectors, and configurable refresh frequency.
  3. Why a geo field type must be set before a map will work.
  4. Why theme and layout are chosen before placing charts.
  5. Why every chart gets a border or shadow.
  6. Canvas ratio and the mobile layout view in Power BI.
  7. Quick measure against new measure, and what DAX is.
  8. The four per-customer measures, and why distinct count is required.
  9. Why summing a discount column is wrong.
  10. The multi-row card, and why headline text belongs at the top.
  11. Conditional formatting as a status indicator.
  12. Consistent colour across visuals, and switching off the redundant legend.
  13. Why a tree map can beat a slicer as a filter.
  14. The five channels on the scatter plot, and what the four quadrants mean.
  15. Why fixed axis ranges break under filtering.
  16. Small multiples, data labels inside bars, and removing the x-axis.
  17. Why a card naming the current selection is needed.
  18. Filters: the three scopes, the four ways a reader filters, and the four reasons they make a dashboard a dashboard.
  19. The three tools compared, and what stays constant across all of them.