Situations dans lesquelles utiliser des colonnes calculées et des champs calculés

Remarque : Nous faisons de notre mieux pour vous fournir le contenu d’aide le plus récent aussi rapidement que possible dans votre langue. Cette page a été traduite automatiquement et peut donc contenir des erreurs grammaticales ou des imprécisions. Notre objectif est de faire en sorte que ce contenu vous soit utile. Pouvez-vous nous indiquer en bas de page si ces informations vous ont aidé ? Voici l’article en anglais à des fins de référence aisée.

Lorsque vous apprenez à utiliser Power Pivot, la plupart des utilisateurs découvrent que la véritable puissance consiste à les agréger ou à calculer un résultat d'une certaine façon. Si vos données comportent une colonne avec des valeurs numériques, vous pouvez facilement les agréger en les sélectionnant dans une liste de champs du tableau croisé dynamique ou Power View. Par nature, étant donné qu'il s'agit d'une valeur numérique, elle est automatiquement additionnée, moyenne, comptabilisée ou quel que soit le type d'agrégation que vous sélectionnez. C'est ce qu'on appelle une mesure implicite. Les mesures implicites sont parfaites pour une agrégation rapide et facile, mais elles ont des limites et ces limites peuvent presque toujours être résolues à l'aide de mesures explicites et de colonnes calculées.

Examinons tout d'abord un exemple dans lequel nous utilisons une colonne calculée pour ajouter une nouvelle valeur texte pour chaque ligne d'une table nommée Product. Chaque ligne de la table Product contient toutes sortes d'informations sur les produits que nous vendons. Nous avons des colonnes pour nom de produit, couleur, taille, prix revendeur, etc. Nous avons une autre table liée nommée catégorie de produit qui contient une colonne ProductCategoryName. Ce que nous souhaitons, c'est pour chaque produit de la table Product d'inclure le nom de la catégorie de produit à partir de la table Product Category. Dans notre table Product, nous pouvons créer une colonne calculée nommée Product Category, comme celle-ci:

Colonne calculée Catégorie de produit

Notre nouvelle formule de catégorie de produit utilise la fonction DAX associée pour obtenir les valeurs de la colonne ProductCategoryName dans la table de catégories de produits associées, puis entre ces valeurs pour chaque produit (chaque ligne) dans la table Product.

Voici un exemple d'utilisation d'une colonne calculée pour ajouter une valeur fixe pour chaque ligne que nous pouvons utiliser ultérieurement dans la zone lignes, colonnes ou filtres du tableau croisé dynamique ou dans un rapport Power View.

Nous allons créer un autre exemple dans lequel nous voulons calculer une marge bénéficiaire pour nos catégories de produits. Il s'agit d'un scénario courant, même dans un grand nombre de didacticiels. Nous avons une table Sales dans notre modèle de données qui comporte des données de transaction, et il existe une relation entre la table Sales et la table Product Category. Dans la table Sales, nous avons une colonne qui contient des montants de ventes et une colonne qui contient des coûts.

Nous pouvons créer une colonne calculée qui calcule un montant de bénéfice pour chaque ligne en soustrayant les valeurs de la colonne CMV des valeurs de la colonne SalesAmount, comme suit:

Colonne Bénéfices dans la table Power Pivot

Nous pouvons maintenant créer un tableau croisé dynamique et faire glisser le champ de catégorie de produit sur colonnes, et notre nouveau champ bénéfice dans la zone valeurs (une colonne dans une table dans PowerPivot est un champ de la liste des champs de tableau croisé dynamique). Le résultat est une mesure implicite nommée somme des bénéfices. Il s'agit d'un montant agrégé des valeurs de la colonne profit pour chacune des différentes catégories de produits. Notre résultat se présente comme suit:

Tableau croisé dynamique simple

Dans ce cas, le bénéfice n'est judicieux que sous forme de champ dans les valeurs. Si nous devions placer le bénéfice dans la zone colonnes, notre tableau croisé dynamique ressemblera à ce qui suit:

