# Referral Partner Sales Dashboard: Power BI rebuild guide

This guide lists everything needed to rebuild the Referral Partner Sales Dashboard in Power BI Desktop from the sample data in this folder. The HTML dashboard (`dashboard/Referral Partner Sales Dashboard.html`) shows what the finished report should look like, and the screenshots show each page. The report has 6 pages and should take about an hour to rebuild.

## 1. Files you need

- `data/Larkspur Data Tables.xlsx`: ten Excel tables (one per sheet) plus an About sheet with reconciliation totals. The same tables are in `data/csv/` if you prefer CSV.
- `rebuild/Larkspur Theme.json`: colors, fonts, and visual defaults.
- This guide, which holds every measure in DAX.

## 2. Load the data

1. In Power BI Desktop, choose Get data > Excel workbook and open `Larkspur Data Tables.xlsx`.
2. In the Navigator, check the ten tables whose names start with `tbl` (not the sheets), then choose Transform data.
3. Rename each query to drop the `tbl` prefix and restore spaces: Applications, Workflow Actions, Document Requests, Officers, Channels, Equipment Categories, Partners, Sales Reps, States, Calendar.
4. Set data types using the table below, then choose Close and apply.

| Column pattern | Set the data type to | Examples |
|---|---|---|
| Every ID column | Text | ApplicationID, RequestID, PartnerID, OfficerID, RepID, ChannelID, StateCode, StateFIPS (keeps the leading zero, as in 01) |
| Columns ending in DateTime | Date/time | ReceivedDateTime, DecisionDateTime, FileCompleteDateTime, ActionDateTime, RequestedDateTime |
| Date columns | Date | ReceivedDate, FundedDate, OnboardDate, HireDate, StartDate, Calendar[Date] |
| Amounts | Fixed decimal number, formatted as currency | RequestedAmount, FundedAmount, ApprovalLimit |
| Business-day durations | Decimal number | TurnTimeBusinessDays, DecisionWaitBusinessDays, WaitBusinessDays |
| Counts and calendar numbers | Whole number | DocumentRequests, ActionCount, BusinessDayOfMonth, BusinessDaysLeftInMonth |

## 3. Calculated tables and columns

The Calendar table comes from the workbook, because the business-day columns (holidays, business days left in the month) are maintained in the source. Mark it as the date table on `Calendar[Date]`, then add two columns to it with **New column**: `Month Start = DATE ( YEAR ( Calendar[Date] ), MONTH ( Calendar[Date] ), 1 )`, `Month = FORMAT ( Calendar[Date], "mmm yyyy" )`, and `Quarter = "Q" & QUARTER ( Calendar[Date] ) & " " & YEAR ( Calendar[Date] )`. Sort Month by Month Start.

Create these on the Modeling tab with **New table**:

**Flow Stage**

```
Flow Stage =
DATATABLE (
    "Stage", STRING, "Sort", INTEGER,
    {
        { "Received", 1 }, { "Decided", 2 }, { "Approved", 3 },
        { "Contract sent", 4 }, { "Contract signed", 5 }, { "Funded", 6 }
    }
)
```

**Pipeline Stage**

```
Pipeline Stage =
DATATABLE (
    "Stage", STRING, "Sort", INTEGER,
    {
        { "Submitted", 1 }, { "Approved", 2 }, { "Contract sent", 3 },
        { "Contract signed", 4 }, { "Funded", 5 }
    }
)
```

**Score Bins**

```
Score Bins =
SELECTCOLUMNS ( GENERATESERIES ( 0, 95, 5 ), "Bin", [Value] )
```

**Score Threshold**

```
Score Threshold =
DATATABLE ( "Score Threshold", INTEGER, { { 35 }, { 45 }, { 55 }, { 70 } } )
```

Add these calculated columns with **New column** on the table named in each heading:

**Applications[File Complete Date]**: Date the last requested document arrived.

```
File Complete Date =
DATE ( YEAR ( Applications[FileCompleteDateTime] ), MONTH ( Applications[FileCompleteDateTime] ), DAY ( Applications[FileCompleteDateTime] ) )
```

**Applications[File Complete Days Left]**: Business days left in the month when the file was complete.

