Start a conversation ↗

Description

Excel Power Pivot: Build a Data Model, Create Measures and Publish a Dashboard

Move beyond the limits of the PivotTable by building a genuine data model in Excel

  • 2 days — 14 h
  • In-person or virtual
  • Foundation
  • Up to 6 participants

PivotTables quickly reach their limits: several files to reconcile, cascading lookup formulas that slow the workbook down, indicators that cannot be calculated without intermediate columns. The file becomes heavy and no one other than its author knows how to develop it further.

These two days introduce a different way of working in Excel: loading several tables into the data model, creating relationships, writing measures and publishing an interactive dashboard. Each participant builds a complete dashboard from the source files provided, then refreshes it for a new period.

Learning objectives

  • Load several sources into the Excel data model
  • Create relationships between tables and check their consistency
  • Write DAX measures to calculate the required indicators
  • Build an interactive dashboard with slicers and timelines
  • Organise the dashboard refresh for each new reporting period

What makes this programme different

A complete dashboard is built from file loading through to final layout
The lookup formulas participants use today are replaced by relationships
The refresh process is tested by swapping the source files at the end of the session

Programme

1From PivotTable to Data Model

Understanding what Power Pivot changes

  • Limits of PivotTables when working with multiple sources
  • Enabling and getting started with the add-in
  • Loading tables and the concept of a data model
  • Difference between a worksheet table and a model table
  • Organising source files and naming conventions

2Relationships and Model Structure

Linking tables rather than copying columns

  • Fact tables and reference tables
  • Creating relationships and selecting the key
  • Cardinality and filter direction
  • Building a date table
  • Checking the model and detecting duplicate keys

3Measures and Calculations

Calculate once and reuse everywhere

  • Difference between a calculated column and a measure
  • Basic aggregation measures and formatting of results
  • Ratio calculations and percentage of total
  • Period-on-period comparison using the date table
  • Naming and documenting measures

4Dashboard and Refresh

Delivering a tool others can use

  • Building PivotTables from the model
  • Slicers, timelines and synchronisation across worksheets
  • PivotCharts and dashboard layout
  • Refreshing sources and managing file paths
  • Sharing the workbook and limitations to be aware of

Who is it for

Experienced Excel users, management controllers, executive assistants, administrators and anyone producing recurring dashboards.

Prerequisites

Confident use of Excel and the ability to build a PivotTable.

Dates & locations

36 scheduled dates between November 2026 and December 2027. Seats are confirmed in the order enquiries are received.

November 2026

December 2026

January 2027

February 2027

March 2027

April 2027

May 2027

June 2027

September 2027

October 2027

November 2027

December 2027

None of these dates suit you? We open additional sessions on request, and any programme can be run privately for your team.

Practical details

Before the programme
Online positioning questionnaire. Your development objectives are shared with the trainer, who tailors the practical case studies to your context.
Teaching methods
Theoretical input, workshops and practical case studies. Digital course materials and method sheets provided.
Assessment
Multiple-choice tests and role-play exercises. Assessment of learning at the start and end of the programme, with immediate and 60-day follow-up evaluations.
After the programme
One year of access to the e-learning platform. Self-assessment of the skills acquired and a 30-day follow-up session with your trainer.
How to register
Registration online or on the basis of a quotation.
Lead time
11 working days after confirmation of registration.
Accessibility
Accessible to people of determination. Contact our accessibility coordinator to design a suitable solution: contact@mpf-academy.ae
Start dates
Rolling intake: in addition to the scheduled sessions, this programme can start on request.