Pour concevoir des indicateurs et KPI (Key Performance Indicators) à l’aide de Power Pivot dans Excel, vous allez utiliser une mesure créée avec le langage DAX (Data Analysis Expressions), permettant ainsi la visualisation de performances clés.
Toutes les données nécessaires pour faire l’exercice se trouvent dans TP Concevoir des indicateurs et KPI.
À l’issue des manipulations, vous aurez produit le classeur Excel PIB_Lab36.xlsx.
La correction de l’exercice se trouve dans TP Concevoir des indicateurs et KPI.
Les manipulations de Concevoir des mesures doivent être terminées avec succès.
Connecter une source de données
Premièrement, commencez par ouvrir le classeur Excel PIB_Lab34.xlsx qui contient les données.
Ouvrir Power Pivot
Ensuite, cliquez sur le ruban Power Pivot pour ouvrir la fenêtre de gestion de Power Pivot.
Arranger le tableau croisé dynamique
Puis, cliquez sur la feuille Feuil1 pour la sélectionner. Normalement, elle est sélectionnée par défaut.
Puis, déplacez le champ Gamme sur Lignes et mettez-le en-dessous du champ Canal.
Créer un indicateur PKI
Ensuite, pour créer un KPI, vous devez d’abord avoir une mesure de base sur laquelle le KPI sera basé.
C’est le cas du modèle de ce classeur, qui contient déjà une mesure DAX, qui s’intitule Mt Vente.
En particulier, celle-ci calcule le total des ventes en utilisant la formule :
=SUM(Ventes[Montant HT])
Cette mesure est disponible pour être utilisée directement dans Excel.
Ensuite, cliquez sur le ruban Accueil, puis sur Indicateurs de performance clés et enfin sur Nouvel indicateur de performance clé (KPI).
Dans la boîte de dialogue Indicateur de Performance clé (KPI), choisissez la mesure que vous voulez utiliser comme valeur de base pour votre KPI.
Dans notre scénario, il s’agit de la mesure Mt Vente.
Ensuite, définissez la cible pour votre KPI. Vous pouvez choisir une valeur fixe ou une mesure existante qui agira comme la cible.
Exemple avec Total des Ventes
Par exemple, si votre mesure est le Total des Ventes (Mt Vente), la cible peut être un objectif de vente spécifique.
Dans notre scénario, cochez l’option Valeur absolue et indiquez la valeur de 300000, puis faites une tabulation.
Vous remarquerez que les valeurs de seuils d’états se sont mises à jour automatiquement avec les valeurs : 120000, 240000. En effet, par défaut, la valeur du seuil d’état bas correspond à 40 % de la valeur cible, tandis que la valeur du seuil d’état haut correspond à 80 % de la valeur cible.
Vous pouvez ajuster précisément les valeurs des seuils d’état en les modifiant directement. Cela peut être fait en saisissant une valeur précise pour le seuil d’état ou en bougeant le curseur qui se trouve sous la valeur du seuil d’état.
Puis, choisissez comment visualiser le KPI en sélectionnant les icônes pour les différentes plages de performance (par exemple, vert pour atteindre ou dépasser la cible, jaune pour proche de la cible, rouge pour en dessous de la cible).
Ensuite, cliquez sur OK pour créer le KPI.
Votre KPI est aussitôt affiché dans le tableau croisé dynamique.
Par défaut, les valeurs numériques du KPI s’affichent : -1,0,1.
Cependant, dans ce scénario, vous préférez afficher les icônes de couleur.
En particulier, dans le volet des Champs de tableau croisé dynamique (à droite), décochez l’indicateur État sous le KPI Mt Vente, puis cochez le à nouveau aussitôt : les icônes s’affichent.
Créer une autre mesure
Vous allez créer une nouvelle mesure.
En particulier, pour créer une nouvelle mesure, sélectionnez la table dans la vue Données de Power Pivot où vous souhaitez ajouter la mesure. Les mesures peuvent être créées directement dans les tables pour une organisation logique.
Dans notre scénario, il s’agit de la table Ventes.
Puis cliquez sur Mesures puis sur Nouvelle mesure. Cela ouvre la boîte de dialogue pour créer une mesure.
Ensuite, dans la boîte de dialogue Nouvelle mesure, donnez un nom significatif à votre mesure, à la place de mesure 1. Il faut choisir un nom descriptif qui reflète clairement ce que mesure la formule.
Dans ce scénario, elle s’intitule Mt Canaux
Ensuite, dans la zone de formule, entrez votre expression DAX pour calculer la mesure.
En particulier, créez une mesure calculant le total des ventes en utilisant la formule :
= CALCULATE([Mt Vente], ALL(Gammes))
Dans cette formule, [Mt Vente] est la mesure précédemment calculée et ALL(Gammes) permet de faire la somme des ventes, toutes gammes confondues.
Ensuite, après avoir entré votre formule, cliquez sur le bouton Vérifier la formule : le message Aucune erreur dans la formule s’affiche avec une icône en forme de pastille verte.
Si une icône en forme de triangle jaune s’affiche, lisez simplement le message qui l’accompagne. Prenez le temps d’en comprendre le sens, puis corrigez l’erreur.
Pendant la création de la mesure, il est conseillé de procéder immédiatement à son formatage avant de la sauvegarder.
Pour cela, sélectionnez l’option Devise (Currency) dans le menu déroulant Catégorie. La fenêtre de dialogue qui apparaît ensuite vous présentera l’euro (€) comme monnaie avec une précision de deux décimales.
Conservez ces réglages par défaut et validez en cliquant sur OK pour enregistrer votre mesure.
Il peut arriver que la mesure s’ajoute automatiquement dans le tableau croisé dynamique : cela dépend où est le focus. Si cela arrive, décochez la mesure Mt Canaux dans le volet des Champs de tableau croisé dynamique (à droite).
Créer un autre KPI
Vous allez créer un autre KPI, axé sur des objectifs liés aux canaux.
En particulier, cliquez sur Indicateurs de performance clés et enfin sur Nouvel indicateur de performance clé (KPI).
Dans la boîte de dialogue Indicateur de Performance clé (KPI), choisissez la mesure que vous voulez utiliser comme valeur de base pour votre KPI.
Dans notre scénario, il s’agit de la mesure Mt Canaux.
Ensuite, définissez la cible pour votre KPI. Vous pouvez choisir une valeur fixe ou une mesure existante qui agira comme la cible.
Dans notre scénario, cochez l’option Valeur absolue et indiquez la valeur de 100000, puis faites une tabulation.
Ensuite, cliquez sur OK pour créer le KPI.
Votre KPI est aussitôt affiché dans le tableau croisé dynamique. Comme ce n’est pas l’objectif, dans le volet des Champs de tableau croisé dynamique (à droite), décochez le champ État du KPI Mt Canaux.
Utiliser l’autre KPI
Maintenant, vous allez utiliser le KPI nouvellement créé dans un autre type de tableau croisé dynamique.
Premièrement, accédez au menu Gérer dans le groupe Modèle de données . Cela vous permet d’accéder au modèle de données.
Puis, cliquez sur la partie inférieure du menu Tableau croisé dynamique pour ouvrir un sous-menu proposant plusieurs options.
Sélectionnez l’option Tableau croisé dynamique aplati (tout en bas).
Dans la boîte de dialogue qui s’affiche, conservez les paramètres par défaut et cliquez sur le bouton OK.
La feuille Feuil2 s’affichera.
Dans le volet des Champs de tableau croisé dynamique (à droite), cochez le champ Canal (sous Canaux) et déplacez-le dans la section Colonnes.
Puis, cochez le champ Date de vente (sous Ventes).
Immédiatement après, vous remarquerez que les champs Date de vente (année), Date de vente (trimestre), Date de vente (mois) apparaissent et sont sélectionnés par défaut.
Au cours du prochain exercice, vous verrez comment ces champs ont été automatiquement générés dans le modèle pour la table Ventes.
Ensuite, décochez les champs Date de vente (trimestre) et Date de vente (mois) qui se sont ajoutés automatiquement.
Puis, décochez également Date de vente, ne conservant que Date de vente (année), puis déplacez Date de vente (année) dans la section Lignes.
Cliquez sur la petite flèche à gauche du KPI Mt Canaux pour ouvrir et afficher la liste des mesures et indicateurs.
Sous le KPI Mt Canaux, cochez les mesures et champs : Valeur (Mt Canaux) et État.
Ensuite, formatez la mesure Mt Canaux en format Monétaire en cliquant sur sa petite flèche, puis sur Paramètres des champs de valeurs, et enfin sur le bouton Format de nombre. Sélectionnez Monétaire et validez par OK deux fois.
Pour vérifier que ce KPI est indépendant des gammes, déplacez le champ Gamme (sous Gammes) dans la section Filtres.
Ensuite, dans le tableau croisé dynamique aplati, sélectionnez différentes valeurs du filtre Gamme et vérifiez que ni les valeurs ni les KPI ne sont modifiés.
Enregistrer son travail
Puis, enregistrez votre classeur Excel avec le nom PIB_Lab36.xlsx
Enfin, fermez le classeur Excel PIB_Lab36.xlsx
La manipulation est terminée.
Laisser un commentaire