Partager via

Créer une mesure dans un tableau croisé dynamique ou un graphique croisé dynamique

  • Article

Une mesure est un calcul que vous créez afin de mesurer un résultat par rapport à d'autres facteurs d'analyse pertinents, par exemple des caractéristiques relatives au temps, au lieu, à l'organisation ou au produit. Il existe plusieurs manières de créer une mesure, mais selon la façon dont vous prévoyez d'utiliser la mesure, toutes les approches n'ont pas la même efficacité. Plus précisément, si vous créez un classeur utilisable en tant que modèle de données dans une application de création de rapports, veillez à créer uniquement des mesures explicites, comme indiqué dans les sections ci-dessous.

Création de mesures utilisable dans les modèles de données

Les mesures que vous créez dans un classeur PowerPivot peuvent être utilisées dans les rapports que vous générez dans Power View et d'autres applications de création de rapports. Par ailleurs, elles peuvent coexister avec d'autres mesures ou calculs que vous créez directement dans le rapport. Pour qu'une mesure s'affiche dans le rapport en tant que calcul prédéfini, vous devez la créer dans un classeur PowerPivot en tant que mesure explicite. Une mesure explicite est une mesure que vous créez manuellement. Elle diffère des mesures implicites qui sont générées automatiquement par Excel lorsque vous ajoutez des champs à la zone Valeurs d'un tableau croisé dynamique.

Création de mesures utilisables dans Excel

Lorsque vous créez une mesure utilisable dans Excel, vous devez tout d'abord ajouter un tableau croisé dynamique ou un graphique croisé dynamique à votre classeur PowerPivot. Vous pouvez ensuite créer des mesures via l'une des approches suivantes :

  • Créez une mesure implicite en faisant glisser un champ de la liste de champs PowerPivot vers la zone Valeurs. Si vous faites glisser un champ numérique, la mesure implicite est calculée à l'aide d'une agrégation SUM. Si vous faites glisser un champ de texte, la mesure est calculée à l'aide d'une agrégation COUNT. Vous pouvez modifier une mesure implicite afin de remplacer l'agrégation utilisée dans le calcul, par exemple MIN, MAX ou DISTINCTCOUNT.

    [!REMARQUE]

    Les mesures implicites sont faciles à créer mais elles sont soumises à davantage de contraintes qu'une mesure explicite. Les mesures implicites ne peuvent pas être renommées, déplacées ou utilisées dans d'autres tableaux croisés dynamiques ou graphiques croisés dynamiques dans le classeur. En outre, comme les mesures implicites sont basées sur un champ existant, la suppression de ce champ entraîne également la suppression de la mesure implicite associée. Enfin, les mesures implicites ne peuvent utiliser que le format de données intégré à l'agrégation ; elles ne prennent pas en charge la large gamme de formats de données accessibles aux mesures explicites.

  • Créez une mesure explicite manuellement, à l'aide du bouton Nouvelle mesure sur le ruban PowerPivot.

  • Créez une mesure explicite manuellement dans la fenêtre PowerPivot, dans la Zone de calcul, en tapant un nom de mesure et une formule dans la zone de formule d'une cellule.

Lorsque vous ajoutez la mesure, la formule est évaluée pour chaque cellule dans la zone Valeurs du tableau croisé dynamique. Comme un résultat est créé pour chaque combinaison d'en-têtes de lignes et de colonnes, le résultat de la mesure peut être différent dans chaque cellule du tableau croisé dynamique.

Exemple : création d'une mesure explicite qui utilise une agrégation simple

Cet exemple inclut des données relatives aux vélos provenant de la base de données AdventureWorks. Pour plus d'informations sur l'obtention de l'exemple de classeur, consultez Obtenir des exemples de données pour PowerPivot. Pour plus d'informations sur les formules, consultez Générer des formules pour les calculs.

