<!-- https://learnsql.cpetoday.com/analyze/report-recipes -->
# Power BI and Excel analysis recipes (how to ask for, and build, common reports)

## Step plan for "analyze X in Power BI"
1. State the question (what decision, which measure, which breakdown, which period).
2. Pick the database and tables (describe_database, describe_table), verify keys and join paths, and compute the answer in SQL first (run_sql) so you know the right number.
3. Model: fact grain, dimensions, a Calendar, relationships, base measures.
4. Build measures, then visuals; validate each visual against the SQL answer.
5. 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 this assistant
- "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."
