# Community Benefit Dashboard: Power BI Rebuild Guide

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

This guide rebuilds the Community Benefit Dashboard in Power BI Desktop from the files in the `data` folder. The HTML dashboard shows the finished layout, so keep it open beside Power BI while you work. Plan on about 45 minutes.

## Files

| File | What it holds |
|---|---|
| Community Benefit Dashboard - Data Tables.xlsx | One table per sheet, plus a README sheet |
| csv folder | The same tables as CSV files, if you prefer a folder source |
| Community Benefit Theme.json | Colors, fonts, and visual defaults for the report |

## 1. Load the tables

Use Get data, then Excel workbook, and select every sheet except README. Set these data types in Power Query before you load:

| Table | Columns and types |
|---|---|
| DimHospital | HospitalKey (text), HospitalCode (text), HospitalName (text), Setting (text), LicensedBeds (whole number) |
| DimFiscalYear | FiscalYearKey (whole number), FiscalYear (text), StartDate (date), EndDate (date) |
| DimScheduleHLine | LineKey (text), Part (text), LineNumber (text), LineLabel (text), ShortLabel (text), LineGroup (text), DataSource (text), SortOrder (whole number) |
| DimProgram | ProgramKey (text), ProgramName (text), PriorityKey (text), StandardCategory (text), LoggingBasis (text), IsActivityProgram (whole number) |
| DimPriorityArea | PriorityKey (text), PriorityArea (text), SortOrder (whole number) |
| DimPosition | PositionKey (text), Position (text), JobFamily (text), BaseHourlyWage (fixed decimal), LoadedHourlyRate (fixed decimal) |
| FactCommunityBenefit | FiscalYearKey (whole number), HospitalKey, ProgramKey, LineKey, PriorityKey (text), PersonsServed (whole number), TotalExpense, OffsettingRevenue, NetBenefit (fixed decimal), IsActivityProgram (whole number) |
| FactHospitalExpense | FiscalYearKey (whole number), HospitalKey (text), TotalExpense, BadDebtExpense, ExpenseForPercent (fixed decimal) |
| FactActivityDetail | RowID (text), FiscalYearKey (whole number), HospitalKey, ProgramKey, LineKey, PositionKey (text), ActivityDate (date), Hours (decimal), StaffCost, IndirectCost, DirectNonStaffCost, TotalExpense, OffsettingRevenue, NetBenefit (fixed decimal), PersonsServed (whole number) |

FactCommunityBenefit has one row per fiscal year, hospital, and program. The finance-supplied lines (7a, 7b, 7c, and 7g) appear as programs with keys F7a, F7b, F7c, and F7g and IsActivityProgram = 0. FactActivityDetail holds the FY2026 staff-time rows behind the activity-based programs and is there for drill-through, so the main page does not need it.

## 2. Relationships

Create these relationships in Model view. All are many-to-one with single-direction filtering from the dimension to the fact.

| From (many) | To (one) |
|---|---|
| FactCommunityBenefit[HospitalKey] | DimHospital[HospitalKey] |
| FactCommunityBenefit[FiscalYearKey] | DimFiscalYear[FiscalYearKey] |
| FactCommunityBenefit[ProgramKey] | DimProgram[ProgramKey] |
| FactCommunityBenefit[LineKey] | DimScheduleHLine[LineKey] |
| FactCommunityBenefit[PriorityKey] | DimPriorityArea[PriorityKey] |
| FactHospitalExpense[HospitalKey] | DimHospital[HospitalKey] |
| FactHospitalExpense[FiscalYearKey] | DimFiscalYear[FiscalYearKey] |
| FactActivityDetail[HospitalKey] | DimHospital[HospitalKey] |
| FactActivityDetail[ProgramKey] | DimProgram[ProgramKey] |
| FactActivityDetail[LineKey] | DimScheduleHLine[LineKey] |
| FactActivityDetail[PositionKey] | DimPosition[PositionKey] |
| FactActivityDetail[FiscalYearKey] | DimFiscalYear[FiscalYearKey] |

Do not relate DimPriorityArea to DimProgram. DimProgram carries PriorityKey for reference, but a second path to DimPriorityArea would make the model ambiguous.

Set DimScheduleHLine[ShortLabel] to sort by SortOrder, and DimPriorityArea[PriorityArea] to sort by SortOrder.

