# Community Survey Results: Power BI Rebuild Guide

This guide lists what is needed to rebuild the Community Survey Results dashboard in Power BI Desktop. The HTML dashboard in the `dashboard` folder shows the target layout. Plan on about an hour.

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

## 1. Load the data

Get Data > Excel workbook > `data/Community Survey - Power BI Data.xlsx`. Load every sheet.

| Table | Grain | Key columns |
|---|---|---|
| Responses | One row per respondent (1,412 rows) | resp_id (text), area, age_group, gender, race_ethnicity, household_income, self_rated_health, unmet_need, open_response, weight |
| Concerns | One row per concern a respondent selected | resp_id, concern_code |
| Barriers | One row per barrier a respondent selected | resp_id, barrier_code |
| Statements | One row per agreement statement a respondent answered | resp_id, statement_code, score (1 to 5) |
| Themes | One row per coded open-ended theme | resp_id, theme |
| Dim Concern, Dim Barrier, Dim Statement, Dim Score | Lookup tables | code and label columns |

In Power Query, set `resp_id` to Text in every table. Keep `weight` as Decimal Number.

Multi-select questions are stored in long tables (one row per answer) because this is the cleanest way to count "choose all that apply" questions in Power BI without fourteen separate yes or no columns.

## 2. Relationships

| From (many) | To (one) | Direction |
|---|---|---|
| Concerns[resp_id] | Responses[resp_id] | Single (Responses filters Concerns) |
| Barriers[resp_id] | Responses[resp_id] | Single |
| Statements[resp_id] | Responses[resp_id] | Single |
| Themes[resp_id] | Responses[resp_id] | Single |
| Concerns[concern_code] | Dim Concern[concern_code] | Single |
| Barriers[barrier_code] | Dim Barrier[barrier_code] | Single |
| Statements[statement_code] | Dim Statement[statement_code] | Single |
| Statements[score] | Dim Score[score] | Single |

Keep all filters single direction. The demographic slicers sit on Responses and flow down to the answer tables, while the denominators stay on Responses so a concern or barrier on an axis does not shrink its own base.

## 3. Weighting switch

Create a disconnected table with Enter Data, named `Weighting`, with one column `Option` and two rows: `Unweighted` and `Weighted`. Use it as a single-select slicer.

## 4. DAX measures

Put these in a `_Measures` table.

```
Is Weighted = SELECTEDVALUE ( Weighting[Option], "Unweighted" ) = "Weighted"

Respondents = DISTINCTCOUNT ( Responses[resp_id] )

Base = IF ( [Is Weighted], SUM ( Responses[weight] ), [Respondents] )

Concern Count =
IF ( [Is Weighted],
     SUMX ( Concerns, RELATED ( Responses[weight] ) ),
     COUNTROWS ( Concerns ) )

Concern % = DIVIDE ( [Concern Count], [Base] )

Concern % All Respondents =
CALCULATE ( [Concern %], REMOVEFILTERS ( Responses ) )

Barrier Count =
IF ( [Is Weighted],
     SUMX ( Barriers, RELATED ( Responses[weight] ) ),
     COUNTROWS ( Barriers ) )

Barrier % = DIVIDE ( [Barrier Count], [Base] )

Statement Count =
IF ( [Is Weighted],
     SUMX ( Statements, RELATED ( Responses[weight] ) ),
     COUNTROWS ( Statements ) )

Score Share =
DIVIDE ( [Statement Count],
         CALCULATE ( [Statement Count], REMOVEFILTERS ( 'Dim Score' ) ) )

Score Share Diverging =
VAR s = SELECTEDVALUE ( 'Dim Score'[score] )
RETURN SWITCH ( TRUE (),
    s <= 2, -[Score Share],
    s = 3, BLANK (),
    [Score Share] )

Agree % =
DIVIDE ( CALCULATE ( [Statement Count], Statements[score] >= 4 ), [Statement Count] )

Disagree % =
DIVIDE ( CALCULATE ( [Statement Count], Statements[score] <= 2 ), [Statement Count] )

Neither % =
DIVIDE ( CALCULATE ( [Statement Count], Statements[score] = 3 ), [Statement Count] )

Fair or Poor Health % =
DIVIDE ( CALCULATE ( [Base], Responses[self_rated_health] IN { "Fair", "Poor" } ), [Base] )

Unmet Need % =
DIVIDE ( CALCULATE ( [Base], Responses[unmet_need] = "Yes" ), [Base] )

Fair or Poor Health % All = CALCULATE ( [Fair or Poor Health %], REMOVEFILTERS ( Responses ) )

Unmet Need % All = CALCULATE ( [Unmet Need %], REMOVEFILTERS ( Responses ) )

Open-Ended Base =
CALCULATE ( [Base], Responses[open_response] <> "" )

Theme Count =
IF ( [Is Weighted],
     SUMX ( Themes, RELATED ( Responses[weight] ) ),
     COUNTROWS ( Themes ) )

Theme % = DIVIDE ( [Theme Count], [Open-Ended Base] )

Share of All Respondents = DIVIDE ( [Respondents], CALCULATE ( [Respondents], REMOVEFILTERS ( Responses ) ) )

Top Concern =
VAR t = TOPN ( 1, VALUES ( 'Dim Concern'[concern] ), [Concern %], DESC )
RETURN CONCATENATEX ( t, 'Dim Concern'[concern], ", " )

Top Concern % = MAXX ( VALUES ( 'Dim Concern'[concern] ), [Concern %] )

Small Group Message =
IF ( [Respondents] < 50,
     "Only " & [Respondents] & " respondents match these filters, so treat these percentages with care.",
     BLANK () )

Filter Summary =
VAR a = IF ( ISFILTERED ( Responses[area] ), SELECTEDVALUE ( Responses[area], "Several areas" ), "All areas" )
VAR g = IF ( ISFILTERED ( Responses[age_group] ), SELECTEDVALUE ( Responses[age_group], "Several age groups" ), "All ages" )
RETURN "Showing: " & a & ", " & g & IF ( [Is Weighted], ". Results are weighted.", ". Results are unweighted." )
```

