Concevoir des mesures dans Excel

Pour concevoir des mesures à l’aide de Power Pivot dans Excel, vous allez utiliser le langage DAX (Data Analysis Expressions) pour créer des formules qui résument vos données, permettant ainsi des analyses approfondies.

Toutes les données nécessaires pour faire l’exercice se trouvent dans TP Concevoir des mesures.

À l’issue des manipulations, vous aurez produit le classeur Excel PIB_Lab34.xlsx.

Rappel

Une mesure est une formule de calcul dynamique.

Une mesure est créée à l’aide d’une formule DAX (Data Analysis Expressions).

Généralement, une formule d’une mesure utilise des fonctions comme COUNT, SUM, etc.

Cependant, il est possible de définir librement sa formule.

Une mesure nommée peut être passée comme argument à d’autres mesures.

Le nom de la mesure est avant la formule.

Exemple d’une mesure :

• Total des Frais = SUM([Dépenses])

Connecter une source de données

Premièrement, commencez par ouvrir le classeur Excel Ventes_simple.xlsx qui contient les données.

Activer Power Pivot

Ensuite, activez l’onglet Power Pivot dans le ruban Excel en allant dans le ruban Fichier, puis en cliquant sur le menu Options puis dans le menu Compléments.

Dans le menu déroulant Gérer (en bas), sélectionnez le choix Compléments COM et cliquez sur le bouton Atteindre.

Enfin, cochez la case à côté de Microsoft Office Power Pivot for Excel et cliquez sur le bouton OK.

Ouvrir Power Pivot

Ensuite, une fois l’onglet Power Pivot activé, cliquez dessus pour ouvrir la fenêtre de gestion de Power Pivot.

Puis, dans le groupe Tables, cliquez sur le menu Ajouter au modèle de données pour importer votre table Ventes dans le modèle de données Power Pivot.

Recommencez cette manipulation pour importer les 2 autres tables : Canaux, Gammes.

Afficher Diagramme

Ensuite, après avoir importé vos tables dans Power Pivot, sélectionnez le menu Vue de diagramme dans l’onglet Accueil de Power Pivot.

Cela ouvre une fenêtre où vous pouvez voir les relations existantes, si elles existent, ou en créer de nouvelles.

Afin de mieux comprendre la suite, vous allez agrandir la table Ventes pour voir tous les champs de cette table.

Pour ce faire, avec la souris, tirez légèrement le bas de la table, jusqu’à faire disparaître l’ascenseur vertical. Dès que l’ascenseur a disparu, arrêtez.

La table Ventes joue un rôle pivot car elle contient les valeurs numériques qui vont être analysées.

Elle contient aussi les identifiants qui vont permettre de relier les tables.

Comme la table Ventes joue un rôle pivot, disposez cette table au milieu de la vue Diagramme, à l’aide de la souris, puis disposez les 2 autres tables de chaque côté.

Créer des relations

Pour créer des mesures, il est nécessaire que le modèle ne soit pas vide et qu’il existe des relations entre les tables.

Notamment, vous allez commencer à créer une relation entre deux tables.

Le principe est de sélectionner la table et la colonne dans votre première table qui correspond à la clé dans la deuxième table.

Assurez-vous que les colonnes que vous reliez contiennent des données correspondantes et uniques pour garantir l’intégrité de la relation.

Notamment, pour créer une relation entre la table Ventes et la table Canaux, glissez le champ Canal de la table Ventes sur le champ ID de la table Canaux.

Power Pivot crée la relation et vous pouvez voir la relation que vous venez de créer : la relation est symbolisée par un trait orienté.

Par ailleurs, cette relation est quantifiée. Elle est de type 1 à Plusieurs. En effet, pour 1 Canal, il existe plusieurs Ventes.

Ensuite, répétez le processus pour créer une relation entre la table Ventes et la table Gammes, en glissant le champ Gamme de la table Ventes sur le champ ID de la table Gammes.