Cet exemple illustre deux modes de création d'une mesure explicite. En premier lieu, vous devez créer une mesure dans la fenêtre PowerPivot, dans une Zone de calcul qui affiche toutes les mesures définies dans le modèle. Vous devez ensuite ajouter un tableau croisé dynamique ou un graphique croisé dynamique à votre classeur PowerPivot, puis utiliser la boîte de dialogue Paramètres de mesure pour ajouter une mesure. La formule d'une mesure définit une somme, une moyenne ou un autre calcul à l'aide des colonnes et des tables du classeur PowerPivot.

  1. Dans la fenêtre PowerPivot, cliquez sur l'onglet Dossier de base, puis dans le groupe Affichages, cliquez sur Zone de calcul.

  2. Dans la table FactResellerSales, cliquez sur n'importe quelle cellule au sein de la Zone de calcul.

  3. Dans la barre de formule en haut du classeur, entrez une formule au format suivant <nom_mesure>:<formule> :

    Projected Sales:=SUM('FactResellerSales'[SalesAmount])*1.06

  4. Cliquez sur OK pour accepter la formule.

  5. Dans la fenêtre PowerPivot, cliquez sur l'onglet Dossier de base, et dans le groupe Rapports, cliquez sur Tableau croisé dynamique.

    Dans la boîte de dialogue Créer un tableau croisé dynamique, vérifiez que Nouvelle feuille de calcul est sélectionné, puis cliquez sur OK.

    PowerPivot crée un tableau croisé dynamique vierge dans une nouvelle feuille de calcul Excel et affiche la Liste de champs PowerPivot du côté droit du classeur.

  6. Développez la table FactResellerSales pour afficher la mesure que vous venez de créer. Si vous aviez déjà un tableau croisé dynamique dans le classeur avant de créer la mesure, vous devez cliquer sur le bouton Actualiser en haut de la liste de champs PowerPivot afin d'actualiser les champs.

  7. Dans la fenêtre Excel, sous l'onglet PowerPivot, dans le groupe Mesures, cliquez sur Nouvelle mesure.

  8. Dans la boîte de dialogue Paramètres de mesure, pour Nom de la table, cliquez sur la flèche Bas et sélectionnez FactResellerSales dans la liste déroulante.

    Le choix de la table détermine l'emplacement où la définition de la mesure sera stockée. La mesure ne doit pas nécessairement être stockée avec une table qu'elle référence.

  9. Dans Nom de la mesure (tous les tableaux croisés dynamiques), tapez Total Quantity.

  10. Le nom de la mesure doit être unique au sein d'un classeur, et vous ne pouvez pas utiliser le même nom que celui utilisé pour toute colonne d'un classeur.

  11. Dans la zone de texte Formule, positionnez le curseur après le signe égal (=), puis entrez la formule suivante :

    SUM(FactResellerSales[OrderQuantity])

  12. Cliquez sur OK.

Les deux mesures que vous avez créées sont enregistrées avec la table de données source mais elles peuvent être utilisées dans n'importe quel tableau croisé dynamique ou graphique croisé dynamique. Les mesures apparaissent dans la Liste de champs PowerPivot et sont accessibles à tous les utilisateurs du classeur.

Exemple : création d'une mesure explicite qui utilise une agrégation personnalisée

Dans cet exemple, vous allez créer une agrégation personnalisée qui utilise l'une des nouvelles fonctions d'agrégation DAX, SUMX et la fonction ALL, qui dans ce cas retourne toutes les valeurs d'une colonne indépendamment du contexte de cette colonne. L'exemple utilise les colonnes suivantes de l'exemple de classeur DAX :

  • DateTime [CalendarYear]

  • ProductCategory[ProductCategoryName]

  • ResellerSales_USD[SalesAmount_USD]

L'exemple utilise un tableau croisé dynamique qui utilise CalendarYear comme étiquette de ligne et ProductCategoryName comme étiquette de colonne ; SalesAmount_USD est utilisé dans la formule de la mesure. L'exemple répond à la question : quel pourcentage des ventes totales de 2005 à 2008 représente chaque année et chaque catégorie de produits ? Vous pourrez ainsi voir quel pourcentage du total les ventes de vélos ont représenté en 2007. Pour répondre à cette question, nous utilisons la formule de mesure suivante :

=SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])

La formule est construite comme suit :

  1. Le numérateur, SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD]), est la somme des valeurs de ResellerSales_USD[SalesAmount_USD] pour la cellule active du tableau croisé dynamique. Avec le contexte de CalendarYear et ProductCategoryName, cette valeur sera différente pour chaque combinaison d'année et de catégorie de produits. Par exemple, le nombre total de vélos vendus en 2003 est différent du nombre total d'accessoires vendus en 2008.

  2. Pour le dénominateur, commencez par spécifier une table, ResellerSales_USD, puis utilisez la fonction ALL pour supprimer tout le contexte de la table. Ainsi, la valeur sera le même pour chaque combinaison d'année et de catégorie de produits : le dénominateur sera toujours les ventes totales de 2005 à 2008.

  3. Vous utilisez ensuite la fonction SUMX pour additionner les valeurs de la colonne ResellerSales_USD[SalesAmount_USD]. En d'autres termes, vous obtenez la somme de ResellerSales_USD[SalesAmount_USD] pour toutes les ventes des revendeurs.

[!REMARQUE]

Dans Windows Vista et Windows 7, les fonctionnalités fournies dans la fenêtre PowerPivot sont disponibles sur un ruban, présenté dans cette rubrique. Dans Windows XP, ces fonctionnalités sont disponibles dans un ensemble de menus. Si vous utilisez Windows XP et souhaitez voir les correspondances entre les commandes des menus et les commandes du ruban, consultez Interface utilisateur de PowerPivot dans Windows XP.

