Partager via

  • Article

Créer une mesure

Cette rubrique montre comment créer une mesure reposant sur les données de l'exemple de classeur DAX. Ce classeur 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 réaliser des calculs.

Présentation des mesures

Une mesure est une formule créée spécifiquement pour être utilisée dans un tableau croisé dynamique (ou un graphique croisé dynamique) qui utilise des données PowerPivot. Les mesures peuvent reposer sur des fonctions d'agrégation standard, comme COUNT ou SUM, ou vous pouvez définir votre propre formule à l'aide de DAX. Une mesure est utilisée dans la zone Valeurs d'un tableau croisé dynamique. Si vous souhaitez placer des résultats calculés dans une zone différente d'un tableau croisé dynamique, utilisez plutôt une colonne calculée (Créer une colonne calculée).

Lorsque vous créez une mesure, vous l'associez à une table dans le classeur. La définition de la mesure est enregistrée avec cette table. Elle apparaît dans la Liste de champs PowerPivot et est disponible à tous les utilisateurs du classeur.

Création et modification de mesures

Avant de créer une mesure, vous devez ajouter un tableau croisé dynamique ou un graphique croisé dynamique à votre classeur PowerPivot. 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.

Après avoir ajouté un tableau croisé dynamique ou un graphique croisé dynamique à votre classeur PowerPivot, utilisez la boîte de dialogue Paramètres de mesure pour ajouter une mesure qui contient une formule. La formule définit une somme, une moyenne ou d'autres calculs à l'aide des colonnes et des tables de la fenêtre PowerPivot. Vous créez des agrégations standard comme dans Excel, en faisant glisser des champs vers la zone de champ Valeurs, puis en choisissant l'une des méthodes d'agrégation standard : COUNT, SUM, AVERAGE, MIN ou MAX. Les agrégations personnalisées sont présentées dans la section suivante.

La mesure que vous créez peut être utilisée dans plusieurs tableaux croisés dynamiques ou graphiques croisés dynamiques. 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.

Exemple : création d'une mesure 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 2001 à 2004 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 2003. 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 2004.

  2. Pour le dénominateur, vous commencez par spécifier une table, ResellerSales_USD, et 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 2001 à 2004.

  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.

Notes

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 Accueil, 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, puis sélectionnez ResellerSales_USD dans la liste et 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 Accueil, dans le groupe Nombre, cliquez sur le bouton de pourcentage (%), puis cliquez deux fois 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 2003 ont représenté 31,71 % de toutes les ventes de 2001 à 2004.

All Reseller Sales

Étiquettes de colonne

 

 

 

 

Étiquettes de ligne

Accessories

Bikes

Clothing

Components

Total général

2001

0.02%

9.10%

0.04%

0.75%

9.91%

2002

0.11%

24.71%

0.60%

4.48%

29.90%

2003

0.36%

31.71%

1.07%

6.79%

39.93%

2004

0.20%

16.95%

0.48%

2.63%

20.26%

Total général

0.70%

82.47%

2.18%

14.65%

100.00%

Modifier une mesure existante

Pour afficher la définition d'une mesure existante, vous utilisez la Liste de champs PowerPivot. La liste de champs PowerPivot contient la liste de toutes les tables de la fenêtre PowerPivot active, ainsi que les colonnes de données brutes, les colonnes calculées et toutes les mesures que vous avez pu définir. Vous pouvez cliquer avec le bouton droit sur la définition d'une mesure quelconque et sélectionner Modifier la formule pour ouvrir une boîte de dialogue qui vous permet d'afficher et de modifier la définition de la mesure.

Pour afficher et modifier une mesure existante

  1. Dans la fenêtre Excel, cliquez n'importe où dans la zone du tableau croisé dynamique ou du graphique croisé dynamique pour afficher la Liste de champs PowerPivot.

  2. Dans la Liste de champs PowerPivot, trouvez la table qui contient la mesure que vous avez créée.

    Chaque table peut contenir des colonnes de base, des colonnes calculées et des mesures. Les mesures sont identifiables à la petite icône de calculatrice figurant à droite du nom de la mesure.

    Pour cet exemple, cliquez avec le bouton droit sur SumAmtByReseller, puis cliquez sur Modifier la formule.

  3. Dans la boîte de dialogue Paramètres de mesure, modifiez la formule.

    Vous pouvez également modifier le nom de la mesure ou le nom personnalisé, et la table associée.