```
File Complete Days Left =
LOOKUPVALUE ( Calendar[BusinessDaysLeftInMonth], Calendar[Date], Applications[File Complete Date] )
```

**Applications[File Complete Window]**: Month-end window flag used on the Turn time page.

```
File Complete Window =
IF (
    ISBLANK ( Applications[File Complete Days Left] ), BLANK (),
    IF ( Applications[File Complete Days Left] <= 2, "Last 3 business days", "Rest of month" )
)
```

**Applications[Days Left Bucket]**: Axis for the decision wait chart, sorted by Days Left Sort.

```
Days Left Bucket =
VAR d = Applications[File Complete Days Left]
RETURN
    IF ( ISBLANK ( d ), BLANK (), IF ( d >= 10, "10+", IF ( d = 0, "Last day", FORMAT ( d, "0" ) ) ) )
```

**Applications[Days Left Sort]**: Sort column for Days Left Bucket.

```
Days Left Sort =
VAR d = Applications[File Complete Days Left] RETURN IF ( ISBLANK ( d ), BLANK (), 10 - MIN ( d, 10 ) )
```

**Applications[Exit Point]**: Where applications that did not fund left the process.

```
Exit Point =
SWITCH (
    TRUE (),
    Applications[Status] = "Declined", "Declined",
    Applications[Status] = "Reversed", "Approval reversed",
    Applications[Status] = "Withdrawn" && ISBLANK ( Applications[Decision] ), "Withdrawn while documents were pending",
    Applications[Status] = "Withdrawn", "Withdrawn after approval",
    Applications[Status] = "In progress", "Still in progress",
    BLANK ()
)
```

**Applications[Outcome Group]**: Outcome group for the sales pages.

```
Outcome Group =
SWITCH (
    Applications[Status],
    "Funded", "Funded",
    "Declined", "Declined",
    "In progress", "In progress",
    "Withdrawn or reversed"
)
```

**Channels[Channel Comparison]**: Legend for the conversion trend line.

```
Channel Comparison =
IF ( Channels[Channel] = "Independent broker", "Independent broker", "All other channels" )
```

**Equipment Categories[Category Comparison]**: Legend for the document wait trend line.

```
Category Comparison =
IF ( 'Equipment Categories'[EquipmentCategory] = "Medical", "Medical", "All other categories" )
```

Set these sort-by-column options: Days Left Bucket by Days Left Sort, Channels[Channel] by Channels[SortOrder], 'Equipment Categories'[EquipmentCategory] by its SortOrder, and the Stage column of both stage tables by its Sort column.

## 4. Relationships

Delete any relationships Power BI detected on its own, then create these in Model view. All are many-to-one with single cross-filter direction. Inactive relationships are switched on inside specific measures with USERELATIONSHIP.

| From (many side) | To (one side) | Active | Note |
|---|---|---|---|
| `Applications[ReceivedDate]` | `Calendar[Date]` | Yes | Filters by the date the application was received |
| `Applications[FundedDate]` | `Calendar[Date]` | No (inactive) | Used by funded measures through USERELATIONSHIP |
| `Applications[ChannelID]` | `Channels[ChannelID]` | Yes |  |
| `Applications[EquipmentCategory]` | `'Equipment Categories'[EquipmentCategory]` | Yes |  |
| `Applications[OfficerID]` | `Officers[OfficerID]` | Yes | The credit officer assigned to the application |
| `Applications[PartnerID]` | `Partners[PartnerID]` | Yes | Blank for direct and marketplace applications |
| `Partners[RepID]` | `'Sales Reps'[RepID]` | Yes | Rep filters reach applications through Partners |
| `Applications[StateCode]` | `States[StateCode]` | Yes | Location of the borrowing business |
| `'Workflow Actions'[ApplicationID]` | `Applications[ApplicationID]` | Yes | Actions inherit every application filter |
| `'Document Requests'[ApplicationID]` | `Applications[ApplicationID]` | Yes | Requests inherit every application filter |

The Flow Stage, Pipeline Stage, Score Bins, and Score Threshold tables have no relationships.

## 5. Theme and page setup