Tableau croisé dynamique sans valeurs utiles

Notre champ bénéfice ne fournit pas d'informations utiles lorsqu'il est placé dans des zones de colonnes, de lignes ou de filtres. Cette valeur est significative en tant que valeur agrégée dans la zone valeurs.

Ce que nous avons fait, c'est créer une colonne nommée profit qui calcule une marge bénéficiaire pour chaque ligne de la table Sales. Nous avons ensuite ajouté bénéfice à la zone valeurs de notre tableau croisé dynamique, créant automatiquement une mesure implicite dans laquelle un résultat est calculé pour chaque catégorie de produit. Si vous pensez que le bénéfice actuellement calculé pour nos catégories de produits vous convient, vous avez terminé. Nous avons d'abord calculé un bénéfice pour chaque ligne de la table ventes, puis ajouté un bénéfice à la zone valeurs dans laquelle il a été agrégé pour chaque catégorie de produit. Si vous pensez également que nous n'avons pas vraiment eu besoin de créer la colonne calculée bénéfice, vous êtes également correct. Mais comment calculer le bénéfice sans créer de colonne calculée de bénéfice?

Bénéfice, serait vraiment mieux calculé en tant que mesure explicite.

Pour le moment, nous allons quitter la colonne calculé bénéfice de la table ventes et de la catégorie produit en colonnes et tirer les valeurs de notre tableau croisé dynamique pour comparer les résultats.

