CPG KPI computation example
A recent social post walks through hands‑on KPI computation for revenue, COGS and profit — a neat primer for building driver‑based bridges and margin decomposition in CPG contexts. The dataset approach provides actionable formulas you can directly translate into Power BI measures for reporting and root‑cause analysis. (x.com)
The dataset shown in the post translates to concrete DAX measures — for example Revenue = SUM(Sales[Amount]), COGS = SUMX(Sales, Sales[UnitCost]*Sales[Quantity]), GrossProfit = [Revenue] - [COGS], and GrossProfit% = DIVIDE([GrossProfit],[Revenue],0) (slidescope.com)). A driver‑based bridge should split revenue into price × volume less discounts and COGS into unit cost × volume plus freight and yield/mix variances; using a value‑driver tree makes each node quantifiable so a 1% price lift on $10,000,000 increases revenue by $100,000 and — at a 30% gross margin — raises gross profit by $30,000 (inspiredeconomist.com)). Implement variance decomposition in Power BI with SUMX/CALCULATE measures and display the outputs in a waterfall/bridge visual; Microsoft’s KPI visuals guidance and the Power BI “Customer Profitability” sample illustrate surfacing the top drivers on an executive summary page (learn.microsoft.com)). FP&A teams convert decomposition into C‑suite action by translating basis‑point movements into dollars and scenarios — for example breaking a 200‑bp margin decline into +120 bp material inflation and −80 bp mix, then showing the implied EBIT delta — an approach recommended by FP&A and value‑driver frameworks for board decisions (corporatefinanceinstitute.com)). The post’s recommended atomic schema — transactional rows with unit price, unit cost, quantity, discount, freight and plant/SKU identifiers — enables reusable measures, drill‑through SKU‑level margin and inventory KPIs, and mirrors public Power BI P&L projects and GitHub dashboards used for reproducible financial reporting (slidescope.com)).