1. View > Themes > Browse for themes, then choose `Larkspur Theme.json`.
2. Format page > Canvas settings: Custom, 1600 by 1000 pixels, to match the screenshots. The default 16:9 page also works.
3. The HTML version uses the IBM Plex Sans typeface. The theme uses Segoe UI, which every Windows computer has, so the report looks the same for everyone who opens it.
4. Place a rectangle down the left side in #1C1F3A for the navigation rail and add page navigator buttons, or use the built-in page tabs.

## 6. Measures

Create a blank table named **_Measures** to hold the measures (Home > Enter data > Load), then add each measure below and set its display folder and format. This report uses 47 measures.

### Core

**Applications** (Whole number). Count of applications in the current filter context.

```
Applications =
COUNTROWS ( Applications )
```

**Decided Applications** (Whole number). Applications with a credit decision recorded.

```
Decided Applications =
CALCULATE ( [Applications], NOT ISBLANK ( Applications[Decision] ) )
```

**Approved Applications** (Whole number). Applications approved, including approvals later reversed.

```
Approved Applications =
CALCULATE ( [Applications], Applications[Decision] = "Approved" )
```

**Declined Applications** (Whole number). Applications declined.

```
Declined Applications =
CALCULATE ( [Applications], Applications[Decision] = "Declined" )
```

**Approval Rate** (Percentage, 1 decimal). Approved share of decided applications.

```
Approval Rate =
DIVIDE ( [Approved Applications], [Decided Applications] )
```

**Funded Applications** (Whole number). Applications that funded, counted by the date received.

```
Funded Applications =
CALCULATE ( [Applications], Applications[Status] = "Funded" )
```

**Funding Conversion** (Percentage, 1 decimal). Share of applications received that went on to fund.

```
Funding Conversion =
DIVIDE ( [Funded Applications], [Applications] )
```

**Funded Deals** (Whole number). Deals counted by the date they funded, using the inactive FundedDate relationship.

```
Funded Deals =
CALCULATE (
    [Funded Applications],
    USERELATIONSHIP ( Calendar[Date], Applications[FundedDate] )
)
```

**Funded Volume** (Currency, no decimals). Dollars funded in the selected months, by funding date.

```
Funded Volume =
CALCULATE (
    SUM ( Applications[FundedAmount] ),
    USERELATIONSHIP ( Calendar[Date], Applications[FundedDate] )
)
```

**Average Deal Size** (Currency, no decimals). Average funded amount per deal.

```
Average Deal Size =
DIVIDE ( [Funded Volume], [Funded Deals] )
```

**Contracts Sent** (Whole number). Applications with a contract sent to the customer.

```
Contracts Sent =
CALCULATE (
    DISTINCTCOUNT ( 'Workflow Actions'[ApplicationID] ),
    'Workflow Actions'[ActionType] = "Contract sent"
)
```

**Contracts Signed** (Whole number). Applications with a signed contract.

```
Contracts Signed =
CALCULATE ( [Applications], Applications[ContractSigned] = "Yes" )
```

**Signed of Sent** (Percentage, 1 decimal). Share of contracts sent that the customer signed.

```
Signed of Sent =
DIVIDE ( [Contracts Signed], [Contracts Sent] )
```

### Partner Sales

**Partner Submissions** (Whole number). Applications submitted by referral partners.

```
Partner Submissions =
CALCULATE ( [Applications], Channels[ChannelGroup] = "Referral partner" )
```

**Active Partners** (Whole number). Partners with at least one submission in the selection.

```
Active Partners =
DISTINCTCOUNTNOBLANK ( Applications[PartnerID] )
```

**Partners Onboarded** (Whole number). Partners onboarded by the end of the selected months.

```
Partners Onboarded =
VAR lastDay = MAX ( Calendar[Date] )
RETURN
    CALCULATE ( COUNTROWS ( Partners ), Partners[OnboardDate] <= lastDay )
```

**New Partners** (Whole number). Partners onboarded during the selected months.

```
New Partners =
VAR firstDay = MIN ( Calendar[Date] )
VAR lastDay = MAX ( Calendar[Date] )
RETURN
    CALCULATE ( COUNTROWS ( Partners ), Partners[OnboardDate] >= firstDay, Partners[OnboardDate] <= lastDay )
```

**Repeat Customer Share** (Percentage, 1 decimal). Share of submissions from businesses that have funded with Larkspur before.

