Yemisi Anthonia

Problem

Before sharing any reports externally, the team at Meridian Retail (simulated e-commerce retailer) needed all Power BI output to reflect the Meridian brand palette. The task was to configure a custom theme with six specific hex values covering primary, secondary, accent, and sentiment colours, and save it under the correct name.

What I did

The theme was configured as a JSON definition named Meridian Retail, with the six required hex values placed in the correct positions within the dataColors array: #E8521A as primary, #1A2E4A as secondary, then #F0A500, #2E7D8C, #4A6741, and #CC2200 for accent and multi-series chart colours. Applying the theme without disturbing existing Power Query transformations or model relationships required care, since any model change can reset visual-level formatting. The existing cents-to-naira conversions, duplicate customer filter, and nine-table relationship structure were all confirmed intact after the theme was applied.

Outcome

Reports built on this file will consistently render in Meridian brand colours across all chart series without any per-visual colour overrides, making the output shareable to external stakeholders.

Next step

A useful check would be to verify how the sentiment colours (good, neutral, bad) render on conditional formatting rules in table visuals, since dataColors controls chart series but sentiment colours may need separate configuration in the theme JSON to propagate automatically.

Problem

Nura Ibrahim at Meridian Retail (simulated e-commerce retailer) had flagged a revenue discrepancy and needed a starting point before any deeper analysis. The first step was to enumerate every order status in the orders table and count how many orders carried each one.

What I did

The query is a straightforward GROUP BY on the status column with COUNT(*) and descending sort, chosen because the output needs to be immediately readable by a non-technical stakeholder scanning for anomalies. Sorting by order_count DESC surfaces the dominant statuses at the top and puts the problematic minority statuses lower, where their counts stand out against the volume of delivered orders. No filters were applied at this stage, since the goal was a complete picture of what statuses exist before any exclusions are decided.

Outcome

The result set gave Nura a confirmed list of statuses to work with and immediately identified the three categories driving the revenue gap, setting up the scope for the follow-on revenue breakdown query.

Next step

A useful follow-on to this query would be to add a SUM of total_cents grouped by the same status column, so the revenue weight of each status category is visible alongside the order count in a single result set.

Problem

The data team at Forggith Pharmaceuticals (simulated pharmaceutical company) needed a working Power BI model before any reporting could begin. The raw source had three structural problems: sales history was split across two sheets, the targets table stored years as columns rather than rows, and there was no date table to support time intelligence.

What I did

To unify the sales history, I used Table.Combine in Power Query, keeping the two source sheets as staging queries and exposing a single factsales table to the model. The wide targets table required Table.UnpivotOtherColumns to produce one row per product, rep, and month, followed by a Date.FromText call with an explicit format string and culture to construct a usable TargetDate column. Rather than a static date range, the DAX CALENDAR() table is anchored dynamically to the minimum and maximum dates across both fact tables using UNION and SELECTCOLUMNS, so it will extend automatically if the source data grows. All nine relationships were set to single-direction filter propagation from dimensions to facts.

Outcome

The completed model gave the next task a validated foundation: all dimension filters propagate correctly to both fact tables, and the date table is marked as the official date table so time intelligence functions can run without ambiguity.

VAR AllDates =
 UNION(
 SELECTCOLUMNS(FactSales, "Date", factsales[MonthYear]),
 SELECTCOLUMNS(Targetsfact, "Date", Targetsfact[TargetDate])
 )
VAR FirstYear = YEAR(MINX(AllDates, [Date]))
VAR LastYear = YEAR(MAXX(AllDates, [Date]))
RETURN
 ADDCOLUMNS(
 CALENDAR(DATE(FirstYear, 1, 1), DATE(LastYear, 12, 31)),
 "Year", YEAR([Date]),
 "Month Number", MONTH([Date]),
 "Month", FORMAT([Date], "MMM"),
 "Quarter", "Q" & FORMAT([Date], "Q"),
 "Year-Month", FORMAT([Date], "yyyy-MM")
 )

This DAX calculated table builds the calendar by inspecting the actual date ranges in both fact tables, so the date spine stays accurate without any hardcoded year boundaries.

Next step

A useful next check would be to verify referential integrity on the ProductID and SalesRepID joins, specifically whether any fact rows have IDs that have no matching dimension record, since deduplication was applied to dimensions but not tested against the fact counts.

Problem

Nura Ibrahim, the CFO at Meridian Retail (simulated e-commerce retailer), had flagged an NGN 18.4 million gap between gross and net revenue for October 2023. The question was which order statuses were responsible and what the exact figures were on both sides of the gap.

What I did

The query computes gross revenue, net revenue, and the gap within a single SELECT using a CASE WHEN expression that excludes cancelled, returned, and refunded orders from the net column, avoiding a subquery. To make the output immediately usable without post-processing, GROUP BY ROLLUP(status) produces the per-status rows and a summary total in one pass. Ordering by GROUPING(status) ASC then gross_revenue DESC pushes the TOTAL row to the bottom while keeping the status rows sorted by size. The status::text cast guards against type-comparison issues on PostgreSQL enum columns.

Outcome

The output gave Nura a single query result showing every status row alongside the summary total, so the gap could be confirmed and the responsible statuses identified without further manipulation.

Next step

A natural follow-on would be to break the NGN 18.4 million gap down by product category or customer segment, to see whether cancellations are concentrated in a particular part of the catalogue rather than distributed evenly.

Problem

With the Forggith Pharmaceuticals data model in place, the next requirement was a full set of DAX measures to power both report pages. The measures needed to cover actual sales, targets, and several time-comparison calculations, all without a stored revenue column in either fact table.

What I did

Because revenue is not a stored column, both Total Revenue and Total Target Revenue use SUMX to iterate row by row and multiply quantity against the related product price from DimProducts via RELATED. The time intelligence measures build on that base: Revenue YTD and Target Revenue YTD use TOTALYTD, Revenue PY YTD nests DATESYTD inside SAMEPERIODLASTYEAR, and Revenue SPLY wraps CALCULATE directly with SAMEPERIODLASTYEAR. For Revenue MoM %, a VAR pattern captures the current and prior month values before calling DIVIDE, which guards the calculation against division by zero in months with no prior data. Achievement % is simply DIVIDE of the two YTD measures.

Outcome

The measure suite gave both report pages working figures for sales performance, target tracking, and period-over-period comparison, all responding correctly to the Year, Quarter, Month, Team, and Product Class slicers.

Revenue MoM % =
VAR CurrentMonthRevenue = [Total Revenue]
VAR PreviousMonthRevenue =
 CALCULATE(
 [Total Revenue],
 DATEADD('date table'[Date], -1, MONTH)
 )
RETURN
 DIVIDE(
 CurrentMonthRevenue - PreviousMonthRevenue,
 PreviousMonthRevenue
 )

The VAR pattern separates the two period values before the division, and DIVIDE returns blank rather than an error when PreviousMonthRevenue is zero or blank, which matters at the start of a data series.

Next step

Testing Revenue MoM % at the Product Class slicer level for the earliest months in the dataset would confirm whether the measure returns blank cleanly or produces misleading results when no prior-month data exists for a given filter context.