Pour créer une mesure qui utilise une agrégation personnalisée

  1. Dans la fenêtre PowerPivot, cliquez sur l'onglet Dossier de base, et dans le groupe Rapports, cliquez sur Tableau croisé dynamique.

  2. Dans la boîte de dialogue Créer un tableau croisé dynamique, vérifiez que Nouvelle feuille de calcul est sélectionné, puis cliquez sur OK.

    PowerPivot crée un tableau croisé dynamique vierge dans une nouvelle feuille de calcul Excel et affiche la Liste de champs PowerPivot du côté droit du classeur.

  3. Dans la fenêtre Excel, utilisez la Liste de champs PowerPivot pour ajouter des colonnes au tableau croisé dynamique :

    1. Recherchez la table DateTime et faites glisser la colonne CalendarYear vers la zone Étiquettes de ligne du tableau croisé dynamique.

    2. Recherchez la table ProductCategory et faites glisser la colonne ProductCategoryName vers la zone Étiquettes de ligne du tableau croisé dynamique.

  4. Dans la fenêtre Excel, sous l'onglet PowerPivot, dans le groupe Mesures, cliquez sur Nouvelle mesure.

  5. Dans la boîte de dialogue Paramètres de mesure, pour Nom de la table, cliquez sur la flèche Bas et sélectionnez ResellerSales_USD dans la liste déroulante.

    Le choix de la table détermine l'emplacement où la définition de la mesure sera stockée. La mesure ne doit pas nécessairement être stockée avec une table qu'elle référence.

  6. Dans Nom de la mesure (tous les tableaux croisés dynamiques), tapez AllResSalesRatio.

    Ce nom est utilisé comme identificateur pour la mesure ; par conséquent, il doit être unique dans le classeur et ne peut pas être changé.

  7. Dans Nom personnalisé (ce tableau croisé dynamique), tapez All Reseller Sales Ratio.

    Ce nom est utilisé uniquement à des fins d'affichage dans le tableau croisé dynamique actuel. Par exemple, vous pouvez réutiliser la mesure AllResSalesRatio dans d'autres tableaux croisés dynamiques mais lui attribuer un autre nom ou utiliser un langage différent.

  8. Dans la zone de texte Formule, positionnez le curseur après le signe égal (=).

  9. Tapez SUMX, puis une parenthèse.

    =SUMX(

    Au fur et à mesure que vous tapez, l'info-bulle située sous la zone de texte Formule indique que la fonction SUMX requiert deux arguments : le premier est une table ou une expression qui retourne une table, et le second est une expression qui fournit les nombres pouvant être additionnés.

    Tapez Res, sélectionnez ResellerSales_USD dans la liste, puis appuyez sur TAB.

    Le nom de la colonne est inséré dans la formule, comme suit :

    =SUMX(ResellerSales_USD

  10. Tapez une virgule.

    L'info-bulle est mise à jour pour indiquer que l'argument obligatoire suivant doit spécifier une expression. Une expression peut être une valeur, une référence à une colonne ou une combinaison des deux. Par exemple, vous pouvez créer une expression qui additionne deux autres colonnes. Pour cet exemple, vous allez indiquer le nom d'une colonne qui contient le montant des ventes de chaque revendeur.

  11. Tapez les premières lettres du nom de la table qui contient la colonne que vous souhaitez inclure. Pour cet exemple, tapez Res et sélectionnez la colonne ResellerSales_USD[SalesAmount_USD] dans la liste.

  12. Appuyez sur Tab pour insérer le nom de colonne dans la formule et ajoutez une parenthèse fermée comme indiqué ici :

    =SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])

  13. Tapez une barre oblique, puis tapez ou copiez et collez le code suivant dans la boîte de dialogue Paramètres de mesure :

    SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])

    Notez la manière dont la fonction ALL est imbriquée dans la fonction SUMX. La formule entière apparaît maintenant comme suit :

    =SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])

  14. Cliquez sur Vérifier la formule.

    La formule est vérifiée afin d'identifier de potentielles erreurs de syntaxe ou de référence. Résolvez toute erreur qui aurait pu être rencontrée, telle qu'une parenthèse ou une virgule manquante.

  15. Cliquez sur OK.

    La mesure remplit maintenant le tableau croisé dynamique avec des valeurs pour chaque combinaison d'année civile et de catégorie de produits.

  16. Mettez le tableau en forme :

    1. Sélectionnez les données du tableau croisé dynamique, y compris la ligne Total général.

    2. Sous l'onglet Dossier de base, dans le groupe Nombre, cliquez sur le bouton de pourcentage (%), puis double-cliquez sur le bouton d'augmentation des décimales (<- .0 .00).

    Le tableau final doit se présenté comme ci-dessous. Vous pouvez maintenant voir le pourcentage des ventes totales pour chaque combinaison de produit et d'année. Par exemple, les ventes de vélos en 2007 ont représenté 31,71 % de toutes les ventes de 2005 à 2008.

All Reseller Sales

Étiquettes de colonne

 

 

 

 

Étiquettes de ligne

Accessories

Bikes

Clothing

Components

Total général

2005

0.02%

9.10%

0.04%

0.75%

9.91%

2006

0.11%

24.71%

0.60%

4.48%

29.90%

2007

0.36%

31.71%

1.07%

6.79%

39.93%

2008

0.20%

16.95%

0.48%

2.63%

20.26%

Total général

0.70%

82.47%

2.18%

14.65%

100.00%

Voir aussi

Référence

Indicateur de performance clé, boîte de dialogue

Concepts

Mesures dans PowerPivot

Fenêtre PowerPivot : Zone de calcul

Autres ressources

Vue d'ensemble des rapports, graphiques et tableaux croisés dynamiques