```
Repeat Customer Share =
DIVIDE (
    CALCULATE ( [Applications], Applications[CustomerType] = "Repeat" ),
    [Applications]
)
```

**Pipeline Count** (Whole number). Value for each partner pipeline step.

```
Pipeline Count =
SWITCH (
    SELECTEDVALUE ( 'Pipeline Stage'[Stage] ),
    "Submitted", [Applications],
    "Approved", [Approved Applications],
    "Contract sent", [Contracts Sent],
    "Contract signed", [Contracts Signed],
    "Funded", [Funded Applications]
)
```

**Contracts Out Not Signed** (Currency, no decimals). Requested amount on approved deals with a contract out and no signature yet.

```
Contracts Out Not Signed =
VAR sentApps =
    CALCULATETABLE (
        VALUES ( 'Workflow Actions'[ApplicationID] ),
        'Workflow Actions'[ActionType] = "Contract sent",
        REMOVEFILTERS ( Calendar )
    )
RETURN
    CALCULATE (
        SUM ( Applications[RequestedAmount] ),
        REMOVEFILTERS ( Calendar ),
        TREATAS ( sentApps, Applications[ApplicationID] ),
        Applications[ContractSigned] = "No",
        Applications[Status] = "In progress"
    )
```

### Partner Health

**As Of Date** (Date). Latest application date in the model. Every health measure counts back from it.

```
As Of Date =
CALCULATE ( MAX ( Applications[ReceivedDate] ), REMOVEFILTERS ( ) )
```

**Recent Pace** (Decimal, 1 place). Average submissions per month over the last 90 days.

```
Recent Pace =
VAR asOf = [As Of Date]
RETURN
    COALESCE (
        DIVIDE ( CALCULATE ( [Applications], REMOVEFILTERS ( Calendar ), Applications[ReceivedDate] > asOf - 90 ), 3 ),
        0
    )
```

**Usual Pace** (Decimal, 1 place). Average submissions per month over the six months before the recent window.

```
Usual Pace =
VAR asOf = [As Of Date]
RETURN
    DIVIDE (
        CALCULATE (
            [Applications],
            REMOVEFILTERS ( Calendar ),
            Applications[ReceivedDate] > asOf - 270,
            Applications[ReceivedDate] <= asOf - 90
        ),
        6
    )
```

**Activity Points** (Decimal, 0 places). Up to 40 points for keeping the usual pace.

```
Activity Points =
VAR u = [Usual Pace]
VAR r = [Recent Pace]
VAR ratio = IF ( u > 0, DIVIDE ( r, u ), IF ( r > 0, 1, 0 ) )
RETURN
    40 * MIN ( 1, ratio )
```

**Conversion 12 Months** (Percentage, 0 decimals). Funded share of the partner's closed submissions over 12 months.

```
Conversion 12 Months =
VAR asOf = [As Of Date]
VAR closed =
    CALCULATE ( [Applications], REMOVEFILTERS ( Calendar ),
        Applications[ReceivedDate] > asOf - 365, Applications[Status] <> "In progress" )
VAR funded =
    CALCULATE ( [Applications], REMOVEFILTERS ( Calendar ),
        Applications[ReceivedDate] > asOf - 365, Applications[Status] = "Funded" )
RETURN
    DIVIDE ( funded, closed, 0 )
```

**Conversion Points** (Decimal, 0 places). Up to 30 points. A 60% funding conversion earns all 30.

```
Conversion Points =
30 * MIN ( 1, [Conversion 12 Months] / 0.6 )
```

**Days Since Last Submission** (Whole number). Calendar days since the partner's most recent submission.

```
Days Since Last Submission =
INT ( [As Of Date] - CALCULATE ( MAX ( Applications[ReceivedDate] ), REMOVEFILTERS ( Calendar ) ) )
```

**Recency Points** (Decimal, 0 places). Up to 20 points, falling to zero at 90 days without a submission.

```
Recency Points =
VAR ds = [Days Since Last Submission]
RETURN
    SWITCH ( TRUE (), ds <= 14, 20, ds >= 90, 0, 20 * ( 90 - ds ) / 76 )
```

**Approval 12 Months** (Percentage, 0 decimals). Approval rate on the partner's submissions over 12 months.

