Découvrez Power Pivot : Définition, Concepts et Mode d'Emploi pour débutants

Quand il s'agit d'élaborer des tableaux de bord sur Excel, les tableaux croisés dynamiques sont quasiment obligatoires, et très utiles. Quand les tableaux croisés dynamiques montrent des limites sur Excel, notamment pour faire des calculs bien précis, ou encore des calculs qui impliquent plusieurs tableaux, on passe à Power Pivot ou Power BI.

Comment utiliser Power Pivot ?

Que faut-il pratiquer en particulier pour pouvoir utiliser cet outil à son plein potentiel ?

Cette vidéo démontre comment Power Pivot vient briser les limites des Tableaux Croisés Dynamiques (TCD) classiques d'Excel.

Les concepts clés présentés :

• L'ajout au Modèle de Données : comment envoyer vos tableaux Excel directement dans le moteur Power Pivot. C’est cette étape qui permet de gérer des volumes de données bien plus importants qu'un TCD standard.
• La fin du RECHERCHEV : L'auteur illustre la puissance des Relations. Au lieu de ramener péniblement des colonnes d'un tableau à l'autre avec des formules, on crée un lien visuel (une relation) entre la table de ventes et la table clients, par exemple.
• L'introduction aux Mesures DAX : Plutôt que de simplement glisser des champs dans les valeurs du TCD, la vidéo explique comment créer des mesures (formules DAX). Ces mesures sont plus robustes et permettent de réaliser des calculs qui seraient impossibles ou très instables dans un Excel classique.
• Un TCD multi-tables : Le résultat final est un Tableau Croisé Dynamique capable d'afficher des données provenant de plusieurs tableaux différents simultanément, de manière fluide et automatique.

Les concepts clés de Power Pivot à connaître

1. Des tables structurées

Pour ceux qui ont utilisé des tableaux croisés dynamiques, vous aurez probablement remarqué qu'il s'agit principalement de faire glisser les colonnes du tableau source vers les champs ligne, colonne ou valeur, pour faire apparaître les calculs que nous cherchons à visualiser.

Seulement voilà : cela suppose que nous avons déjà un tableau bien structuré et « prêt à l'emploi ».

Dans la vie quotidienne, ce n'est pas si simple, et nous avons souvent besoin d'aller chercher les données à leur source, les transformer pour leur donner la structure en colonnes, et les envoyer vers Power Pivot. L'outil qui permet de faire tout cela est Power Query.

Le but et le fonctionnement de Power Query : il est capable de repérer les données d'un fichier, d'un dossier, d'une base de données en lignes, etc., tant que celui-ci est accessible par l'ordinateur.

Puis, c'est à nous d'exécuter les manipulations nécessaires afin de retravailler la structure des données, pour qu'elles deviennent utilisables par la suite sur Power Pivot.

C'est bien là la partie de Power Query à pratiquer, afin d'utiliser Power Pivot dans les meilleures conditions.

2. Les relations entre les tables

Sur Power Pivot, il y a deux types de relations possibles entre deux tables différentes, les autres étant tout bonnement refusées par le logiciel. Celles-ci sont les relations 1–1 (lire « un vers un ») et 1–* (lire « un vers plusieurs »).

Que représentent ces chiffres ?

On considère qu'une table est reliée à l'autre via une colonne de chaque côté. Dans au moins un des côtés de cette relation, la colonne ne doit comporter aucun doublon. C'est donc dans ce contexte où on peut dire que la table du côté du 1 sert de dimension à la table du côté du plusieurs.

C'est bien grâce à ces liens que plus tard, on peut créer des mesures et des tableaux croisés dynamiques qui peuvent prendre en compte plusieurs tables simultanément.

Parfois, il est également nécessaire de faire plusieurs liens entre deux tables (exemple classique : relier un calendrier à des factures via leur date d'émission et via leur date de paiement).

Dans ce cas-là, il ne peut y avoir qu'une seule relation active à la fois, et donc la deuxième relation sera en pointillés, activable via les mesures avec la fonction USERELATIONSHIP.

C’est donc la cardinalité de la relation qui définit ce que sont les faits et les dimensions.

‍

3. Tables de faits et tables de dimensions

Maintenant, intéressons-nous aux types de tables : sur Power Pivot (et Power BI) il y en a 2, et il est important de les différencier.

Ces types sont : les tables de faits et les tables de dimensions.

Définissons ces deux types de tables complémentaires :

• Table de faits : ce sont des tables où se trouvent des observations, des événements, des faits de manière générale. Ce sont donc ces tables qui vont être la source et le centre de tous les calculs et les indicateurs que nous souhaitons extraire, voire visualiser. Par exemple, une table avec des factures qui inclut les montants et des dates de facture serait une table de faits.
• Table de dimensions : le rôle de la table de dimensions est d'apporter du contexte à la table de faits, et donc des informations complémentaires aux tables de faits.

Une autre utilité pratique de la table des dimensions est aussi de servir de « pont » entre deux tables de faits, qui va donc les relier indirectement (dans 99% des cas, il ne faut jamais relier les tables de faits directement, sous peine de calculer des faux indicateurs).

Un exemple très commun de table de dimensions est le calendrier, qui va permettre d'ancrer dans le temps des informations liées à des événements sur une table de faits.

Petite devinette : considérant ce qui vient d'être décrit, laquelle de ces deux tables est une table de faits, et laquelle est une table de dimensions ?

Ici donc, l'intérêt est de bien visualiser quelles tables de données correspondent à quel type de table, pour pouvoir monter un modèle de données fiable et performant.

Une fois les tables définies et reliées, il est temps de commencer à mesurer les indicateurs que nous cherchons à dégager.

‍

4. Les mesures

Nous atteignons probablement le sujet le plus vaste et potentiellement le plus complexe de Power Pivot.

Voici le scénario : nous avons importé toutes les tables, dont les faits et les dimensions en passant par Power Query et nous avons créé tous les liens nécessaires entre les tables.

Maintenant, nous souhaitons personnaliser à fond les calculs que nous souhaitons afficher dans nos tableaux croisés dynamiques par la suite. Nous sommes un niveau au-dessus de juste changer les paramètres des champs de valeur pour faire afficher la moyenne plutôt que la somme d'une colonne.

C'est à ce moment-là que nous utilisons le langage DAX (Data Analysis Expressions) pour préparer le calcul de tous nos indicateurs, et faire parler les données. Ces formules que nous créons s'appellent mesures et seront placées par la suite dans le champ valeurs de notre tableau croisé dynamique.

Comme tout langage de formules, il y aura forcément des formules plus souvent utilisées que d'autres, avec en star des fonctions : CALCULATE (voir l'article sur le top 12 des fonctions en DAX).

