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

Lorsqu’ils apprennent à utiliser Power Pivot pour la première fois, la plupart des utilisateurs découvrent que la puissance réelle consiste à agréger ou à calculer un résultat d’une manière ou d’une autre. Si vos données ont une colonne avec des valeurs numériques, vous pouvez facilement l’agréger en la sélectionnant dans un tableau croisé dynamique ou une liste de champs Power View. Par nature, étant donné qu’il est numérique, il est automatiquement additionné, moyenné, compté ou n’importe quel type d’agrégation que vous sélectionnez. Il s’agit d’une mesure implicite. Les mesures implicites sont idéales pour l’agrégation rapide et facile, mais elles ont des limites, et ces limites peuvent presque toujours être surmontées avec des mesuresexplicites et des colonnes calculées.

Examinons d’abord un exemple où nous utilisons une colonne calculée pour ajouter une nouvelle valeur de texte pour chaque ligne d’une table nommée Product. Chaque ligne de la table Product contient toutes sortes d’informations sur chaque produit que nous vendons. Nous avons des colonnes pour le nom du produit, la couleur, la taille, le prix du revendeur, etc.. Nous avons une autre table associée nommée Product Category qui contient une colonne ProductCategoryName. Ce que nous voulons, c’est que chaque produit de la table Product inclue le nom de la catégorie de produit de la table Catégorie de produit. Dans notre table Product, nous pouvons créer une colonne calculée nommée Product Category comme suit :

Notre nouvelle formule Product Category utilise la fonction DAX RELATED pour obtenir des valeurs à partir de la colonne ProductCategoryName dans la table Product Category associée, puis entre ces valeurs pour chaque produit (chaque ligne) dans la table Product.

Il s’agit d’un excellent exemple de la façon dont nous pouvons utiliser une colonne calculée pour ajouter une valeur fixe pour chaque ligne que nous pourrons 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 de nombreux tutoriels. Nous avons une table Sales dans notre modèle de données qui contient 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 les montants des ventes et une autre colonne qui contient les coûts.

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

À présent, nous pouvons créer un tableau croisé dynamique et faire glisser le champ Catégorie de produit vers COLONNES, et notre nouveau champ Profit dans la zone VALEURS (une colonne d’une table dans PowerPivot est un champ dans la liste des champs de tableau croisé dynamique). Le résultat est une mesure implicite nommée Sum of Profit. Il s’agit d’une quantité agrégée de valeurs de la colonne profit pour chacune des différentes catégories de produits. Notre résultat ressemble à ceci :

Dans ce cas, Profit n’a de sens qu’en tant que champ dans VALEURS. Si nous devions placer Profit dans la zone COLONNES, notre tableau croisé dynamique se présenterait comme suit :

Notre champ Profit ne fournit aucune information utile lorsqu’il est placé dans des zones COLONNES, LIGNES ou FILTRES. Cela n’a de sens qu’en tant que valeur agrégée dans la zone VALEURS.

Nous avons créé une colonne nommée Profit qui calcule une marge bénéficiaire pour chaque ligne de la table Sales. Nous avons ensuite ajouté Profit à la zone VALEURS de notre tableau croisé dynamique, créant automatiquement une mesure implicite, où un résultat est calculé pour chacune des catégories de produits. Si vous pensez que nous avons vraiment calculé le bénéfice pour nos catégories de produits à deux reprises, vous avez raison. Nous avons d’abord calculé un bénéfice pour chaque ligne de la table Sales, puis nous avons ajouté Profit à la zone VALEURS où il a été agrégé pour chacune des catégories de produits. Si vous pensez également que nous n’avons pas vraiment besoin de créer la colonne calculée profit, vous avez également raison. Mais comment alors calculer notre bénéfice sans créer une colonne calculée profit ?

Profit, serait vraiment mieux calculé comme une mesure explicite.

Pour l’instant, nous allons laisser notre colonne Des bénéfices calculés dans la table Sales et Product Category dans COLUMNS et Profit in VALUES de notre tableau croisé dynamique, pour comparer nos résultats.

Dans la zone de calcul de notre table Sales, nous allons créer une mesure nommée Total Profit (pour éviter les conflits de noms). En fin de compte, il produit les mêmes résultats que ce que nous avons fait auparavant, mais sans une colonne calculée de profit.

Tout d’abord, dans la table Sales, nous sélectionnons la colonne SalesAmount, puis cliquez sur Somme automatique pour créer une mesure Sum of SalesAmountexplicite. N’oubliez pas qu’une mesure explicite est celle 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 allons renommer ces valeurs Total SalesAmount et Total COGS pour faciliter leur identification.

Ensuite, nous créons une autre mesure avec cette formule :

Total Profit :=[ Total SalesAmount] - [Total COGS]

Après avoir modifié le format de notre nouvelle mesure Total Profit en devise, nous pouvons l’ajouter à notre tableau croisé dynamique.

Vous pouvez voir que notre nouvelle mesure Total Profit retourne les mêmes résultats que la création d’une colonne calculée sur les bénéfices, puis la placer dans VALEURS. La différence est que notre mesure Total Profit est beaucoup plus efficace et rend notre modèle de données plus propre et plus simple, car nous calculons à ce moment et uniquement pour les champs que nous sélectionnons pour notre tableau croisé dynamique. Nous n’avons pas vraiment besoin de cette colonne calculée profit 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 les données occupent 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électionnons les champs dont nous voulons bénéficier dans le tableau croisé dynamique, comme les catégories de produits, la région ou les dates.

Examinons un autre exemple. Un où une colonne calculée crée des résultats qui à première vue semblent corrects, mais....

