What is a “Power BI” Dashboard?
Microsoft Power BI is a suite of business intelligence (BI), reporting, and data visualisation products. Power BI allows data from multiple sources to be combined and analysed, in real-time, and presented visually.
Aptem have harnessed the capabilities of Power BI to develop a suite of custom-built “Dashboards” designed to provide you with finger-tip access to the data that your business needs across a range of key business areas. These dashboards have been fully embedded into the Aptem platform meaning they require no additional specialist knowledge or software to use them.
Who has the funding dashboard been designed for?
The funding dashboard has been designed to be used by anyone in the business who may need sight of funding values and predictions and who have been given the authority to do so. Typically, this could be people in, but not limited to, the following key business roles:
- Finance Director/Manager
- Managing Director
- Board members
- Operations Director/Manager
- MIS Director/Manager
Which information does the funding dashboard contain?
The funding dashboard has been created to provide indicative funding information based on the data currently held within your Aptem platform. The Dashboard provides indicative funding amounts broken down into the following sections:
- Headline
- Year-To-Date (YTD) totals and summary
- Funding Data - detailed learner funding information and summaries by Month and standard/ framework
- Funding details
- PMR/ TNP Report
- Missing Aims, Weighting & Errors
Where is the data extracted from?
The data contained in the funding dashboard is taken directly from the complex databases that sit behind the screens you see within Aptem. Every piece of data entered by you into Aptem is securely stored in these databases and are used to power the dashboards, therefore, ensuring your dashboard is always showing up-to-date information (when loaded and/or refreshed, maximum of one refresh per hour period).
An example of the key data and filters used in each of the separate pages (tabs) of the finance report are detailed in the following sections. Each page contains a number of data assumptions, hard-coded filters and user definable filters which will be explained where possible.
For reference:
- Data assumptions - Pre-determined assumptions made about the data to specify inclusion or exclusion from the dashboard.
- Hard-coded filters - These are filters coded in during the development of the dashboards and are not editable or changeable by the end user.
- Filters - These are page levels filters that can be adjusted by the end user to further shape the data.
- Where the term “(calculated)” is used, this means the figures in this visual have been calculated by means of manual DAX programming language and are not standalone values directly available via the OData API feed (i.e. these are not explicit fields/values captured in the Aptem platform).
Note
If no group filter is applied to the data table, learners will appear for each group they are in. All measures will only count the learner once.
Individual tab details
Tab: Missing Aims, Weighting & Errors
Tab: Headline
Filters include:
- UKPRN
- Academic Year
Visuals included:
- Funding Summary per month by FIS funding line
- Total per month
- Year to date
- Year Total
Tab: Year-To-Date
Filters include:
- UKPRN
- Age band (calculated)
- Category
- Apprenticeship
- Other
- Standard/ Framework
- ILR Signature/ Status
Visuals included:
- Monthly funding values (calculated) – consisting of:
- “In month” total
- On programme payment (OPP)
- Math
- English
- Learning support assistance (LSA)
- Projected outcome (predicted payment)
- Outcome payment (on confirmed end date)
- Balancing payment
- 16-18 Provider incentive
- 16-18 Employer incentive
- 16-18 framework uplift
- 16-18 Traineeship
- 19-24 Traineeship
- 16-18 Study Programme
- 19-24 Study Programme
- Functional Skills
- Adult Skills
- ADL
- Total YTD
- Potential Outcome
- YTD Values
Data assumptions:
- Learners have an active, Valid, ILR record (i.e. Would be included in a batch file submission to ESFA)
Hard-coded filters:
- Learner type is “User”
- Programme Type is 2,3,20,21,22,23,24 or 25
Tab: Funding Data
Filters include:
- UKPRN
- Months
- ILR Aima by learning status
- ILR Status
- Aim Type
- Age band (calculated)
- Latest AIm
- Live Status of Aims
- Case Owner
- Programme Type
- This includes Traineeships and AEB Study Programmes
- On Programme Funding, consisting of;
- Category
- Standard/ Framework Name
- Type (Standard/ Framework)
- Number of Learners on Programme
- Current Months Funding
- Next Months Funding Values
- Groups
- Academic Year
Visuals included:
- Total funding by month (calculated)
- Learner aims, status and funding data - Note: Only the latest programme aim will be displayed per Learner
- Learner Funding details - consisting of:
- Learner ID
- Learner reference
- Learner ref number
- Learner ULN
- First name
- Last name
- Full name
- D.O.B
- Age at start of programme
- Age band
- Employer
- Post code
- Learning start postcode
- Delivery postcode
- Case owner name
- Gender Code
- Gender
- Ethnicity Code
- Ethnicity
- Partner UKPRN
- Funding model
- Funding model TYPE
- ACT
- ACT Description
- S.O.F
- S.O.F Name
- FIS Funding Line
- Total YTD
- Potential Outcome Payments
- Aim Type
- Aim type name
- Program ID
- Programme name
- Name
- Ref Number
- Programme Type
- Programme Type Name
- Level
- Framework Code
- Standard Code
- Pathway code
- Planned OTJ Hours
- Withdrawal reason
- ILR episode status
- EPA org ID
- EPA organisation
- Status
- Learning Status
- Outcome
- Outcome Name
- Grade
- Restart indicator
- Original start date
- Start date
- Planned end date
- Actual end date
- Achievement date
- Special education needs indicator
- EHCP indicator
- Learner support funding indicator, start and end dates
- Primary health problem
- Disabled students allowance indicator
- Eligibility for enhanced funding indicator
- Advanced learner loan indicator
- Total assessment price (TNP2)
- Total training price (TNP2)
- Residual Assessment Cost
- Residual Training Cost
- TNP Sum
- Aim Value
- Cofunded Value
- Planned Enrichment Hours
- Planned Learning Hours
- Main Aim Flag
- Latest Aim Flag
- SSA1
- SSA2
- Date of employment Status (latest)
- Aims ILR status
- Exclude from batch
- Past End Date
- Root Group
- Group level 1, 2,3,4,5,6 etc
- Learner Funding details - consisting of:
Key Performance Indicators:
- Total Leavers
- Leavers with full achievement
- Leavers without achievement
- Learners 'on break in learning'
- Learners still on the programme but past the planned end date
- Learners currently on programme and 'in funding'
- Number of learners with no ILR or privately funded
- Learners with excluded unknown ILR and one or more aims
- Average number of days on programme
- Average days since last learning plan activity
- Learners with no learning plan activity in the last 60 days
Data assumptions:
- Learners have an active, valid, ILR record (i.e. would be included in a batch file submission to ESFA)
Hard-coded filters:
- Learner type is “User”
- Programme Type is 2,3,20,21,22,23 or 24
User definable filters:
- None
Tab: Funding Details
Filters:
- UKPRN
- Include Restarts
- Months
- Funding Type
- ILR aims by learning status
- Live status of aims
- Case Owner
- Programme Type
- Individual Aims excluded from the batch
- Academic Year
Visuals included:
- Claim component breakdown by month (current and next three months), (calculated) - consisting of:
- “In month” total
- On programme payment (OPP)
- Math
- English
- Learning support assistance (LSA)
- Projected outcome (predicted payment)
- Outcome payment (on confirmed end date)
- Balancing payment
- 16-18 Provider incentive
- 16-18 Employer incentive
- 16-18 framework uplift
- 16-18 Traineeship
- 19-24 Traineeship
- 16-18 Study Programme
- 19-24 Study Programme
- Functional Skills
- Adult Skills
- ADL
- Funding Revenue by month (calculated) – consisting of:
- “In month” total
- On programme payment (OPP)
- Math
- English
- Learning support assistance (LSA)
- 16-18 uplift
- Projected outcome (predicted payment)
- Outcome payment (on confirmed end date)
- ULN
- Current programme
- Category
- Standard / Framework name
- Full name
Key Performance Indicators:
- Current Month
- Provider Weighting
- Unmatched Aims
Data assumptions:
- Learners have an active, Valid, ILR record (i.e. Would be included in a batch file submission to ESFA)
Hard-coded filters:
- Learner type is “User”
- Programme Type is 25
- All ILRs, all episodes, all aims
Calculations:
- Total learners including leavers
- Count of aims from total learners
- Number of 'live' learners
- Number of 'live' aims from 'live' learners
- Number of 'live' aims excluded from batch
- Number of 'live' learners unfunded/past end
- Number on learning break
- 'Live' learners with Aim type 1
- 'Live' learners without Aim type 1
- Learner Aims, Status and Funding Table
Tab: PMR/TNP Report
Filters:
- UKPRN
- Subscription Status
- Type
Visuals included:
- TNP total this month
- PMR total this month
- TNP total last month
- PMR total last month
- TNP/PMR Summary by month
- Detailed TNP/PMR by learner, data and type
Tab: Missing Aims, Weighting & Errors
Filters:
- UKPRN
- Aim Title
Visuals included:
- Learners with aims but unmatched
- Provider weighting
- Possible Funding Code Errors
- Missing Programme Type (trainees)
- Missing Programme Type (AEB)
- Missing SoF
- Missing Aim Title
- Unmatched Aims
Power BI reports in Aptem console
Administrators with the Power BI Funding Report role can view the Power BI Funding report in Aptem console. Please note that even after the role has been assigned, it takes some time to take affect and show the reports. The report cannot be seen immediately.
To view Power BI reports in Aptem console, click Power BI Reports on the left menu.
On the Reports screen, you can view the Funding report. If you have access to more than one report, you can use the navigation menu on top to navigate to the report you want to see.
You can view the information across the different tabs on each report.
Refreshing the Power BI report
To refresh a report, you can use the Refresh button. Reports cannot be refreshed within an hour of the last refresh. The time of the last update, and the next available refresh time is shown.