Dans la zone de calcul de notre table Sales, nous allons créer une mesure nommée bénéficetotal (afin d'éviter les conflits de noms). Au final, il produira les mêmes résultats que nous l'avons fait précédemment, mais sans colonne calculée de marge.

Tout d'abord, dans la table Sales, sélectionnez la colonne SalesAmount, puis cliquez sur Somme automatique pour créer une somme explicite de SalesAmountmesure. N'oubliez pas qu'une mesure explicite est une mesure que nous créons dans la zone de calcul d'une table dans Power Pivot. Nous faisons de même pour la colonne COGS. Nous renommerons le total SalesAmount et le total des Cogs pour faciliter leur identification.

Bouton Somme automatique dans Power Pivot

Nous créons ensuite une autre mesure avec la formule suivante:

Bénéfice total: = [ total SalesAmount]-[total Cogs]

Remarque : Nous pourrions également écrire notre formule en tant que bénéfice total: = SUM ([SalesAmount])-SUM ([CMV]), mais en créant des mesures totales totales SalesAmount et total CMV, nous pouvons également les utiliser dans notre tableau croisé dynamique, et nous pouvons les utiliser comme arguments dans toutes sortes de formules de mesure.

Une fois que vous avez modifié le format de notre nouvelle mesure bénéfice total en devise, nous pouvons l'ajouter à notre tableau croisé dynamique.

Tableau croisé dynamique

Vous pouvez constater que la nouvelle mesure bénéfice total renvoie les mêmes résultats que la création d'une colonne calculée de bénéfices, puis leur placement dans les valeurs. La différence est que notre mesure de bénéfice total est bien plus efficace et améliore notre nettoyeur de modèle de données, car nous calculons au moment et uniquement les champs que nous avons sélectionnés pour notre tableau croisé dynamique. Nous n'avons pas vraiment besoin de la colonne calculée bénéfice après tout.

Pourquoi cette dernière partie est-elle importante? Les colonnes calculées ajoutent des données au modèle de données, et Data occupe de la mémoire. Si nous actualisons le modèle de données, les ressources de traitement sont également nécessaires pour recalculer toutes les valeurs de la colonne profit. Nous n'avons pas vraiment besoin d'utiliser des ressources comme celle-ci, car nous voulons vraiment calculer notre bénéfice lorsque nous sélectionnarons les champs auxquels nous voulons tirer dans le tableau croisé dynamique, comme les catégories de produits, les régions ou les dates.

Examinons un autre exemple. Une colonne dans laquelle une colonne calculée génère des résultats qui s'affichent à première vue, mais pas...

Dans cet exemple, nous voulons calculer les montants des ventes en tant que pourcentage des ventes totales. Nous créons une colonne calculée nommée % des ventes dans notre table Sales, comme suit:

Colonne calculée % des ventes

Notre formule indique: pour chaque ligne de la table Sales, divise la valeur de la colonne SalesAmount par la somme totale de tous les montants de la colonne SalesAmount.

Si nous créons un tableau croisé dynamique et ajoutons une catégorie de produit aux colonnes et sélectionnez notre nouvelle colonne % de ventes pour y insérer des valeurs, nous obtenons une somme totale de% des ventes pour chacune de nos catégories de produits.

Tableau croisé dynamique affichant la somme des pourcentages des ventes pour les catégories de produits

Correct. Cette opération s'affiche correctement. Mais nous allons ajouter un segment. Nous ajoutons l'année calendrier, puis nous sélectionnerons une année. Dans ce cas, nous allons sélectionner 2007. C'est ce que nous obtenons.

Résultat incorrect de la somme des pourcentages des ventes dans le tableau croisé dynamique

Au premier coup d'œil, cela peut toujours sembler correct. Toutefois, nos pourcentages devraient vraiment totaliser 100%, car nous souhaitons connaître le pourcentage des ventes totales pour chacune de nos catégories de produits 2007. Que se passe-t-il?

Notre colonne% de ventes a calculé un pourcentage pour chaque ligne correspondant à la valeur de la colonne SalesAmount divisé par le total de toutes les valeurs de la colonne SalesAmount. Les valeurs d'une colonne calculée sont fixes. Il s'agit d'un résultat immuable pour chaque ligne de la table. Lorsque nous avons ajouté % des ventes au tableau croisé dynamique, celui-ci a été agrégé sous la forme d'une somme de toutes les valeurs de la colonne SalesAmount. Cette somme de toutes les valeurs de la colonne% des ventes sera toujours de 100%.

Conseil : Veillez à lire le contexte dans les formules DAX. Il fournit une bonne compréhension du contexte de niveau ligne et du contexte de filtre, qui est ce que nous décrivons ici.

Nous pouvons supprimer notre colonne calculée% sur ventes, car cela ne va pas nous aider. Au lieu de cela, nous allons créer une mesure qui calcule correctement notre pourcentage de ventes totales, quels que soient les filtres ou les segments appliqués.

SouVenez-vous de la mesure TotalSalesAmount que nous avons créée précédemment, celle qui fait juste la somme de la colonne SalesAmount? Nous l'avons utilisé en tant qu'argument dans notre mesure de bénéfice total, et nous allons le réutiliser comme argument dans notre nouveau champ calculé.

Conseil : La création de mesures explicites, telles que le total SalesAmount et le total des COGSs, ne s'avère pas seulement utile dans un tableau croisé dynamique ou un rapport, mais elles sont également utiles en tant qu'arguments dans d'autres mesures lorsque vous avez besoin du résultat en tant qu'argument. Cela rend vos formules plus efficaces et plus faciles à lire. Il s'agit d'une bonne pratique de modélisation des données.

Nous créons une nouvelle mesure avec la formule suivante:

% du total des ventes: = ([total SalesAmount])/CALCULATE ([total SalesAmount], ALLSELECTED ())

Cette formule indique comment diviser le résultat de total SalesAmount à l'aide de la somme totale de SalesAmount sans filtres de colonne ou de ligne autres que ceux définis dans le tableau croisé dynamique.

Conseil : Veillez à consulter à propos des fonctions Calculate et ALLSELECTED dans la référence Dax.

À présent, si nous ajoutons notre nouveau % des ventes totales au tableau croisé dynamique, nous obtenons:

Résultat correct de la somme des pourcentages des ventes dans le tableau croisé dynamique

Cela semble mieux. À présent, notre % des ventes totales pour chaque catégorie de produit est calculée sous la forme d'un pourcentage des ventes totales de l'année 2007. Si nous sélectionnaons une autre année ou plus d'un an dans le segment CalendarYear, nous obtenons de nouveaux pourcentages pour nos catégories de produits, mais notre total général est toujours 100%. Nous pouvons également ajouter d'autres segments et filtres. Notre% total Sales Measure produira toujours un pourcentage des ventes totales, quel que soit le nombre de segments appliqués ou de filtres. Avec les mesures, le résultat est toujours calculé en fonction du contexte déterminé par les champs dans les colonnes et les lignes, et par les filtres ou segments appliqués. Il s'agit de la puissance des mesures.

Voici quelques conseils pour vous aider à déterminer s'il est nécessaire de déterminer si une colonne calculée ou une mesure est appropriée pour un calcul particulier:

Utiliser des colonnes calculées

  • Si vous souhaitez que vos nouvelles données apparaissent sur des lignes, colonnes ou filtres dans un tableau croisé dynamique, ou sur un axe, une légende ou une mosaïque par dans une visualisation Power View, vous devez utiliser une colonne calculée. À l'instar des colonnes de données normales, les colonnes calculées peuvent être utilisées comme champ dans n'importe quelle zone, et s'il s'agit de nombres, ils peuvent également être agrégés dans les valeurs.

  • Si vous souhaitez que vos nouvelles données soient une valeur fixe pour la ligne. Par exemple, vous avez une table de dates avec une colonne de dates, et vous souhaitez une autre colonne qui contient uniquement le nombre du mois. Vous pouvez créer une colonne calculée qui calcule uniquement le numéro du mois à partir des dates de la colonne Date. Par exemple, = MONTH ('date' [date]).

  • Si vous voulez ajouter une valeur de texte à chaque ligne d'un tableau, utilisez une colonne calculée. Les champs contenant des valeurs de texte ne peuvent jamais être agrégés dans les valeurs. Par exemple, = FORMAT («date» [date], «mmmm») nous renvoie le nom du mois pour chaque date figurant dans la colonne Date de la table de dates.

Utiliser des mesures

  • Si le résultat de votre calcul dépend toujours des autres champs que vous sélectionnez dans un tableau croisé dynamique.

  • Si vous devez effectuer des calculs plus complexes, par exemple calculer un nombre sur la base d'un filtre d'un tri, ou calculer un cumul annuel, ou un écart, utilisez un champ calculé.

  • Si vous souhaitez conserver la taille de votre classeur à un minimum et optimiser ses performances, créez autant de calculs que possible en tant que mesure possible. Dans de nombreux cas, tous vos calculs peuvent être des mesures, réduisant de façon significative la taille du classeur et accélérant le temps d'actualisation.

N'oubliez pas que la création de colonnes calculées, comme nous l'avons fait avec notre colonne profit, et l'agrégation dans un rapport ou un tableau croisé dynamique. C'est en fait un excellent moyen de découvrir et créer vos propres calculs. À mesure que vous comprenez ces deux fonctionnalités extrêmement puissantes de Power Pivot, vous souhaiterez créer le modèle de données le plus efficace et précis que vous pouvez utiliser. J'espère que ce que vous avez appris ici vous aide. Il existe d'autres ressources vraiment intéressantes qui peuvent vous aider. Voici quelques exemples: contexte dans les formules DAX, agrégations dans Power Pivotet centre de ressources Dax. Et s'il s'agit d'un peu plus avancé et s'adresse aux professionnels de la comptabilité et finance, l' analyse et la modélisation des données de pertes et profits avec Microsoft Power pivot dans Excel sont chargées avec des exemples de formules et de modélisation de données remarquables.

Développez vos compétences dans Office
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×