## 3. Calculated columns

Add these to DimScheduleHLine so the category chart can show Part II as a single bar:

```
Dashboard Category =
IF (
    DimScheduleHLine[Part] = "Part II",
    "Part II  Community building",
    DimScheduleHLine[LineNumber] & "  " & DimScheduleHLine[ShortLabel]
)

Dashboard Category Sort =
IF ( DimScheduleHLine[Part] = "Part II", 99, DimScheduleHLine[SortOrder] )
```

Sort Dashboard Category by Dashboard Category Sort.

## 4. Measures

Create an empty table named Measures (Enter data, one blank column) and add each measure below to it. Format currency measures as currency with no decimals, and percent measures as percentage with one decimal.

```
Total CB Expense = SUM ( FactCommunityBenefit[TotalExpense] )

Offsetting Revenue = SUM ( FactCommunityBenefit[OffsettingRevenue] )

Net Benefit = SUM ( FactCommunityBenefit[NetBenefit] )

Net Community Benefit (Part I) =
CALCULATE ( [Net Benefit], DimScheduleHLine[Part] = "Part I" )

Community Building Net (Part II) =
CALCULATE ( [Net Benefit], DimScheduleHLine[Part] = "Part II" )

Expense for Percent = SUM ( FactHospitalExpense[ExpenseForPercent] )

Percent of Total Expense =
DIVIDE ( [Net Community Benefit (Part I)], [Expense for Percent] )

People Served =
CALCULATE (
    SUM ( FactCommunityBenefit[PersonsServed] ),
    FactCommunityBenefit[IsActivityProgram] = 1
)

Activities and Programs =
CALCULATE (
    COUNTROWS (
        SUMMARIZE (
            FactCommunityBenefit,
            FactCommunityBenefit[HospitalKey],
            FactCommunityBenefit[ProgramKey]
        )
    ),
    FactCommunityBenefit[IsActivityProgram] = 1
)

Program Net Benefit =
CALCULATE ( [Net Benefit], FactCommunityBenefit[IsActivityProgram] = 1 )

Hospitals Reporting =
CALCULATE (
    DISTINCTCOUNT ( FactCommunityBenefit[HospitalKey] ),
    FactCommunityBenefit[IsActivityProgram] = 1
)

Share of Program Net =
DIVIDE (
    [Program Net Benefit],
    CALCULATE ( [Program Net Benefit], ALLSELECTED ( DimProgram ) )
)

Selected FY = SELECTEDVALUE ( DimFiscalYear[FiscalYearKey] )

Net Part I Prior Year =
VAR fy = [Selected FY]
RETURN
    CALCULATE (
        [Net Community Benefit (Part I)],
        REMOVEFILTERS ( DimFiscalYear ),
        DimFiscalYear[FiscalYearKey] = fy - 1
    )

Net Part I Change % =
DIVIDE ( [Net Community Benefit (Part I)] - [Net Part I Prior Year], [Net Part I Prior Year] )

Percent of Total Expense Prior Year =
VAR fy = [Selected FY]
RETURN
    CALCULATE (
        [Percent of Total Expense],
        REMOVEFILTERS ( DimFiscalYear ),
        DimFiscalYear[FiscalYearKey] = fy - 1
    )

Percent Change (pts) =
IF (
    NOT ISBLANK ( [Percent of Total Expense Prior Year] ),
    ( [Percent of Total Expense] - [Percent of Total Expense Prior Year] ) * 100
)

People Served Prior Year =
VAR fy = [Selected FY]
RETURN
    CALCULATE ( [People Served], REMOVEFILTERS ( DimFiscalYear ), DimFiscalYear[FiscalYearKey] = fy - 1 )

People Served Change % =
DIVIDE ( [People Served] - [People Served Prior Year], [People Served Prior Year] )

Activities Prior Year =
VAR fy = [Selected FY]
RETURN
    CALCULATE ( [Activities and Programs], REMOVEFILTERS ( DimFiscalYear ), DimFiscalYear[FiscalYearKey] = fy - 1 )

Activities Change = [Activities and Programs] - [Activities Prior Year]

Part Color =
IF ( SELECTEDVALUE ( DimScheduleHLine[Part] ) = "Part II", "#C4841C", "#008A7E" )

Latest FY =
CALCULATE ( MAX ( FactCommunityBenefit[FiscalYearKey] ), REMOVEFILTERS ( DimFiscalYear ) )

Trend Bar Color =
IF ( MAX ( DimFiscalYear[FiscalYearKey] ) = [Latest FY], "#008A7E", "#A8D4CE" )

Hospital Bar Label =
FORMAT ( [Net Community Benefit (Part I)] / 1000000, "$#,0.0" ) & "M  ("
    & FORMAT ( [Percent of Total Expense], "0.0%" ) & ")"

KPI Net Subtitle =
IF (
    ISBLANK ( [Net Part I Prior Year] ),
    "First year shown",
    FORMAT ( [Net Part I Change %], "+0.0%;-0.0%" ) & " vs prior year. Part II community building adds "
        & FORMAT ( [Community Building Net (Part II)] / 1000, "$#,0" ) & "K."
)
```