Format every percent measure as Percentage with 1 decimal place.

## 5. Page layout (1600 by 1000)

```
+----------------+----------------------------------------------------------------+
| Title          | Filter Summary (card)   Small Group Message (card, gold fill)  |
| Area slicer    +-------------+-------------+-------------+-------------------+  |
| Age slicer     | Respondents | Fair/Poor % | Unmet Need %| Top Concern       |  |
| Gender slicer  +-------------+-------------+-------------+-------------------+  |
| Race slicer    | Top health concerns         | Community conditions           |  |
| Income slicer  | (bar)                       | (diverging stacked bar)        |  |
| Weighting      +-----------------------------+--------------------------------+  |
| Sample note    | Barriers to care (bar)      | Open-ended themes (bar)        |  |
+----------------+-----------------------------+--------------------------------+--+
```

| Visual | Type | Fields | Notes |
|---|---|---|---|
| Slicers | Dropdown slicers | Responses[area], [age_group], [gender], [race_ethnicity], [household_income]; Weighting[Option] | Place in a dark plum (#3B2F63) side panel with white text. |
| KPI cards | Card (new) | Respondents with Share of All Respondents as reference label; Fair or Poor Health % with Fair or Poor Health % All; Unmet Need % with Unmet Need % All; Top Concern with Top Concern % | Callout font Bitter, plum. |
| Top health concerns | Clustered bar | Y: Dim Concern[concern]; X: Concern %, Concern % All Respondents | Sort by Concern % All Respondents so bars keep the same order when filters change. Color this view plum (#3B2F63) and all respondents light lilac (#C9C1E6). |
| Community conditions | Stacked bar | Y: Dim Statement[statement]; X: Score Share Diverging; Legend: Dim Score[score_label] | Colors: Strongly disagree #B23A5A, Disagree #E8A6B8, Agree #A8B9E0, Strongly agree #3F5FA8. Add Neither % to the tooltip, because Power BI cannot split one segment across the center line. Sort by Agree %. |
| Barriers to care | Bar | Y: Dim Barrier[barrier]; X: Barrier % | Use a lighter lilac (#B8AEDB) for "No barriers in the past 12 months" through conditional formatting on barrier_code = "B10". |
| Open-ended themes | Bar | Y: Themes[theme]; X: Theme % | Gold (#D99A2B). |

## 6. Theme

Import `rebuild/Community Survey - Power BI Theme.json` through View > Themes > Browse for themes. It sets the plum, lilac and gold palette, the Bitter title font, white visual backgrounds and a light page background (#FAF9FC). If Bitter is not installed, Power BI falls back to Segoe UI.

## 7. Check before publishing

1. With no filters and unweighted results, Respondents should read 1,412, Fair or Poor Health % should read 23.7% and Unmet Need % should read 14.7%.
2. Mental health (depression, anxiety) should be the top concern at 31.6% unweighted and 35.0% weighted.
3. With Area = North Rural, Respondents should read 258 and the top concern should be Access to health care.
4. With Age group = 18-24, the small group message should appear (37 respondents).