How to Create a BI Dashboard for a Business Review

To create BI dashboard views for a business review, start with the decisions and validate the measures before choosing charts. Build a small, reconciled scorecard from the exports you already have. Each result should tell an accountable person whether to investigate, act or keep watching.

This is the business preparation workflow. For the broader purpose, see what business intelligence means; for the spreadsheet construction steps, use the existing Excel dashboard guide.

Write the questions before the measures

Suppose a US equipment supplier holds a September 2026 commercial review. The general manager needs to decide whether the revenue gap needs a sales response, whether the company is protecting margin and whether delivery performance needs intervention.

Those questions produce a compact measure list:

Specify exclusions before calculating. Decide how returns affect revenue, which costs belong in gross profit and what counts as an on-time completed order. A partially shipped order cannot be counted as complete just because one line shipped.

Use one business period for the review, while keeping each measure's population explicit. The sales total describes posted transactions; the service rate describes orders completed during the month. They answer different questions and should not be joined row by row just to share a page.

Prepare a source workbook with a clear grain

Retain the original exports. Create a summary worksheet with one row per channel, built from those exports, and retain the identifiers needed to investigate its totals. In this illustration, each completed order belongs to one channel and appears once in the service count.

Place the following summary in A1:E4. Dollar values are exact whole USD, and order counts are whole orders.

The summary should contain one entry for each channel. In H2, check the frequency of the channel in A2, then fill down:

=COUNTIF($A$2:$A$4,A2)

Each example row returns 1. A result above 1 needs review because this summary is intended to have a unique channel key. That rule would not apply to raw invoice lines, where repeated channels are expected. Microsoft documents COUNTIF as counting cells meeting a criterion.

To retrieve Wholesale revenue from the summary for a separate check cell:

=SUMIF($A$2:$A$4,"Wholesale",$B$2:$B$4)

It returns $420,000. This uses Microsoft's SUMIF function to add amounts belonging to a selected category. Covirage is not affiliated with Microsoft.

Compute the measures from amounts and counts

In F2 calculate gross profit and in G2 calculate margin, then fill down through row 4:

F2: =B2-C2
G2: =IF(B2=0,"",F2/B2)

The labels identify destination cells; paste the expression beginning with =. Show percentages to one decimal place and keep unrounded values for later calculations.

The underlying totals are revenue of 420,000 + 180,000 + 100,000 = $700,000; cost of 273,000 + 90,000 + 72,000 = $435,000; completed orders of 300 + 120 + 80 = 500; and on-time orders of 270 + 114 + 76 = 460.

Use these headline formulas:

Revenue: =SUM(B2:B4)
Gross margin: =SUM(F2:F4)/SUM(B2:B4)
On-time rate: =SUM(E2:E4)/SUM(D2:D4)

Revenue less cost is 700,000 - 435,000 = $265,000, which equals the sum of channel gross profit. Margin is 265,000 / 700,000 = 37.9%, rounded. The on-time rate is 460 / 500 = 92.0%. Averaging the channel percentages would give the smaller channels the same weight as Wholesale.

Add targets and prove the comparison

The approved target workbook provides these input values for the same month and business scope: revenue of $720,000, gross margin of 35.0% and on-time rate of 95.0%. Treat those as approved assumptions, not calculated actuals.

On a separate Review sheet, place these measures in rows 2:4, actuals in B and targets in C. For the revenue dollar gap in D2, use =B2-C2; for revenue percentage variance in E2, use =IF(C2=0,"",B2/C2-1). The latter returns -2.8%, rounded. For the rate gaps in D3 and D4, use =(B3-C3)*100 and =(B4-C4)*100, displayed as percentage points.

Reconcile the revenue and cost totals to the approved finance export, and order counts to the completed-order register. Check that each on-time count is no larger than its completed-order count. Keep a visible unresolved-check status when a control does not tie.

Choose a layout the reviewer can use

Place the headline actuals and targets together. Put a channel comparison below them, followed by the exceptions that explain a decision. Show the period, USD unit, measure definitions and export cutoff beside the page.

Here the review should investigate the sales gap and delivery misses while understanding the favorable margin. Strong margin does not cancel late orders. Assign separate follow-ups to sales, operations and finance instead of combining unlike measures into one overall score.

The KPI dashboard template provides a matching layout reference. Once the measures are approved, the Power BI dashboard workflow covers that platform's distinct business context.

Where it goes wrong

• Choosing charts before questions. Attractive views can fill the page without supporting a decision.
• Double-counting at the wrong grain. Order counts cannot be summed from repeated invoice lines without first identifying unique orders.
• Converting unknown values to zero. A missing cost means margin is unverified; it does not mean the sale had no cost.
• Comparing different scopes. A target covering all channels cannot be judged against an actual excluding Direct.
• Using rounded percentages in calculations. Retain the source amounts and full-precision rates, then round only for display.

Validate your dashboard inputs in Covirage

Bring the reporting workbook and its supporting export in a file or Google Sheet. Covirage maps the columns; its tools compute and check the measures, and the external AI model explains the result with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.

See how this supports self-service analytics.