Enfin, fermez la vue Diagramme, en cliquant sur la croix en haut et à droite : vous revenez sur Power Pivot.

Créer une mesure

Ensuite, 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.

Notez que cette boite de dialogue ne s’affiche que s’il existe des relations entre les tables et que le modèle n’est pas vide.

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 Vente.

Ensuite, dans la zone de formule, entrez votre expression DAX pour calculer la mesure.

Créer une mesure pour calculer un total

En particulier, créez une mesure calculant le total des ventes en utilisant la formule :

=SUM(Ventes[Montant HT])

Dans cette formule, Ventes est le nom de votre table et Montant HT est le nom de la colonne contenant les montants des ventes.

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. Bien qu’il soit possible de modifier le format d’une mesure ultérieurement, le faire dès sa création permet d’économiser du temps.

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.

La mesure est désormais disponible pour être utilisée dans des rapports croisés dynamiques ou d’autres analyses dans Excel.

Utiliser la mesure

Notamment, l’objectif est d’avoir un tableau croisé dynamique qui ventile la somme des montants HT par canaux de ventes et gammes de produits.

Pour utiliser ces relations dans un tableau croisé dynamique, cliquez sur le menu Gérer, dans le groupe Modèle de données. Cela vous permet d’accéder au modèle de données.

Dans le ruban Accueil, cliquez sur la partie haute du menu Tableau croisé dynamique.

Ensuite, dans la boîte de dialogue Tableau croisé dynamique, gardez les options par défaut, puis cliquez sur le bouton OK : la feuille Feuil1 s’affiche.

Puis, dans le volet des Champs de tableau croisé dynamique (à droite), cochez sur la petite flèche à gauche de la table Ventes, pour l’ouvrir et afficher la liste des champs et mesures.

Ensuite, cochez la mesure Mt Vente.

Une mesure est symbolisée par fx.

Ensuite, formatez en format Monétaire la mesure Mt Vente, en cliquant sur sa petite flèche puis Paramètres des champs de valeurs puis sur le bouton Format de nombre, en sélectionnant le choix Monétaire et en validant par OK deux fois.

Vous obtenez le total général des montants HT, grâce à la mesure Mt Vente.

Ensuite, vous allez le répartir par canaux de vente.

Notamment, dans le volet des Champs de tableau croisé dynamique, cochez le champ Canal (sous Canaux) et le champ Gamme (sous Gammes).

Enfin, déplacez le champ Gamme dans Colonnes.

Vous obtenez un tableau croisé dynamique qui ventile les montants HT par canaux et gammes, grâce à la mesure Mt Vente.

Intérêt de la mesure

Par ailleurs, remarquez que le tableau croisé dynamique obtenu est exactement identique à celui de l’exercice précédent sur la définition des relations entre tables.

Cette similitude est à la fois normale et attendue.

En effet, dans ce cas pédagogique spécifique, la mesure a été conçue pour être strictement équivalente à la somme des montants hors taxe.

Ainsi, il est tout à fait logique (et rassurant) que les résultats concordent.

Cependant, vous pourriez légitimement vous demander quel est l’intérêt de créer une mesure si le résultat reste inchangé.

Le prochain exercice sur la conception d’indicateurs et de KPI vous fournira un exemple simple et clair qui illustrera la valeur ajoutée d’avoir une mesure définie.

En effet, pour élaborer un indicateur clé de performance (KPI), l’existence d’une mesure est indispensable. La création d’un KPI sans mesure n’est tout simplement pas envisageable.

D’autres exemples, peut-être moins évidents à première vue, seront abordés ultérieurement pour mettre en lumière de façon concrète les avantages des mesures dans l’analyse des données.

Enregistrer son travail

Puis, enregistrez votre classeur Excel avec le nom PIB_Lab34.xlsx

Enfin, fermez le classeur Excel PIB_Lab34.xlsx

La manipulation est terminée.