Bashirat Oyekunle

Problem

Dr. Ingrid Hoffmann at Forggith Pharmaceuticals (simulated pharmaceutical company) needed a brand-aligned two-page Power BI report ready for board presentation. Page 1 required eight KPI cards, a map, a drill-down channel chart, and a product-class breakdown with four slicers. Page 2 required rep- and team-level achievement views, all measures driving the numbers rather than implicit column aggregations.

What I did

Every visual on both pages is connected to the DAX measure suite built in a prior task, so the numbers respond correctly to slicer selections rather than pulling raw column totals. For the channel visual, I built a parent-child drill-down hierarchy so the board can move from channel to sub-channel without switching pages. The Page 2 team matrix places Actual Revenue, Achievement %, and Actual Volume side by side, and the achievement bar chart is sorted ascending so underperforming products appear first, directly answering the board's anticipated question about laggards. Brand colours are applied consistently: burgundy for primary series, dark blue for secondary visuals, and the Forggith logo on both page headers.

Outcome

The board received a self-contained report where any slicer combination surfaces rep- or product-level underperformance without additional data preparation.

Next step

A useful next step would be isolating the bottom-performing sales representatives by team to determine whether underperformance is concentrated in one team or spread evenly across all four.

Problem

Funmi Alade, Head of Customer Experience at FlowRoute Logistics (simulated logistics company), needed to know the size and shape of the complaint problem before a board presentation. The specific question was which single category to lead with and why, based on a count and percentage breakdown across all complaint categories.

What I did

Rather than using a subquery to calculate the total, I used a window function, SUM(COUNT(*)) OVER (), to compute the grand total in the same pass as the per-category counts. ROUND at one decimal place kept the output readable without additional formatting steps. Sorting by complaint count descending put the most common category first, matching the order Funmi would naturally read the results. The key analytical move was looking past the leading category to note that all four categories cluster between 24.7% and 25.6%, which reframes the finding from a single-point failure to a systemic pattern.

Outcome

Funmi received a one-query result with counts, percentages, and a clear analytical verdict, structured so the board communication could distinguish between a surface finding and the deeper operational signal.

SELECT
 Category,
 COUNT(*) AS ComplaintCount,
 SUM(COUNT(*)) OVER () AS TotalComplaints,
 ROUND(
 COUNT(*) * 100.0
 / SUM(COUNT(*)) OVER (),
 1
 ) AS PercentageTotal
FROM logistics.complaints
GROUP BY Category
ORDER BY ComplaintCount DESC;

The window function SUM(COUNT(*)) OVER () computes the grand total within the same GROUP BY pass, avoiding a subquery and making the percentage calculation self-contained in a single query.

Next step

Segmenting complaint counts by delivery region or route would reveal whether the uniform distribution holds everywhere or whether specific corridors drive disproportionate volumes in any single category.

Problem

Chidi Okafor at Meridian Retail (simulated retail company) needed to explain to investors why marketing, operations, and finance each report a different customer count. The task was to replicate all three counting methodologies in Excel, label each result clearly, and produce an investor-facing summary that explains the funnel logic in plain terms.

What I did

Three live formulas in a dedicated Customer Count Validation sheet produce each team's number. COUNTA across the customer ID column, minus the header row, gives the raw marketing total. COUNTIF targets the text string "FALSE" in the is_duplicate column rather than a logical boolean, because the database export encodes booleans as text labels, and using a logical FALSE as the criterion would return zero. COUNTIFS adds a second condition on total_orders greater than zero to isolate finance's active-customer definition. The output table includes a Target Team column, a Validated Count column, and an Investor Explanation column so the story is readable without opening a single formula.

Outcome

Chidi received a single investor-ready sheet where each count is live, labelled, and accompanied by a plain-language explanation of why it differs from the others.

Next step

Examining the 26 zero-order non-duplicate profiles by signup date and channel would show whether they represent a specific acquisition cohort or campaign that consistently fails to convert.

Problem

Funmi Alade at FlowRoute Logistics needed to brief an executive named Emeka on a data quality finding without using technical language. The ask was a sub-150-word email covering three points: what went wrong with the reported failure numbers, the two specific rates before and after correction, and a clear statement on whether the issue was ongoing.

What I did

The email answers Funmi's three numbered questions in the exact order she asked them, so she can read it straight through without reorganising the information. Replacing the technical term ghost attempts with extra delivery retries on old orders keeps the explanation accurate without requiring systems knowledge. Citing the March 15, 2023 cutoff date gives Funmi a concrete piece of evidence to repeat, rather than a vague assurance that the patch held. The final sentence, that the real failure rate is still nearly 10%, was included because stopping at the corrected number would leave Emeka thinking the problem is resolved.

Outcome

Funmi received a self-contained 150-word brief she could relay to the executive verbatim, with the corrected figures, the evidence of closure, and the remaining operational risk all in place.

Next step

Tracking whether the 9.80% true failure rate has moved in the months since March 2023 would determine whether the operational problem is improving, stable, or worsening.

Problem

Dr. Ingrid Hoffmann at Forggith Pharmaceuticals needed ten DAX measures to power both pages of an executive report, with no stored revenue column available in either the sales or targets fact table. The model also required the wide targets table to be restructured before any measures could reference it.

What I did

Because revenue exists nowhere as a column, Total Revenue and Total Target Revenue are both built with SUMX, iterating row by row and pulling price from the products dimension via RELATED. Year-to-date measures use TOTALYTD over the marked date table, and prior-year comparisons use CALCULATE with DATEADD at the year interval rather than hard-coded year filters, so the measures stay slicer-responsive. Revenue MoM % captures current and prior month revenue in named variables before passing them to DIVIDE, which guards against divide-by-zero without a separate IF branch. The wide FactTargets table was unpivoted in Power Query from a format where years were column headers into a long format with a constructed TargetDate column, making it joinable to DimDate on a single key.

Outcome

The measure suite provides the complete computational layer for both report pages, with every KPI card, achievement percentage, and time-comparison metric driven by a named, auditable measure rather than an implicit aggregation.

Total Revenue =
 SUMX(FactSales, FactSales[Quantity] * RELATED(DimProducts[ProductPrice]))

Revenue MoM % =
 VAR CurrentMonthRev = [Total Revenue]
 VAR PreviousMonthRev = CALCULATE([Total Revenue], DATEADD(DimDate[Date], -1, MONTH))
 RETURN DIVIDE(CurrentMonthRev - PreviousMonthRev, PreviousMonthRev)

Achievement % =
 DIVIDE([Revenue YTD], [Target Revenue YTD])

These three measures illustrate the core patterns in the suite: SUMX for row-by-row revenue computation, named variables with DATEADD for month-on-month change, and DIVIDE for safe ratio calculation.

Next step

Adding row-level security roles scoped to individual sales teams would make the same report usable for team managers without exposing cross-team performance data.