Training Metrics: Chart Overdue Training by Month

This page contains examples of charting overdue training by month using Grand Avenue’s internal charting tool and Excel. Questions to help determine the appropriate training record search criteria are at How do I select the correct data to show my training metrics?

Collect Training Data

Training metrics using Grand Avenue’s internal charting tool

Click Generate Chart for Results to create a chart of training records.

Open

Select “Generate Chart for Results”

Select the chart options shown below and click Generate Chart.

Open

Training metrics using Excel

Charts generated in Excel from exported data take more time to create but have a full range of flexibility in how the data is presented.

Click the Export Results to Excel link on the search results page.

Open

Select “Export Results to Excel”

On the next page, click the Export Results to Excel button. Open the exported CSV file and save it as an Excel Workbook (*.xlsx). In the Excel workbook, click Insert → PivotTable. When prompted, place the PivotTable on a new worksheet.

Open

Exported Search Results

Select the PivotTable Fields shown below.

1. Drag the Months (Training Completed Date) field to the Rows box.

2. Drag the Training Overdue? field to the Columns box.

3. Drag the Training Overdue? field to the Values box.

Open

Here’s the PivotTable generated from the fields selected above.

Open

Select the Pivot Table and click Insert → PivotChart. Here’s the corresponding PivotChart.

Open

Training metrics using PowerBI

Use the instructions in the previous section to export training record search results to Excel or use Connector Read to access training records directly from GAS. Import the training records to PowerBI.

Here are some example PowerBI charts:

Open

Overdue Training by Month

Open

Overdue Training by Month with Percentage of Training Overdue

1. Here are the settings for the first chart:

   1. Records: TrainingRecords

   2. X-axis:: TrainingCompletedDate

   3. (Column) y-axis: Count of TrainingOverdue

   4. (Column) Legend: TrainingOverdue

   5. Measures: None

   6. Filters:

      1. AssignedToUser=Yes

      2. TrainingCompleted=Yes

      3. TrainingOverdue=Yes or No

2. The second chart is the same data as the first chart but also includes a line showing the percent of training overdue each month. In additional to the settings shown above, the line requires the following settings:

   1. Line y-axis: Percent of Overdue Training

   2. Measures:

      1. Training Records = COUNT(TrainingRecords[TrainingOverdue])

      2. Count of TrainingOverdue for Yes = CALCULATE(     COUNTA('TrainingRecords'[TrainingOverdue]),     'TrainingRecords'[TrainingOverdue] IN { "Yes" }
      3. Percent of Overdue Training = DIVIDE([Count of TrainingOverdue for Yes],[Training Records])