Report recipes
The standard sales, finance and operations report layouts and the measures behind them.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Plan the standard sales, finance and operations report pages.
- Choose the measures that each report page needs.
- Build Excel-first versions of the same reports.
- Write prompts that get useful analysis from an AI assistant.
Step plan for "analyze X in Power BI"
- State the question (what decision, which measure, which breakdown, which period).
- Model: fact grain, dimensions, a Calendar, relationships, base measures.
- Build measures, then visuals; validate each visual against the SQL answer.
- Format, add tooltips and drill, test slicers, then publish or share.
Sales analysis report (the standard four-page layout)
- Page 1 Overview: cards (Total Sales, Gross Margin %, Units, Customers), monthly line with prior-year line, bar of sales by category, map or bar by geography, year/channel slicers.
- Page 2 Products: matrix of Category > Subcategory with Sales, Margin %, % of total; Top 10 table; scatter of Price vs Units sized by Margin.
- Page 3 Customers or stores: Top N, ABC classification, new vs returning.
- Page 4 Detail: drillthrough table with order lines. Measures to cover: Total Sales, Total Cost, Gross Margin, Gross Margin %, Units, Average Price, Sales PY, YoY %, Sales YTD, Rolling 12M, % of Total, Rank, Top N.
Finance reports
- Actual versus budget: Actual, Budget (separate fact), Variance, Variance %, Forecast, with conditional formatting (red/green) and a waterfall of variance by category.
- AR / AP aging: add an Age column (days outstanding as of a report date), a bucket column (0-30, 31-60, 61-90, 90+) with sort order, and measures for open balance by bucket:
Open Balance = SUMX(Invoices, Invoices[Amount] - Invoices[Paid]); a stacked bar by bucket. - P&L: Account table with a Report Order and type (Revenue, COGS, Expense); measures as amounts with sign handling (
IF(Accounts[Type] = "Revenue", -[Credit - Debit], [Credit - Debit])); matrix with accounts on rows, months on columns; subtotals with ISINSCOPE. - Cash flow and balances: semi-additive measures with LASTDATE and a running total.
- Customer profitability: sales, cost to serve, margin by customer, Pareto.
Operations and HR
Headcount (semi-additive: closing count per month), turnover % = leavers / average headcount, utilization = billable hours / available hours, backlog, cycle time with DATEDIFF, SLA % within target.
Excel-first versions
- Power Pivot: load tables to the Data Model, measures through Power Pivot > Measures, PivotTable + slicers + timeline; use CUBE formulas for fixed-layout reports.
- Without Power Pivot: Power Query to shape, PivotTable on a table, SUMIFS/XLOOKUP formulas, dynamic arrays (FILTER, SORT, UNIQUE, LET) for what DAX would do (see the Excel practice workbooks on the site).
- Choose Power BI when you need relationships across many tables, time intelligence on big data, interactive visuals, sharing to the web, or row-level security. Choose Excel when the audience lives in spreadsheets and the data is modest.
Prompts that work well with an AI assistant connected through MCP
- "Create a Power BI project for contoso with a time intelligence page: YTD, prior year, YoY %, rolling 12 months."
- "Write DAX for new vs returning customers by month in the classicmodels database."
- "How do I make a measure selector in Power BI and in Power Pivot?"
- "Why does my percent of total show 100% on every row?" (check ALL vs ALLSELECTED, relationship, grain)
- "Generate a sample report on dvdrental with revenue by category, top customers and a rental duration histogram."
Want to see it run?
Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.