```
Approval 12 Months =
VAR asOf = [As Of Date]
RETURN
    CALCULATE ( [Approval Rate], REMOVEFILTERS ( Calendar ), Applications[ReceivedDate] > asOf - 365 )
```

**Approval Points** (Decimal, 0 places). Up to 10 points. An 85% approval rate earns all 10.

```
Approval Points =
10 * MIN ( 1, DIVIDE ( [Approval 12 Months], 0.85, 0 ) )
```

**Is Scored** (Whole number). 1 when a partner has at least six months of history and three submissions in the last year.

```
Is Scored =
VAR asOf = [As Of Date]
VAR onb = SELECTEDVALUE ( Partners[OnboardDate] )
VAR n12 =
    CALCULATE ( [Applications], REMOVEFILTERS ( Calendar ), Applications[ReceivedDate] > asOf - 365 )
RETURN
    IF ( onb <= asOf - 180 && n12 >= 3, 1, 0 )
```

**Health Score** (Whole number). Partner health score from 0 to 100. Blank for partners without enough history.

```
Health Score =
IF (
    [Is Scored] = 1,
    ROUND ( [Activity Points] + [Conversion Points] + [Recency Points] + [Approval Points], 0 )
)
```

**Health Band** (Text). At risk under 45, Watch from 45 to 69, Healthy at 70 and up.

```
Health Band =
VAR s = [Health Score]
RETURN
    IF ( ISBLANK ( s ), BLANK (), IF ( s < 45, "At risk", IF ( s < 70, "Watch", "Healthy" ) ) )
```

**Main Issue** (Text). The score part with the biggest shortfall, written as a short phrase for the list and the outreach sheet.

```
Main Issue =
VAR ga = 40 - [Activity Points]
VAR gc = 30 - [Conversion Points]
VAR gr = 20 - [Recency Points]
VAR gp = 10 - [Approval Points]
VAR mx = MAX ( MAX ( ga, gc ), MAX ( gr, gp ) )
VAR u = [Usual Pace]
RETURN
    SWITCH (
        TRUE (),
        mx <= 0.5, "No weak area",
        mx = ga,
            IF ( [Recent Pace] = 0, "No submissions in 90 days",
                "Activity down " & FORMAT ( 1 - DIVIDE ( [Recent Pace], u ), "0%" ) ),
        mx = gc, "Funding conversion " & FORMAT ( [Conversion 12 Months], "0%" ),
        mx = gr, "Last submission " & [Days Since Last Submission] & " days ago",
        "Approval rate " & FORMAT ( [Approval 12 Months], "0%" )
    )
```

**Score Threshold Value** (Whole number). Selected threshold, 45 when nothing is selected.

```
Score Threshold Value =
SELECTEDVALUE ( 'Score Threshold'[Score Threshold], 45 )
```

**Below Threshold** (Whole number). Visual filter for the partner list and the outreach sheet.

```
Below Threshold =
VAR s = [Health Score]
RETURN
    IF ( NOT ISBLANK ( s ) && s < [Score Threshold Value], 1, 0 )
```

**Partners Scored** (Whole number). Count of partners with a health score.

```
Partners Scored =
SUMX ( VALUES ( Partners[PartnerID] ), [Is Scored] )
```

**Partners At Risk** (Whole number). Partners scoring under 45. Copy the pattern for Watch and Healthy.

```
Partners At Risk =
COUNTROWS ( FILTER ( VALUES ( Partners[PartnerID] ), [Health Band] = "At risk" ) )
```

**Partners in Bin** (Whole number). Partners whose score falls in each five-point bin.

```
Partners in Bin =
VAR lo = SELECTEDVALUE ( 'Score Bins'[Bin] )
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( Partners[PartnerID] ),
            VAR s = [Health Score]
            RETURN NOT ISBLANK ( s ) && s >= lo && ( s < lo + 5 || lo = 95 )
        )
    )
```

**Funded Last 12 Months** (Currency, no decimals). Funded dollars on the partner's submissions from the last 12 months. The list is sorted by this.

```
Funded Last 12 Months =
VAR asOf = [As Of Date]
RETURN
    CALCULATE ( SUM ( Applications[FundedAmount] ), REMOVEFILTERS ( Calendar ), Applications[ReceivedDate] > asOf - 365 )
```