Dans cet exemple, nous voulons calculer les montants des ventes sous la forme d’un pourcentage du total des ventes. Nous créons une colonne calculée nommée % des ventes dans notre table Sales, comme suit :

Notre formule indique : Pour chaque ligne de la table Sales, divisez le montant dans la colonne SalesAmount par le total SOMME de tous les montants de la colonne SalesAmount.

Si nous créons un tableau croisé dynamique et que nous ajoutons une catégorie de produit aux COLONNES et que nous sélectionnons notre nouvelle colonne % de Ventes pour la placer dans VALEURS, nous obtenons un total total de % de Ventes pour chacune de nos catégories de produits.

D'accord. Ça a l’air bon jusqu’à présent. Mais nous allons ajouter un segment. Nous ajoutons l’année civile, puis nous sélectionnons une année. Dans ce cas, nous sélectionnons 2007. C’est ce que nous obtenons.

À première vue, cela peut toujours sembler correct. Mais nos pourcentages devraient vraiment totaler 100 %, car nous voulons connaître le pourcentage des ventes totales pour chacune de nos catégories de produits pour 2007. Qu’est-ce qui s’est passé ?

Notre colonne % de Sales a calculé un pourcentage pour chaque ligne qui est la valeur de la colonne SalesAmount divisée par la somme totale 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é % de Ventes à notre tableau croisé dynamique, il a été agrégé sous la forme d’une somme de toutes les valeurs dans la colonne SalesAmount. Cette somme de toutes les valeurs de la colonne % de Ventes sera toujours de 100 %.

Nous pouvons supprimer notre colonne % de ventes calculée, 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 segments appliqués.

Vous vous souvenez de la mesure TotalSalesAmount que nous avons créée précédemment, celle qui additionne simplement la colonne SalesAmount ? Nous l’avons utilisé comme argument dans notre mesure Total Profit, et nous allons l’utiliser à nouveau comme argument dans notre nouveau champ calculé.

Nous créons une mesure avec la formule suivante :

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

Cette formule indique : Divisez le résultat de Total SalesAmount par le total total de SalesAmount sans filtres de colonne ou de ligne autres que ceux définis dans le tableau croisé dynamique.

Maintenant, si nous ajoutons notre nouveau % du total des ventes au tableau croisé dynamique, nous obtenons :

C’est mieux. À présent, notre pourcentage des ventes totales pour chaque catégorie de produits est calculé sous la forme d’un pourcentage des ventes totales pour l’année 2007. Si nous sélectionnons une année différente, ou plus d’une année dans le segment CalendarYear, nous obtenons de nouveaux pourcentages pour nos catégories de produits, mais notre total général est toujours de 100 %. Nous pouvons également ajouter d’autres segments et filtres. Notre mesure % du total des ventes produit toujours un pourcentage des ventes totales, quels que soient les segments ou les filtres appliqués. Avec les mesures, le résultat est toujours calculé en fonction du contexte déterminé par les champs dans COLONNES et LIGNES, et par les filtres ou segments appliqués. C’est le pouvoir des mesures.

Voici quelques conseils pour vous aider à décider si une colonne calculée ou une mesure est adaptée à un besoin de calcul particulier :

Utiliser des colonnes calculées

• Si vous souhaitez que vos nouvelles données s’affichent sur les lignes, les colonnes ou les filtres d’un tableau croisé dynamique, ou sur un axe, une légende ou une vignette PAR dans une visualisation Power View, vous devez utiliser une colonne calculée. Tout comme les colonnes de données normales, les colonnes calculées peuvent être utilisées comme champ dans n’importe quelle zone, et si elles sont numériques, elles peuvent également être agrégées dans VALUES.

• 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 numéro du mois. Vous pouvez créer une colonne calculée qui calcule uniquement le numéro de mois à partir des dates dans la colonne Date. Par exemple, =MONTH('Date'[Date]).

• Si vous souhaitez ajouter une valeur de texte pour chaque ligne à une table, utilisez une colonne calculée. Les champs avec des valeurs de texte ne peuvent jamais être agrégés dans VALEURS. Par exemple, =FORMAT('Date'[Date],"mmmm ») nous donne le nom du mois pour chaque date dans la colonne Date de la table Date.

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 avez besoin d’effectuer des calculs plus complexes, comme calculer un décompte basé sur un filtre quelconque, ou calculer une variation d’une année sur l’autre, utilisez un champ calculé.

• Si vous souhaitez réduire au minimum la taille de votre classeur et optimiser ses performances, créez autant de calculs que possible. Dans de nombreux cas, tous vos calculs peuvent être des mesures, ce qui réduit considérablement la taille du classeur et accélère le temps d’actualisation.

Gardez à l’esprit qu’il n’y a rien de mal à créer des colonnes calculées comme nous l’avons fait avec notre colonne Profit, puis à l’agréger dans un tableau croisé dynamique ou un rapport. C’est en fait un moyen très pratique et facile d’en savoir plus sur et de créer vos propres calculs. À mesure que vous comprenez ces deux fonctionnalités extrêmement puissantes de Power Pivot, vous voudrez créer le modèle de données le plus efficace et le plus précis possible. J’espère que ce que vous avez appris ici aide. Il y a d’autres ressources vraiment formidables qui peuvent vous aider aussi. En voici quelques-uns : Contexte dans les formules DAX, Agrégations dans Power Pivot et Centre de ressources DAX. Et, bien qu’il soit un peu plus avancé et dirigé vers les professionnels de la comptabilité et de la finance, l’exemple Modélisation et analyse des données de profit and loss avec Microsoft Power Pivot dans Excel est chargé avec d’excellents exemples de formule et de modélisation des données.