Cette fonction permet notamment de modifier le contexte des calculs (et donc les rendre plus pointus). Cela nous mène au dernier aspect à bien comprendre sur Power Pivot : la notion de contexte.

‍

5. Le contexte

À partir du moment où nous commençons à créer des mesures, notamment celles avec CALCULATE (pas seulement), il faudra apprendre à jongler avec le contexte.

Rapide définition du contexte : c'est l'environnement dans lequel une formule en DAX est évaluée. Il y a, sur Power Pivot, deux environnements qui existent : le contexte de ligne et le contexte (non pas de colonne) de filtre. Ces deux contextes deviennent alors contrôlables et permettent des analyses très pointues.

a. Le contexte de ligne

Commençons par le contexte de ligne : on peut imaginer cela comme la ligne considérée dans une table. Si nous faisons une analogie avec Excel, ce serait comme un calcul qui se répéterait ligne par ligne sur un tableau.

Donc, dans le cas où nous créons une colonne calculée, nous créons un contexte de ligne automatiquement, puisqu'un calcul sera refait dans chaque ligne.

Le contexte de ligne n'est pas seulement utilisé dans les colonnes calculées, mais aussi dans les mesures, notamment quand nous utilisons des fonctions itératives (qui font des calculs ligne par ligne) dont les fonctions comme SUMX, AVERAGEX, MAXX, FILTER (donc qui vérifient les tables ligne par ligne)… font partie.

‍

b. Le contexte de filtre

Le contexte de filtre ne correspond pas directement aux lignes des tables mais aux filtres qui y sont appliqués. Ces filtres sont appliqués via plusieurs intermédiaires qui peuvent être : un segment sur Excel, le découpage par le champ lignes ou le champ colonnes d'un tableau croisé dynamique ou encore des conditions appliquées via la fonction CALCULATE.

C'est donc, en soi, comment, à partir d'une simple somme (par exemple), les valeurs sont bien découpées selon le champ lignes dans un tableau croisé dynamique, plutôt que d'avoir un total qui se répète à toutes les lignes. Il est donc important de bien visualiser les contextes de filtre et de ligne pour comprendre comment fonctionnent les mesures.

Cas pratique : Calculer une marge avec Power Pivot et le DAX

Maintenant que vous avez compris la théorie, passons à l'action ! Dans cette vidéo, nous traitons un besoin fondamental pour tout gestionnaire : calculer une marge brute de manière dynamique.

Oubliez les calculs manuels dans des colonnes Excel qui alourdissent vos fichiers.

Ici, nous utilisons l'intelligence du modèle de données pour obtenir un résultat professionnel et automatisé.

Ce que vous allez apprendre dans ce tutoriel :

• Créer vos premières mesures : Comment écrire une formule DAX simple pour calculer le Chiffre d'Affaires et le Coût des Ventes.
• La logique de la Marge : Apprendre à soustraire ces mesures pour obtenir votre rentabilité.
• Le dynamisme du TCD : Voyez comment votre marge s'ajuste instantanément selon que vous analysez un produit, une catégorie ou une période donnée.

Formation Power Pivot pour aller plus loin

Vous avez compris la théorie, mais vous voulez gagner du temps et éviter les erreurs de débutant ? Notre formation Power Pivot & DAX a été conçue pour transformer les utilisateurs d'Excel en véritables architectes de données.

Que vous souhaitiez automatiser vos tableaux de bord ou maîtriser les formules DAX les plus complexes (comme les fonctions de "Time Intelligence" pour comparer vos performances d'une année sur l'autre), nous vous accompagnons pas à pas.

Pourquoi choisir notre parcours ?

• Pratique avant tout : Apprenez à structurer des modèles en étoile robustes et performants.
• Maîtrise du DAX : Domptez les fonctions CALCULATE, FILTER et bien d'autres pour créer des indicateurs sur-mesure.
• Financement : Bonne nouvelle, cette formation est éligible au CPF, vous permettant de monter en compétence sans reste à charge !