**Funded From At-Risk Partners** (Currency, no decimals). Funded dollars from partners now scoring under 45.

```
Funded From At-Risk Partners =
SUMX (
    FILTER ( VALUES ( Partners[PartnerID] ), [Health Band] = "At risk" ),
    [Funded Last 12 Months]
)
```

**Last Submission Date** (Date). Most recent submission from the partner.

```
Last Submission Date =
CALCULATE ( MAX ( Applications[ReceivedDate] ), REMOVEFILTERS ( Calendar ) )
```

### Outreach List

**Partners Covered** (Whole number). Partners assigned to the selected rep.

```
Partners Covered =
COUNTROWS ( Partners )
```

**Partners Submitting 90 Days** (Whole number). Partners with a submission in the last 90 days.

```
Partners Submitting 90 Days =
VAR asOf = [As Of Date]
RETURN
    CALCULATE ( DISTINCTCOUNTNOBLANK ( Applications[PartnerID] ), REMOVEFILTERS ( Calendar ), Applications[ReceivedDate] > asOf - 90 )
```

**Funded Volume 90 Days** (Currency, no decimals). Funded dollars over the last 90 days, by funding date.

```
Funded Volume 90 Days =
VAR asOf = [As Of Date]
RETURN
    CALCULATE ( SUM ( Applications[FundedAmount] ), REMOVEFILTERS ( Calendar ), Applications[FundedDate] > asOf - 90 )
```

**Funded Deals 90 Days** (Whole number). Deals funded over the last 90 days.

```
Funded Deals 90 Days =
VAR asOf = [As Of Date]
RETURN
    CALCULATE ( [Funded Applications], REMOVEFILTERS ( Calendar ), Applications[FundedDate] > asOf - 90 )
```

**Outreach Title** (Text). Dynamic title for the printable outreach page.

```
Outreach Title =
"Weekly partner outreach list for " & SELECTEDVALUE ( 'Sales Reps'[RepName], "all reps" )
```

## 7. Pages and visuals

### 1. Sales overview

Add a report-level filter `Channels[ChannelGroup]` = Referral partner so every page counts partner submissions only. Add slicers for `Calendar[Date]` (Between), `'Sales Reps'[RepName]`, and `Partners[PartnerType]`, and sync the rep and partner type slicers to every page.

| Visual | Fields | Settings |
|---|---|---|
| Card (new), six cards | [Applications], [Approval Rate], [Funded Deals], [Funded Volume], [Funding Conversion], [Active Partners] | Rename [Applications] to Partner submissions. Reference labels: [Average Deal Size] and [Partners Onboarded]. |
| Column chart | X: `Calendar[Month Start]`. Y: [Funded Volume] | By funding date. |
| Bar chart | Y: `Partners[PartnerType]`. X: [Applications]. Tooltips: [Funding Conversion] | Add [Funding Conversion] as a second data label with the new label options, or show it in the tooltip. |
| 100% stacked bar chart | Y: `Partners[PartnerType]`. X: [Applications]. Legend: `Applications[CustomerType]` | New #4B5BD6, Repeat #1BA584. |
| Table | `Partners[PartnerName]`, `Partners[PartnerType]`, `'Sales Reps'[RepName]`, [Applications], [Funding Conversion], [Funded Volume], [Last Submission Date] | Top N 12 on PartnerName by [Funded Volume]. |

### 2. Rep performance



| Visual | Fields | Settings |
|---|---|---|
| Table | `'Sales Reps'[RepName]`, `'Sales Reps'[Territory]`, [Partners Covered], [Active Partners], [New Partners], [Applications], [Approval Rate], [Funding Conversion], [Funded Deals], [Funded Volume], [Repeat Customer Share] | Data bars on Funded Volume. |
| Bar chart | Y: `'Sales Reps'[RepName]`. X: [Funded Volume] |  |
| 100% stacked bar chart | Y: `'Sales Reps'[RepName]`. X: [Applications]. Legend: `Applications[Outcome Group]` | Visual filter: Outcome Group is not In progress. |

### 3. Partner health

Do not sync the date slicer to this page, because every health measure counts back from [As Of Date]. Show `'Score Threshold'[Score Threshold]` as a single-select dropdown slicer.

