BI-Style Measures with Formulas: Filter Context, Removing Filters, Semi-Additive Balances, Top-N with Others, New vs Returning Customers & Dynamic Segments
Think like a business-intelligence report. You will build measures that react to slicer cells, separate measures from row-by-row columns, remove or keep filters for shares of total, handle balances that must not be summed over time, rank with ties and fold the rest into «Others», count new and returning customers, band customers into dynamic segments and branch measures into a KPI page, all with fictional data and every cell checked as you go.
One-time payment · lifetime access · lessons, labs, exam and certificate included · no subscription.
About this course
In this course you build the measures behind a business-intelligence report with plain spreadsheet formulas, every cell checked. You separate row-by-row columns from measures and see why total margin % is not an average of percentages. You turn slicer cells into a filter context with SUMIFS and an «(All)» wildcard, remove or keep filters for shares of total, region and parent group, and report semi-additive stock balances with MAXIFS. You rank with ties, add an «Others» line, count new and returning customers with MINIFS and a distinct count, band customers with an approximate-match XLOOKUP and finish with a KPI page that reconciles to the raw data.
Lessons
- Columns vs Measures: Line Margin vs Total Margin % Pro
- Filter Context: One Measure for Every Slicer Combination Pro
- Removing & Keeping Filters: Share of Total, of Region & of Parent Group Pro
- Semi-Additive Measures: Closing & Average Stock Balances Pro
- Ranking with Ties, a Top 5 & the «Others» Line Pro
- New vs Returning Customers per Month Pro
- Dynamic Customer Segments from a Band Table Pro
- Capstone: Measure Branching & a KPI Page that Reconciles Pro