power-bi-builder

What this skill does

# Power BI Builder

Produce a complete Power BI report design specification. This skill generates the design document that a Power BI developer uses to build the report. Every visual, measure, and configuration decision is made explicitly. No generic placeholder content.

## Report Purpose and Audience

State clearly:

- **Business question the report answers**: One sentence (e.g., "How is our agency's premium production trending by producer and line of business, and where are we against our annual target?")
- **Primary audience**: Role titles, not names. Include the decision they make using this report.
- **Secondary audience**: Who else views it (e.g., producers viewing only their own data)
- **Decision cadence**: How often the report is reviewed and acted on (daily operations, weekly team meeting, monthly board review)

## Data Source Connection

**Connection mode decision**:

| Mode | Use When |
|------|----------|
| Import | Data is < 1 GB, refresh frequency is acceptable at 8x/day or less, complex DAX measures needed |
| DirectQuery | Near-real-time data required (operations), data is > 1 GB, source system is SQL Server / Azure SQL |
| Composite | Mix: some tables imported (slow-changing dimensions) and some DirectQuery (fact tables) |

For most insurance and financial services management reporting: **Import mode** with daily or hourly refresh is appropriate and provides best performance. Use DirectQuery only when the business explicitly requires same-hour data.

**Data sources** (list each):

| Source | Type | Connection String / URL | Refresh Frequency | Tables / Endpoints Used |
|--------|------|------------------------|------------------|------------------------|
| Agency Management System | SQL Server | Server=ams-db.firm.local; Database=AMS_Prod | Daily at 2 AM | dbo.Policies, dbo.Clients, dbo.Producers |
| SharePoint List | SharePoint Online | https://[tenant].sharepoint.com/sites/ops | On refresh | Renewal Tracker list |
| Excel workbook | SharePoint file | /sites/ops/Shared Documents/Targets.xlsx | On refresh | Targets sheet |

**Gateway**: On-premises data gateway required for SQL Server, local files, or any source behind the corporate firewall. Specify: cluster name, installed on which server, which service account runs the gateway service.

## Dataset Design

**Calculated columns vs. measures decision rule**:
- Calculated columns: Used for text manipulation, row-level categories, foreign key derivations. Computed at refresh time, stored in memory.
- Measures: Used for all aggregations, ratios, YTD, MoM calculations. Computed at query time. Always prefer measures over calculated columns for aggregations.

**Key tables needed** (describe, detailed schema in data-model-mapper skill):
- Fact tables: Transactions, policies, claims, loan applications
- Dimension tables: Date, Producer/Agent, Client, Product/Coverage, State/Geography
- Bridge tables: Only if many-to-many relationships exist

## Page Layout Design

Design each report page. Typical report has 3-6 pages.

**Page template**:

```
Page Name: [Noun phrase — what the user sees on this page]
Purpose: [One sentence]
Audience: [All users / Specific role]
Row-Level Security Applied: Yes / No

Slicer Panel (left or top bar, ~15% of canvas width):
  - Date Range: Between slicer on Date[Date]
  - [Dimension 1]: Dropdown slicer
  - [Dimension 2]: Dropdown slicer
  - Clear All Filters button: Bookmark action

Section 1 — KPI Cards (top row):
  Visual: Card (New)
  Metrics displayed (one card per metric):
    - [Metric name]: [Measure name], format: $#,##0 or #,##0.0%
    - Reference label: [Comparison: vs. prior period / vs. target]
    - Conditional formatting: Green if >= target, Red if < 90% of target

Section 2 — Trend (main chart):
  Visual: Line chart
  X-axis: Date[Month-Year]
  Y-axis: [Primary measure]
  Secondary Y-axis: [Secondary measure if dual-axis needed]
  Legend: [Dimension to break by]
  Data labels: On for last data point only
  Markers: On
  Reference line: Target value (constant or from measure)

Section 3 — Breakdown (supporting chart):
  Visual: Clustered bar chart / Stacked bar / Table — specify which and why
  Axis: [Dimension]
  Values: [Measures]
  Sorted by: [Measure] descending
  Conditional formatting on value column: Data bars or color scale

Section 4 — Detail Table (bottom, collapsible):
  Visual: Table
  Columns: [List column names and measures]
  Row subtotals: On / Off
  Column totals: On / Off
  Conditional formatting: [Which columns get highlighting]
```

