How to Produce Departmental Completion Percentages

Note: These instructions should only be followed in Step 7 if it is necessary to display the percentage completion rate for your department or division.

1. Replace Email Address with Department Name

   • In the Rows area, untick Email Address to remove it.
   • Tick Department Name so departments become the row labels.
   • Drop down on Column Labels and tick Yes so the table shows counts for Yes and No.

2. Change the distinct count into percentages

   • Click any number inside the PivotTable.
   • In the PivotTable Fields, open the value field drop‑down on Distinct Count of SSO and select Value Field Settings.
   • In the Show Values As tab, drop down on No Calculation and select % of Row Total.
   • Click OK.
   • To adjust decimal points, right‑click any value, select Number Format, which defaults to Percentage, and choose 0 or 1 decimal place.
   • Your table now shows the proportion of completions across the whole dataset.

3. Add actual totals alongside the percentages

   • Drag SSO into Values a second time.
   • Open Value Field Settings for this second value.
   • Set it to Distinct Count again.
   • Ensure Show Values As is set to No Calculation so it displays real numbers.
   • (Optional) Rename the column header to something clearer, such as Actual Total.

4. Remove the Grand Totals

   • Click anywhere inside the PivotTable.
   • Go to PivotTable Options, select the Totals & Filters tab.
   • Untick Show grand totals for rows.
   • This removes unnecessary extra totals and keeps the table clean.

5. Tidy the layout

   • Adjust column widths to make the table easy to read.
   • Rename column headings if needed (e.g., Completed - Yes %, Completed - Yes – Total).

Note

For divisional users, the exported Power BI dataset includes a field for Level 3 organisational units, but does not include a field for Level 2 entities.

If you need to report or aggregate data at Level 2, you will first need to add a new column in your exported dataset to map each Level 3 entity to its corresponding Level 2 entity. Once created, use this new column in your pivot table in place of the existing ‘Department name’ field.