## 5. Page and visuals

Set the page size to Custom, 1600 by 1000 pixels, and apply the theme (View, Themes, Browse for themes, then pick Community Benefit Theme.json).

| Visual | Type | Fields and settings |
|---|---|---|
| Fiscal year | Slicer (tile style) | DimFiscalYear[FiscalYear]. Single select. Select FY2026 before you save. |
| Hospital | Slicer (tile style) | DimHospital[HospitalCode]. Leave Single select off and turn on Select all. With nothing selected, the page shows the whole system. |
| Net community benefit (Part I, line 7k) | Card | [Net Community Benefit (Part I)]. Add [KPI Net Subtitle] as the reference label. |
| Percent of total expense | Card | [Percent of Total Expense]. Reference label: [Percent Change (pts)] formatted "+0.0 pts". |
| People served (program encounters) | Card | [People Served]. Reference label: [People Served Change %]. |
| Activities and programs | Card | [Activities and Programs]. Reference label: [Activities Change]. |
| Net benefit by Schedule H category | Clustered bar | Y-axis: DimScheduleHLine[Dashboard Category]. X-axis: [Net Benefit]. Bar color: Conditional formatting, Field value, [Part Color]. Data labels on, display units Auto. |
| Net benefit by hospital | Clustered bar | Y-axis: DimHospital[HospitalName]. X-axis: [Net Community Benefit (Part I)]. Data labels: Custom label with [Hospital Bar Label]. Edit interactions so the Hospital slicer does not filter this visual, then click a bar to cross-filter the page. |
| Three-year trend | Clustered column | X-axis: DimFiscalYear[FiscalYear]. Y-axis: [Net Community Benefit (Part I)]. Column color: Field value, [Trend Bar Color]. Edit interactions so the Fiscal year slicer does not filter this visual. |
| Program net benefit by health priority area | Clustered bar | Y-axis: DimPriorityArea[PriorityArea]. X-axis: [Program Net Benefit]. Sort descending by the measure. Visual filter: PriorityArea is not "Not assigned (finance line)". |
| Top programs by net benefit | Table | DimProgram[ProgramName], DimScheduleHLine[LineNumber], DimPriorityArea[PriorityArea], [Hospitals Reporting], [People Served], [Program Net Benefit]. Visual filter: DimProgram[IsActivityProgram] = 1, plus Top N = 9 on ProgramName by [Program Net Benefit]. Turn on data bars for [Program Net Benefit] with fill #A8D4CE. |

Add a text box at the bottom with this line: "Sample data. All names, organizations, and figures are fictional."

## 6. Check your numbers

With FY2026 and the whole system selected, the report should show these values, which also match the SUMMARY sheet of the Community Benefit Reporting Workbook.

| Measure | Expected value |
|---|---|
| Net Community Benefit (Part I) | $219,827,674 |
| Percent of Total Expense | 10.3% |
| Community Building Net (Part II) | $565,212 |
| People Served | 53,299 |
| Activities and Programs | 98 |
| Net Benefit for line 7b (Medicaid) | $126,350,000 |

If a value is off, check that every key column is typed as text and that the relationships in step 2 point from the fact tables to the dimensions.

## Notes on the rules

Line labels follow the 2025 Schedule H (Form 990). Percent of total expense divides net community benefit expense by total expense less bad debt expense, which follows the instructions for Part I, line 7, column (f). Source: Internal Revenue Service, Instructions for Schedule H (Form 990) (2025), irs.gov/instructions/i990sh.