Power BI: From Business Data to Useful Dashboards
Power BI for business turns prepared data and a defined measurement model into reports people can use. The most important work happens before choosing colors: define the question, establish the grain, and reconcile the numbers.
Begin with a decision, not a blank canvas
Ask who will use the report, what decision they must make, how often they need it, and what action a result should trigger. For a service business, a useful first question might be: “Which service line is contributing less gross profit this month?” Define revenue, direct cost, reporting period, and excluded transactions before calculating the answer.
Power BI Desktop is commonly used to prepare data, model it, and author reports. The Power BI service supports publishing and collaboration, subject to your organization’s licensing and configuration. A report can have multiple interactive pages; a dashboard in the service is a separate, single-canvas feature. People often use “dashboard” informally for both, so clarify the intended deliverable.
The practical workflow
| Step | Work to perform | Control |
|---|---|---|
| 1. Connect | Import the approved source or connect to it | Record source, extraction date, and scope |
| 2. Prepare | Use Power Query to set types and clean fields | Explain dropped rows and conversion errors |
| 3. Model | Define tables, keys, and relationships | Test uniqueness and unmatched keys |
| 4. Calculate | Write measures with clear definitions | Compare to a small hand-worked example |
| 5. Present | Choose a few visuals tied to decisions | Label units, periods, and active filters |
| 6. Operate | Publish, control access, refresh, and maintain | Assign an owner and test the actual viewer experience |
Get the data model right
Grain means what one row represents. An invoice-line table and an invoice-header table have different grains. Joining a 900 invoice total to its three lines and summing the repeated total produces 2,700, even though the sale is still 900. Establish the grain before writing measures.
A common star model separates a fact table of business events from dimensions used to group and filter them, such as Date, Customer, and Service. A service dimension can contain one row per ServiceID; the fact table can contain many rows for that ID. Check that the “one” side is actually unique and investigate facts whose keys have no matching dimension row. Do not choose many-to-many relationships or bidirectional filtering simply to hide a modeling error.
Worked example: a service profitability report
Use this original three-row example as a fact table named Sales. Assume revenue and direct cost are recorded at the same row grain and in the same currency.
| Line | Service | Revenue | DirectCost |
|---|---|---|---|
| 1 | Bookkeeping | 1,000 | 600 |
| 2 | Bookkeeping | 500 | 400 |
| 3 | Advisory | 1,500 | 600 |
Create these measures in DAX:
Total Revenue = SUM(Sales[Revenue])
Total Direct Cost = SUM(Sales[DirectCost])
Gross Profit = [Total Revenue] - [Total Direct Cost]
Gross Margin % = DIVIDE([Gross Profit], [Total Revenue])Overall revenue is 3,000, direct cost is 1,600, gross profit is 1,400, and gross margin is 46.67%. Bookkeeping contributes 500 gross profit on 1,500 revenue, or 33.33%. Advisory contributes 900 on 1,500, or 60%.
Measures respond to filter context. Selecting Bookkeeping should change both revenue and direct cost to that service’s rows. This is why testing a filtered view matters. DIVIDE returns blank by default for a zero denominator; decide how a no-revenue period should be explained rather than showing a misleading percentage.
Do not average the three row margins: 40%, 20%, and 60% average to 40%, which differs from the correct aggregate 46.67%. The overall margin uses total gross profit divided by total revenue. It is also not net profit because indirect operating costs are excluded from this example.
Design a report people can act on
Start with three totals, a bar chart comparing gross profit by service, and a detail table for investigation. Add a trend only when you have multiple comparable periods and an appropriate date model. A busy page of gauges rarely explains what the user should do.
Show the reporting period and the latest successful refresh. Use consistent units and meaningful chart titles. Check color contrast and avoid relying solely on red versus green. Explain whether an unfavorable result should prompt a pricing review, a cost review, or a data correction.
Validate before sharing
- Reconcile row counts and totals to the source for the same scope and date.
- Inspect blank values, duplicate keys, and unmatched dimension records.
- Test one service, several services, and the unfiltered total.
- Check that visual interactions do not hide an important filter.
- Verify refresh credentials, source availability, and gateway needs where applicable.
- Test access using the intended viewer permissions; a hidden column or page is not a security control.
Publishing is the beginning of maintenance. Name the business owner, define how metric changes are approved, and keep a short explanation of source transformations. A polished report with stale or misunderstood numbers is not ready for decisions.
Frequently asked questions
Do I need to learn DAX before starting?
You can begin by importing a small clean table and building a simple report. Learn basic measures early so you understand how totals and filters affect calculations.
What is the difference between Power Query and DAX?
Power Query prepares and transforms source data. DAX defines calculations in the model, including measures evaluated under the current filters.
Should I average percentages?
Only when that matches the intended definition. Aggregate margins generally require total profit divided by total revenue rather than a simple average of row percentages.
Can everyone see a report after I publish it?
Access depends on workspace permissions, sharing settings, licensing, and applicable security rules. Verify the actual viewer experience before relying on the deployment.
Sources and further learning
Official documentation checked November 2, 2026. Examples and learning sequences on this page are original teaching illustrations. Product interfaces and plan availability can change.
- Microsoft: star schema guidance
- Microsoft: measures in Power BI Desktop
- Microsoft: dashboards for Power BI designers