# Grantmaking Impact Dashboard: Power BI Rebuild Guide

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

This guide explains how to rebuild the Halden Table Fund grantmaking impact dashboard in Power BI Desktop from the files in this folder. It lists the data model, every DAX measure, the layout of each page, and the design of the print-friendly quarterly summary page. Someone who knows Power BI should be able to finish the rebuild in under an hour.

## Files you need

| File | What it is |
|---|---|
| data/Halden Table Fund - Grants Data Model.xlsx | One Excel table per sheet: Fact Grants, Dim Organization, Dim Program, Program Crosswalk, Dim Geography, Dim Fiscal Calendar, Bridge Populations, and Targets. |
| data/*.csv | The same eight tables as CSV files, in case you prefer to load from a folder. |
| rebuild/Halden Table Fund Theme.json | The report theme with the program colors, fonts, and visual defaults. |
| dashboard/Grantmaking Impact Dashboard.html | The finished dashboard in a browser, which is the reference for every page below. |
| pdf/Quarterly Summary - Sample.pdf | A sample of the print-friendly quarterly summary page. |

## Step 1: Load the data

1. In Power BI Desktop, choose Get data, then Excel workbook, and open the data model workbook.
2. Select the eight tables (they show with a table icon) and choose Transform data. Skip the About sheet.
3. In Power Query, confirm these data types, because the ID columns must stay as text:
    - Text: every ID and key column (Grant ID, Organization ID, Program ID, Geography Key, Target ID, Country Code), plus Fiscal Year, Fiscal Quarter, and Fiscal Year Quarter.
    - Date: Award Date, Grant End Date, Date, and Month Start.
    - Fixed decimal (currency): Award Amount and Target Value.
    - Whole number: People Reached Reported, People Reached Adjusted, Duration Months, Fiscal Year Number, Fiscal Month Number, and Calendar Year.
4. Choose Close and apply.

## Step 2: Build the model

Create these relationships in Model view. All of them are many to one with a single filter direction unless noted.

| From (many side) | To (one side) | Notes |
|---|---|---|
| Fact Grants[Organization ID] | Dim Organization[Organization ID] | |
| Fact Grants[Program ID] | Dim Program[Program ID] | |
| Fact Grants[Geography Key] | Dim Geography[Geography Key] | |
| Fact Grants[Award Date] | Dim Fiscal Calendar[Date] | This is the date that drives every fiscal year calculation. |
| Bridge Populations[Grant ID] | Fact Grants[Grant ID] | Keep the direction single. The population measures turn on two-way filtering only where they need it. |
| Program Crosswalk[Program ID] | Dim Program[Program ID] | Used only for the crosswalk table visual. |

Leave Targets unconnected, because it mixes five-year goals and annual budgets at different grains. The budget and goal measures below read it with TREATAS and simple filters.

Then finish the model with these settings:

- Mark Dim Fiscal Calendar as the date table, using the Date column.
- Sort Month Name by Fiscal Month Number so that July appears first, and sort Fiscal Year Quarter by Date.
- Set Dim Geography[Country] to the Country data category, Dim Geography[State or Province] to State or Province, Dim Geography[City] to City, and Latitude and Longitude to their categories.
- Create a hierarchy in Dim Geography called Location with Region, Country, State or Province, and City.
- Hide the key columns on the fact table (Organization ID, Program ID, Geography Key) so report builders use the dimension columns instead.
- Create an empty table called Measures (Enter data, one blank column) to hold every measure, then hide its blank column.

## Step 3: Add the measures

Every measure below goes in the Measures table. Set the format shown after each group.

### Core measures

```DAX
Total Awarded = SUM ( 'Fact Grants'[Award Amount] )

Grants Awarded = COUNTROWS ( 'Fact Grants' )

Organizations Funded = DISTINCTCOUNT ( 'Fact Grants'[Organization ID] )

Countries Funded =
COUNTROWS ( SUMMARIZE ( 'Fact Grants', 'Dim Geography'[Country] ) )

Cities Funded =
COUNTROWS ( SUMMARIZE ( 'Fact Grants', 'Dim Geography'[Country], 'Dim Geography'[City] ) )

Average Grant = DIVIDE ( [Total Awarded], [Grants Awarded] )

Median Grant = MEDIAN ( 'Fact Grants'[Award Amount] )

Active Grants =
CALCULATE ( [Grants Awarded], 'Fact Grants'[Status] = "Active" )

Repeat Grantees =
COUNTROWS (
    FILTER (
        VALUES ( 'Fact Grants'[Organization ID] ),
        CALCULATE ( COUNTROWS ( 'Fact Grants' ) ) > 1
    )
)

Share of Total Awarded =
DIVIDE (
    [Total Awarded],
    CALCULATE ( [Total Awarded], ALLSELECTED ( 'Dim Geography' ) )
)
```

Format Total Awarded, Average Grant, and Median Grant as currency with no decimals. Format Share of Total Awarded as a percentage with one decimal.

### People reached and the adjustment rule

Reach Factor Cutoff FY holds the last fiscal year that gets the adjustment. The adjustment rule multiplies reported reach by 0.60 for grants awarded in FY2021 and FY2022, because the report form in those years counted repeat visits, and by 1.00 for later years. Figures flagged for review are left out of the adjusted measure until the grantee confirms them. The data file already has a People Reached Adjusted column, but the measure below applies the rule itself so the rule is visible in the model and easy to change.

```DAX
Reach Factor Cutoff FY = 2022

Reach Adjustment Factor = 0.60

People Reached Reported = SUM ( 'Fact Grants'[People Reached Reported] )

People Reached Adjusted =
SUMX (
    'Fact Grants',
    VAR ReportedReach = 'Fact Grants'[People Reached Reported]
    VAR AwardFY = RELATED ( 'Dim Fiscal Calendar'[Fiscal Year Number] )
    VAR IsFlagged = LEFT ( 'Fact Grants'[Reach Note], 7 ) = "Flagged"
    VAR Factor = IF ( AwardFY <= [Reach Factor Cutoff FY], [Reach Adjustment Factor], 1 )
    RETURN IF ( IsFlagged, BLANK (), ReportedReach * Factor )
)

Grants With Reach =
CALCULATE (
    [Grants Awarded],
    NOT ISBLANK ( 'Fact Grants'[People Reached Reported] ),
    LEFT ( 'Fact Grants'[Reach Note], 7 ) <> "Flagged"
)

Reach Reporting Rate = DIVIDE ( [Grants With Reach], [Grants Awarded] )

Cost per Person Reached =
DIVIDE (
    CALCULATE (
        [Total Awarded],
        NOT ISBLANK ( 'Fact Grants'[People Reached Reported] ),
        LEFT ( 'Fact Grants'[Reach Note], 7 ) <> "Flagged"
    ),
    [People Reached Adjusted]
)

Reach Held for Review =
CALCULATE ( [Grants Awarded], LEFT ( 'Fact Grants'[Reach Note], 7 ) = "Flagged" )

Final Reports Overdue =
CALCULATE ( [Grants Awarded], 'Fact Grants'[Status] = "Final report overdue" )
```

Format the reach measures as whole numbers with a thousands separator, Reach Reporting Rate as a percentage, and Cost per Person Reached as currency with two decimals.

### Community-led share

```DAX
Community-Led Dollars =
CALCULATE ( [Total Awarded], 'Dim Organization'[Community-Led] = "Yes" )

Community-Led Share = DIVIDE ( [Community-Led Dollars], [Total Awarded] )
```

### Fiscal-year time intelligence

The fiscal year ends on June 30, so every year-to-date function gets the year-end date "6/30". Fiscal quarters line up with calendar quarters (fiscal Q1 is July through September), so the quarter-to-date functions work without a year-end argument.

```DAX
Awarded FYTD =
TOTALYTD ( [Total Awarded], 'Dim Fiscal Calendar'[Date], "6/30" )

Awarded Prior FY =
CALCULATE ( [Total Awarded], SAMEPERIODLASTYEAR ( 'Dim Fiscal Calendar'[Date] ) )

Awarded Prior FYTD =
CALCULATE ( [Awarded FYTD], SAMEPERIODLASTYEAR ( 'Dim Fiscal Calendar'[Date] ) )

Change vs Prior FY = [Total Awarded] - [Awarded Prior FY]

Change vs Prior FY % = DIVIDE ( [Change vs Prior FY], [Awarded Prior FY] )

Awarded FQTD =
TOTALQTD ( [Total Awarded], 'Dim Fiscal Calendar'[Date] )

Awarded Same Quarter Prior FY =
CALCULATE ( [Awarded FQTD], SAMEPERIODLASTYEAR ( 'Dim Fiscal Calendar'[Date] ) )

Awarded Rolling 12 Months =
CALCULATE (
    [Total Awarded],
    DATESINPERIOD ( 'Dim Fiscal Calendar'[Date], MAX ( 'Dim Fiscal Calendar'[Date] ), -12, MONTH )
)

Grants FYTD =
TOTALYTD ( [Grants Awarded], 'Dim Fiscal Calendar'[Date], "6/30" )
```

Format Change vs Prior FY % as a percentage with one decimal. In a card, show the change measure with a custom format string such as `+$#,##0;-$#,##0;$0`.

### Budget

```DAX
Budget =
CALCULATE (
    SUM ( Targets[Target Value] ),
    Targets[Target Type] = "Annual budget",
    TREATAS ( VALUES ( 'Dim Fiscal Calendar'[Fiscal Year] ), Targets[Fiscal Year] ),
    TREATAS ( VALUES ( 'Dim Program'[Program ID] ), Targets[Program ID] )
)

Awarded vs Budget = [Total Awarded] - [Budget]

Budget Used % = DIVIDE ( [Total Awarded], [Budget] )
```

### Five-year goal progress

The goals cover FY2023 through FY2027 and are set for the whole fund, so these measures clear every filter with REMOVEFILTERS and then keep only the goal period. That way the goal visuals show the same numbers on every page and under any slicer selection.

```DAX
Goal Dollars Target =
CALCULATE ( SUM ( Targets[Target Value] ), Targets[Target ID] = "GOAL-1" )

Goal People Target =
CALCULATE ( SUM ( Targets[Target Value] ), Targets[Target ID] = "GOAL-2" )

Goal Community-Led Target =
CALCULATE ( SUM ( Targets[Target Value] ), Targets[Target ID] = "GOAL-3" )

Goal Period Awarded =
CALCULATE ( [Total Awarded], REMOVEFILTERS (), 'Dim Fiscal Calendar'[In Goal Period] = "Yes" )

Goal Period People Reached =
CALCULATE ( [People Reached Adjusted], REMOVEFILTERS (), 'Dim Fiscal Calendar'[In Goal Period] = "Yes" )

Goal Period Community-Led Share =
CALCULATE ( [Community-Led Share], REMOVEFILTERS (), 'Dim Fiscal Calendar'[In Goal Period] = "Yes" )

Goal Dollars Progress = DIVIDE ( [Goal Period Awarded], [Goal Dollars Target] )

Goal People Progress = DIVIDE ( [Goal Period People Reached], [Goal People Target] )

Goal Community-Led Progress =
DIVIDE ( [Goal Period Community-Led Share], [Goal Community-Led Target] )

Data As Of =
CALCULATE ( MAX ( 'Fact Grants'[Award Date] ), REMOVEFILTERS () )

Goal Years Complete =
VAR LastFY =
    LOOKUPVALUE (
        'Dim Fiscal Calendar'[Fiscal Year Number],
        'Dim Fiscal Calendar'[Date], [Data As Of]
    )
RETURN MAX ( 0, MIN ( 5, LastFY - 2023 + 1 ) )

Expected Pace = DIVIDE ( [Goal Years Complete], 5 )

Goal Dollars Status =
IF ( [Goal Dollars Progress] >= [Expected Pace], "On pace", "Behind pace" )

Goal People Status =
IF ( [Goal People Progress] >= [Expected Pace], "On pace", "Behind pace" )

Goal Community-Led Status =
IF ( [Goal Community-Led Progress] >= 1, "Target met", "Below target" )
```

Goal Years Complete assumes the latest fiscal year in the data is complete, which is true when the data is refreshed after June 30. If you refresh in the middle of a fiscal year, replace LastFY - 2023 + 1 with LastFY - 2023 so the partial year does not count.

### Populations served

A grant can serve several populations, so these measures turn on two-way filtering through the bridge table only inside the calculation.

```DAX
Awarded to Population =
CALCULATE (
    [Total Awarded],
    CROSSFILTER ( 'Bridge Populations'[Grant ID], 'Fact Grants'[Grant ID], BOTH )
)

Grants Serving Population =
CALCULATE (
    [Grants Awarded],
    CROSSFILTER ( 'Bridge Populations'[Grant ID], 'Fact Grants'[Grant ID], BOTH )
)

Population Share of Dollars =
DIVIDE ( [Awarded to Population], CALCULATE ( [Total Awarded], REMOVEFILTERS ( 'Bridge Populations' ) ) )
```

### Labels for titles and notes

```DAX
Selected Years Label =
IF (
    ISFILTERED ( 'Dim Fiscal Calendar'[Fiscal Year] ),
    CONCATENATEX ( VALUES ( 'Dim Fiscal Calendar'[Fiscal Year] ), 'Dim Fiscal Calendar'[Fiscal Year], ", ", 'Dim Fiscal Calendar'[Fiscal Year], ASC ),
    "FY2021 to FY2026"
)

Overview Subtitle = "Grants awarded in " & [Selected Years Label]

Data Note =
"Some figures need follow-up: " & [Final Reports Overdue] & " grants have a final report overdue, and "
    & [Reach Held for Review] & " reach figures are held for review."

Total Awarded Footnote =
FORMAT ( [Total Awarded], "$#,##0" ) & " across " & FORMAT ( [Grants Awarded], "#,##0" ) & " grants"
```

## Step 4: Apply the theme

Choose View, then Themes, then Browse for themes, and select Halden Table Fund Theme.json. The theme sets the five program colors in program order (Full Plate, Growing Roots, Kitchen to Career, Neighborhood Tables, Rapid Response), a light warm page background, white visual backgrounds with a soft border, and Segoe UI text. If you add the Dim Program[Program Name] field to a legend, sort it by Sort Order so the colors stay with the right program.

## Step 5: Build the pages

Use a 16:9 canvas (1280 by 720). Put a header bar across the top of every page (a rectangle in #45204F, 1280 by 64) with the fund name in white on the left and the Data As Of date on the right. Below the header, place the three slicers in one row from left to right: Fiscal Year (tile style, multi-select with Ctrl), Program Name (dropdown), and Region (dropdown). Use View, then Sync slicers, to sync all three across the first five pages. Leave the Guide page out of the sync.

### Page 1: Executive Overview

| Visual | Fields | Position and notes |
|---|---|---|
| Card (new card visual) | Total Awarded, with Total Awarded Footnote as the reference label | Left, 400 by 190. Fill #45204F, white text. This is the one large number on the page. |
| Six cards | Grants Awarded, Organizations Funded, Countries Funded, People Reached Adjusted, Average Grant, Community-Led Share | Two rows of three to the right of the hero card. Add reference labels for Active Grants, Repeat Grantees, Cities Funded, Reach Reporting Rate, Median Grant, and Goal Community-Led Target. |
| Three bullet-style bars (clustered bar chart) | Goal Dollars Progress, Goal People Progress, Goal Community-Led Progress, with Expected Pace as an X-axis constant line | Lower left. Turn off interactions from every slicer (Format, then Edit interactions, then None) so the goal bars always show the full portfolio. Add the three Status measures as data labels or as a small table beside the bars. |
| Clustered column chart | X: Dim Fiscal Calendar[Fiscal Year]; Y: Total Awarded; tooltip: Grants Awarded and Change vs Prior FY % | Lower right. One color (#5E2E6B), data labels on. |
| Card or text box | Data Note | Under the column chart, with a light gold background (#FBF0D8). |

### Page 2: Investment History

| Visual | Fields | Position and notes |
|---|---|---|
| Stacked column chart | X: Fiscal Year; Y: Total Awarded; Legend: Dim Program[Program Name] | Left two thirds. Total labels on. Tooltip: Grants Awarded and Fact Grants[Program Name as Awarded]. |
| Table | Program Crosswalk[Program Name as Awarded], [First FY Used], [Last FY Used], [Current Program Name] | Right third. Sort by Program ID, then First FY Used. |
| Matrix | Rows: Dim Program[Program Name]; Columns: Fiscal Year; Values: Total Awarded, Budget, Awarded vs Budget | Full width at the bottom. Use conditional font color on Awarded vs Budget (red below zero). |
| Bookmark toggle (optional) | A second matrix with Fact Grants[Program Name as Awarded] on rows | Two bookmarks and a button let the reader switch between current names and names as awarded, which is what the checkbox does in the browser version. |

### Page 3: Geography

| Visual | Fields | Position and notes |
|---|---|---|
| Filled map (or Azure Maps with a choropleth layer) | Location: Dim Geography[Country]; Color saturation: Total Awarded | Left two thirds. Diverging off, gradient from #EADCEE to #3E1A4A. Add a second bubble layer with Latitude, Longitude, and Total Awarded if you use Azure Maps. |
| Clustered bar chart | Y: Region; X: Total Awarded | Right third, sorted descending, data labels on. |
| Matrix | Rows: the Location hierarchy; Values: Grants Awarded, Organizations Funded, Total Awarded, Share of Total Awarded, People Reached Adjusted, Average Grant | Full width at the bottom. Turn on the plus and minus icons (Row headers, then +/- icons) so readers can drill from region to city. Add data bars to Share of Total Awarded. |

### Page 4: Populations and Focus Areas

| Visual | Fields | Position and notes |
|---|---|---|
| Clustered bar chart | Y: Bridge Populations[Population Served]; X: Awarded to Population; tooltip: Grants Serving Population | Top left. Add a subtitle that says a grant counts under every population it serves. |
| Clustered bar chart | Y: Fact Grants[Primary Focus Area]; X: Total Awarded | Top right, color #2F8F6A. |
| Matrix as a heat map | Rows: Primary Focus Area; Columns: Program Name; Values: Total Awarded | Bottom left. Use background color conditional formatting from #F3ECF5 to #4B2257, and white font for the darkest cells. |
| Line chart | X: Fiscal Year; Y: Community-Led Share; constant line at 0.40 | Bottom right. Label the constant line "Goal 40%". |

### Page 5: Grant Explorer

| Visual | Fields | Position and notes |
|---|---|---|
| Text slicer or search-enabled slicer | Dim Organization[Organization Name] | Top left. Turn on search in the slicer settings. |
| Two dropdown slicers | Fact Grants[Status], Fact Grants[Primary Focus Area] | Beside the search slicer. |
| Table | Grant ID, Organization Name, Program Name, Program Name as Awarded, Fiscal Year, Country, City, Primary Focus Area, Award Amount, People Reached Adjusted, Status | Full page. Sort by Award Amount descending. Use conditional icons on Status. Readers export with More options, then Export data, which matches the Export to CSV button in the browser version. |

### Page 6: Guide

Use text boxes for the metric definitions, the adjustment rule, the program name note, and the data note. Copy the wording from the Guide tab of the browser version so both versions say the same thing.

## Step 6: Build the print-friendly quarterly summary page

The quarterly summary is a separate page meant to be printed or exported to PDF for the giving committee. The sample PDF in this folder shows the finished layout for FY2026 Q4.

- Page size: Letter, landscape. In Format, then Canvas settings, choose Custom and set 1056 by 816 pixels.
- Background: white, with no page background color, so it prints cleanly.
- Filters: add Dim Fiscal Calendar[Fiscal Year Quarter] as a page-level filter set to one quarter, and hide the filter pane before printing. No slicers appear on this page.
- Header: the fund name, the quarter and its dates, and the Data As Of date, with a 3-pixel rule in #45204F under it.
- KPI row (five cards across the top): Awarded FQTD with Awarded Same Quarter Prior FY as the reference label; Awarded FYTD with Budget Used %; Grants FYTD with Organizations Funded and Countries Funded; new grantees this fiscal year (count of Dim Organization rows where First Funded FY equals the selected year); and Community-Led Share for the fiscal year.
- Left column: a matrix of programs with Awarded FQTD, Awarded FYTD, Budget, Budget Used %, and Awarded vs Budget, followed by a table of the eight largest grants awarded in the quarter (Top N filter on Award Amount).
- Right column: the three goal progress bars with the expected pace line, a gold note box built from the Data Note measure, and a clustered column chart of Total Awarded by Fiscal Quarter for the current and prior fiscal year.
- Footer: the data note and a line explaining that fiscal years run July 1 to June 30.

Keep the KPI values in measures that respond to the page filter (Awarded FQTD and Awarded FYTD both read the latest date in the filtered quarter), so changing the page filter to another quarter updates the whole page.

## Step 7: Check your numbers

With no filters applied, the rebuilt report should match these values from the browser version.

| Measure | Expected value |
|---|---|
| Total Awarded | $50,459,000 |
| Grants Awarded | 640 |
| Organizations Funded | 349 |
| Countries Funded | 21 |
| People Reached Reported | 2,395,392 |
| People Reached Adjusted | 1,752,796 |
| Reach Reporting Rate | 77.3% |
| Cost per Person Reached | $22.28 |
| Community-Led Share | 32.6% |
| Total Awarded for FY2026 | $9,301,500 |
| Budget for FY2026 | $9,200,000 |
| Goal Period Awarded | $37,160,000 (81% of the $46 million goal) |
| Goal Period People Reached | 1,112,007 (65% of the 1.7 million goal) |
| Expected Pace | 80% |