## Filter and Slicer Design

**Slicers**: Place all slicers in a consistent location across all pages. Use a vertical panel on the left for desktop viewing. Sync all slicers across pages using the Sync Slicers pane.

| Slicer | Field | Type | Default Selection |
|--------|-------|------|------------------|
| Date Range | Date[Date] | Between (date range) | Current year to today |
| Producer | Producer[ProducerName] | Dropdown, multi-select | All |
| Line of Business | Product[LineOfBusiness] | Dropdown, multi-select | All |
| State | Geography[State] | Dropdown, multi-select | All |

**Drill-through pages**: Configure drill-through on the detail table or bar chart so users can right-click a producer or policy and navigate to a detail page showing all records for that selection.

**Report-level filters** (hidden from users): Filter out test records, inactive carriers, and records with invalid status codes. Document each hidden filter.

## DAX Measure Library

For every metric displayed in the report, write the complete DAX formula:

```
-- Written Premium (YTD)
Written Premium YTD =
CALCULATE(
    SUM(Policies[WrittenPremium]),
    DATESYTD(Date[Date])
)

-- Written Premium (Prior Year YTD) 
Written Premium PYTD =
CALCULATE(
    [Written Premium YTD],
    SAMEPERIODLASTYEAR(Date[Date])
)

-- Year-over-Year Growth %
Written Premium YoY % =
DIVIDE(
    [Written Premium YTD] - [Written Premium PYTD],
    [Written Premium PYTD],
    BLANK()
)

-- vs. Target %
Written Premium vs Target % =
DIVIDE(
    [Written Premium YTD],
    SUM(Targets[PremiumTarget]),
    BLANK()
)

-- Policy Count (Active)
Active Policy Count =
CALCULATE(
    COUNTROWS(Policies),
    Policies[Status] = "Active"
)

-- Retention Rate (12-month rolling)
Retention Rate 12M =
DIVIDE(
    CALCULATE(
        COUNTROWS(Policies),
        Policies[Status] = "Active",
        DATESINPERIOD(Date[Date], LASTDATE(Date[Date]), -12, MONTH)
    ),
    CALCULATE(
        COUNTROWS(Policies),
        DATEADD(Date[Date], -12, MONTH)
    ),
    BLANK()
)
```

Write a complete measure for every KPI card and chart axis value in the report. Include formatting string comments.

## Conditional Formatting Rules

| Visual | Column/Measure | Format Type | Rules |
|--------|---------------|-------------|-------|
| KPI Cards | vs. Target % | Background color | >= 100%: green #107C10; 90-99%: amber #FFB900; < 90%: red #D13438 |
| Producer table | Retention Rate | Color scale | 3-color: red at 70%, white at 85%, green at 100% |
| State map | Written Premium | Color saturation | Light to dark blue based on value |
| Detail table | Days Open (claims) | Background color | > 30 days: amber; > 60 days: red |

## Mobile Layout

For each page, configure the mobile layout (View > Mobile layout in Power BI Desktop):

- KPI cards: Stack vertically at full width
- Primary chart: Full width, reduced height
- Slicers: Collapse into a filter icon (use the built-in mobile filter pane)
- Detail tables: Omit from mobile layout (too small to use on phone)

## Row-Level Security

If the report will be distributed to producers or agents who should only see their own data:

**RLS role definition** (in Power BI Desktop > Modeling > Manage Roles):
```
Role: Producer
Table: Producers
Filter: [ProducerEmail] = USERPRINCIPALNAME()
```

**Testing**: Create a test role for each RLS scenario. Verify that a producer logging in sees only their records and that totals reflect their filtered da