BI-Style Measures with Formulas: Filter Context, Removing Filters, Semi-Additive Balances, Top-N with Others, New vs Returning Customers & Dynamic Segments

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Calculate sales, margin and margin % row by row for ten order lines, then build the total margin % as a measure and see why it differs from the average of the line percentages.
• Turn three slicer cells (region, group, month) into a filter context and build sales, orders and average-order-value measures that answer every combination – including «(All)».
• Calculate three different percentages for the same product row – share of the grand total, share within its region and share of its parent group – and a slicer-driven group share within the selected region.
• Find the last snapshot date of each month with MAXIFS, report the closing and average stock balance per month, and build a quarter closing balance that is not the sum of anything over time.
• Rank ten shops by revenue, break a tie with a second measure, list the top 5 and fold everyone else into an «Others» line so the table still adds up to the total.
• Find each shop's first purchase date with MINIFS, count new and active customers per month without double counting, and report the returning share.
• Assign every shop to a revenue segment with an approximate-match XLOOKUP on a parameter table, count customers and revenue per segment, and test a what-if threshold.
• Build a small KPI page for the current month: sales per region as a base measure, the prior month through a shifted date filter, the change vs prior month, a reconciliation check against the raw data and a column chart.