| Visual | Fields | Settings |
|---|---|---|
| Card (new), five cards | [Partners Scored], [Partners At Risk] and its Watch and Healthy copies, [Funded From At-Risk Partners] |  |
| Table | `Partners[PartnerName]`, `'Sales Reps'[RepName]`, [Health Score], [Activity Points], [Conversion Points], [Recency Points], [Approval Points], [Main Issue], [Funded Last 12 Months] | Visual filter: [Below Threshold] is 1. Sort by Funded Last 12 Months, descending. Background rules on Health Score: under 45 #FBE3D8, 45 to 69 #FBF0CF, 70 and up #DDF1E4. |
| Column chart | X: `'Score Bins'[Bin]`. Y: [Partners in Bin] | Color rules: bins under 45 #C2410C, 45 to 69 #D9A000, 70 and up #0F7B3E. |
| Stacked bar chart | Y: `'Sales Reps'[RepName]`. X: [Partners At Risk] and its Watch and Healthy copies | Same three colors. |

### 4. Pipeline



| Visual | Fields | Settings |
|---|---|---|
| Funnel | Category: `'Pipeline Stage'[Stage]`. Values: [Pipeline Count] | Stage sorted by Sort. |
| Line chart | X: `Calendar[Month Start]`. Y: [Approval Rate] and [Funding Conversion] | Both are percentages on one axis. |
| Table | `'Sales Reps'[RepName]`, [Applications], [Approved Applications], [Contracts Sent], [Contracts Signed], [Funded Applications], [Approval Rate], [Signed of Sent], [Funding Conversion] | Data bars on Funding Conversion. |
| Bar chart | Y: `'Sales Reps'[RepName]`. X: [Contracts Out Not Signed] |  |

### 5. Partners by state



| Visual | Fields | Settings |
|---|---|---|
| Field parameter | Modeling > New parameter > Fields with [Funded Volume], [Applications], and [Funding Conversion] | Show it as a tile slicer above the map. |
| Map | Location: `States[StateName]`. Color: the field parameter | Use the filled map or shape map visual your tenant allows. Gradient from #D9DEF7 to #1C2366. |
| Table | `States[StateName]`, [Applications], [Funding Conversion], [Funded Volume] | Data bars on Funded Volume. |

### 6. Weekly partner outreach list

Set this page to Letter size, portrait (Format page > Canvas settings), so File > Export > Export to PDF gives one printed page per rep. Sync the rep, partner type, and score threshold slicers.

| Visual | Fields | Settings |
|---|---|---|
| Text box with dynamic values | [Outreach Title] and [As Of Date] |  |
| Card (new), four cards | [Partners Covered], [Partners Submitting 90 Days], [Funded Volume 90 Days], count of partners with [Below Threshold] = 1 |  |
| Table: partners to call first | `Partners[PartnerName]`, `Partners[City]`, [Health Score], [Main Issue], [Last Submission Date], [Funded Last 12 Months] | Visual filter [Below Threshold] is 1, Top N 7 by [Funded Last 12 Months]. Add an empty Notes column with a measure that returns a blank string. |
| Table: partners to thank | `Partners[PartnerName]`, [Funded Deals 90 Days], [Funded Volume 90 Days] | Top N 5 by [Funded Volume 90 Days]. |
| Table: new partners | `Partners[PartnerName]`, `Partners[OnboardDate]`, [Applications], [Funded Volume] | Filter OnboardDate after March 4, 2026 (seven months before the as-of date). |


## 8. Check your numbers

With every slicer cleared, the report should show these values. They match the Reconciliation totals on the About sheet of the data workbook and the HTML dashboard.

| Measure or visual | Expected value |
|---|---|
| Partner submissions ([Applications] with the report filter) | 4,475 |
| Approval Rate | 70.4% |
| Funded Deals | 2,016 |
| Funded Volume | $152,923,500 |
| Funding Conversion | 45.1% |
| Active Partners | 369 |
| Partners Covered | 404 |
| Partners Scored | 253 |
| Partners At Risk (under 45) | 71 |
| Watch (45 to 69) | 56 |
| Healthy (70 and up) | 126 |


*Sample data. All names, organizations, and figures are fictional.*