Dashboards with Power BI and Looker Studio

A dashboard is a single screen that shows the few numbers a team needs to track, updates automatically when the data changes and lets people filter it themselves. Built well, it replaces dozens of "can you send me the latest numbers?" emails. Built badly, it's a wall of charts nobody opens after the first week. In this lesson you'll design a sales dashboard for the course dataset and build it step by step in the two most common free tools: Microsoft Power BI and Google's Looker Studio.

Dashboard, report or analysis?

These three get mixed up, and the confusion leads to bad dashboards:

• A dashboard monitors: the same KPIs, refreshed regularly, for people who check them often. "How are we doing this month?"
• A report documents: a detailed, often static view for a period, such as a monthly sales report with tables.
• An analysis answers a specific question once: "Why did December spike?" (Lesson 2).

If a question will be asked every week, it belongs on a dashboard. If it's asked once, answer it with an analysis and don't clutter the dashboard with it.

Design before you build

Start on paper. Answer three questions: Who will use it (a VP checking weekly, or an analyst exploring)? What decisions will they make with it? Which 3 to 5 KPIs tell them if things are on track? For our retailer's sales team:

Every element earns its place: the KPI cards answer "how are we doing?", the line answers "is it getting better or worse?", the channel bars answer "where does it come from?" and the filters let each regional manager see their own slice.

Build it in Power BI

Power BI Desktop is a free download for Windows. (On a Mac, use Looker Studio below, or Power BI Desktop through a Windows virtual machine.)

1. Get dataLoad both CSV filesHome → Get data → Text/CSV → choose the clean sales file → Transform Data. In Power Query, confirm the types: order_date as Date, units, unit_price and revenue as Whole Number. Close & Apply. Repeat for the products file.
2. ModelRelate the tablesIn Model view, check the relationship products[product] (one) → sales[product] (many). Power BI often creates it automatically; if not, drag one field onto the other.
3. MeasuresWrite DAX measuresSelect the sales table → New measure, and add the five measures below.
4. VisualsAdd cards, charts and slicersCard visuals for the KPIs, a line chart (order_date by month, Total Revenue), a bar chart (channel, Total Revenue) and slicers for region and channel.
5. SharePublishHome → Publish sends the report to the Power BI service. Sharing with colleagues generally requires a paid license; check what your employer uses.

Total Revenue   = SUM ( sales[revenue] )
Orders          = COUNTROWS ( sales )
Avg Order Value = DIVIDE ( [Total Revenue], [Orders] )
Gross Profit    = SUMX ( sales,
                    sales[units] * ( sales[unit_price] - RELATED ( products[unit_cost] ) ) )
Gross Margin %  = DIVIDE ( [Gross Profit], [Total Revenue] )

With no filters, the cards show $89,735, 200 orders, $448.68, $42,514 and 47.4%. Click "West" in the region slicer and every measure recalculates for that slice: $36,626, 64 orders, $572.28 and a 46.5% margin. That's the power of measures: they're calculated on the fly for whatever filters are active (the "filter context").

Build it in Looker Studio

Looker Studio is Google's free dashboard tool and runs in any browser. It connects natively to Google Sheets, BigQuery and Google Analytics.

1. Go to Looker Studio, create a Blank report and choose Add data. Use the File Upload connector for the CSV, or the Google Sheets connector if you uploaded the workbook to Google Drive (pick the Sales Clean sheet).
2. Check the field types: order_date as Date, revenue as Currency (USD) with Sum aggregation.
3. Insert → Scorecard for revenue. For orders, use Record Count. For average order value, add a calculated field: SUM(revenue) / COUNT_DISTINCT(order_id), which returns $448.68.
4. Insert a Time series chart with order_date (Year Month) as the dimension and revenue as the metric, and a Bar chart with channel as the dimension, sorted by revenue descending.
5. Add a control → Drop-down list for region and another for channel, plus a Date range control. Viewers can now filter every chart on the page.
6. Click Share to invite people by email, just like a Google Doc, or schedule an email delivery of the report.

For gross margin, Looker Studio needs the product costs too: use Blend data to join sales and products on the product field, then add a calculated field for gross profit. Blending works, but it's less flexible than Power BI's data model, so many teams prepare joined data in SQL or BigQuery first.

Dashboard design checklist

• The audience and the decisions it supports are written down.
• 3 to 5 KPIs at the top, each with a comparison (vs. last month, vs. target) when available.
• No more than about 6 visuals on the page; details go to a second page.
• Consistent colors: the same channel has the same color everywhere.
• Filters are in one place, and the page shows which filters are active.
• Totals reconcile with a trusted source (here, $89,735 and 200 orders).
• A "last refreshed" date and a short note on the data source are visible.

Why dashboards go unused

Summary

• Dashboards monitor recurring KPIs; one-off questions belong in an analysis.
• Design first: audience, decisions, 3–5 KPIs, then a Z-shaped layout with KPIs on top.
• In Power BI, load and type the data, relate products to sales, and write DAX measures like DIVIDE([Total Revenue], [Orders]).
• In Looker Studio, connect a CSV or Google Sheet, add scorecards, charts, calculated fields and filter controls, then share like a Google Doc.
• Reconcile totals, keep colors consistent and show the refresh date so people trust and use the dashboard.

Next lesson: Data Storytelling: Presenting Your Findings. You'll turn numbers and charts into a short, persuasive story that leads to a decision.