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 véritable puissance réside dans l’agrégation ou le calcul d’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 s’agit d’un chiffre, il sera automatiquement additionné, moyenné, compté, 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 idéales pour une agrégation rapide et facile, mais elles ont des limites, et ces limites peuvent presque toujours être surmontées par des mesuresexplicites et des colonnes calculées.

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

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

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 pouvons utiliser ultérieurement dans la zone LIGNES, COLONNES ou FILTRES du tableau croisé dynamique ou dans un rapport Power View.

Créons un autre exemple où 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 Ventes dans notre modèle de données qui contient des données de transaction, et il existe une relation entre la table Ventes et la table Catégorie de produit. Dans la table Ventes, nous avons une colonne contenant les montants des ventes et une autre colonne contenant les 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 COGS des valeurs de la colonne MontantVentes, comme ceci :

Maintenant, 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 dans une table dans PowerPivot est un champ dans la liste de champs de tableau croisé dynamique). Le résultat est une mesure implicite nommée Somme des bénéfices. Il s’agit d’une quantité agrégée de valeurs de la colonne des bénéfices 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 VALUES. Si nous devions placer Profit dans la zone COLONNES, notre tableau croisé dynamique ressemblerait à ceci :

Notre champ Profit ne fournit aucune information utile lorsqu’il est placé dans des zones COLUMNS, LINES ou FILTERS. Elle n’a de sens qu’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 du tableau des ventes. Nous avons ensuite ajouté Profit à la zone VALEURS de notre tableau croisé dynamique, en créant automatiquement une mesure implicite dans laquelle un résultat est calculé pour chacune des catégories de produits. Si vous pensez que nous avons vraiment calculé les bénéfices de nos catégories de produits deux fois, vous avez raison. Nous avons d’abord calculé un bénéfice pour chaque ligne de la table Ventes, puis nous avons ajouté le bénéfice à la zone VALEURS où il était agrégé pour chacune des catégories de produits. Si vous pensez également que nous n’avons pas vraiment eu besoin de créer la colonne calculée des bénéfices, vous avez également raison. Mais, comment alors calculer notre bénéfice sans créer une colonne calculée Profit ?

Le profit serait vraiment mieux calculé comme une mesure explicite.

Pour l’instant, nous allons laisser notre colonne calculée Profit dans la table Ventes et Catégorie de produit dans COLONNES et Profit dans VALEURS de notre tableau croisé dynamique, pour comparer nos résultats.

Dans la zone de calcul de notre table Ventes, nous allons créer une mesure nommée Bénéfice total (pour éviter les conflits de noms). Au final, cela donnera les mêmes résultats que ce que nous avons fait auparavant, mais sans colonne calculée Profit.

Tout d’abord, dans la table Ventes, nous sélectionnons la colonne MontantVentes, puis nous cliquons sur Somme automatique pour créer une mesure Somme du Montant des Ventes explicite. N’oubliez pas qu’une mesure explicite est une mesure que nous créons dans la zone de calcul d’un tableau dans Power Pivot. Nous faisons de même pour la colonne COGS. Nous allons renommer ces montants Total SalesAmount et Total COGS pour les rendre plus faciles à identifier.

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

Bénéfice total :=[Total SalesAmount] - [Total COGS]

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

Vous pouvez voir que notre nouvelle mesure Total Profit renvoie les mêmes résultats que la création d’une colonne calculée Profit et son placement dans VALEURS. La différence réside dans le fait que notre mesure de la marge totale est beaucoup plus efficace et rend notre modèle de données plus propre et plus léger, car nous calculons à ce moment-là 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 la mémoire. Si nous actualisons le modèle de données, des 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 pour lesquels nous voulons Profit dans le tableau croisé dynamique, comme les catégories de produits, la région ou par dates.

Prenons un autre exemple. Une colonne calculée qui crée des résultats qui semblent corrects à première vue, mais...

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

Notre formule stipule : pour chaque ligne de la table Ventes, divisez le montant de la colonne MontantVentes par le total SOMME de tous les montants de la colonne MontantVentes.

Si nous créons un tableau croisé dynamique, ajoutons une catégorie de produit à COLONNES et sélectionnons notre nouvelle colonne % de ventes pour la mettre dans VALEURS, nous obtenons une somme totale de % de ventes pour chacune de nos catégories de produits.

D’accord. Cela semble bien jusqu’à présent. Mais, ajoutons un segment. Nous ajoutons Calendar année, puis sélectionnons une année. Dans ce cas, nous sélectionnons 2007. C’est ce que nous obtenons.

À première vue, cela peut sembler correct. Mais nos pourcentages devraient vraiment totaliser 100 %, car nous voulons connaître le pourcentage des ventes totales pour chacune de nos catégories de produits pour 2007. Alors, qu’est-ce qui n’a pas fonctionné ?

Notre colonne % des ventes a calculé un pourcentage pour chaque ligne, c’est-à-dire la valeur de la colonne MontantVentes divisée par la somme totale de toutes les valeurs de la colonne MontantVentes. Les valeurs dans une colonne calculée sont fixes. Ils constituent un résultat immuable pour chaque ligne de la table. Lorsque nous avons ajouté % des ventes à notre tableau croisé dynamique, il était agrégé sous la forme d’une somme de toutes les valeurs de la colonne MontantVentes. La somme de toutes les valeurs de la colonne % de ventes sera toujours de 100 %.

Nous pouvons supprimer notre colonne calculée % des ventes, car cela ne nous aidera pas. Au lieu de cela, nous allons créer une mesure qui calcule correctement notre pourcentage des 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 Profit total, et nous allons l’utiliser à nouveau comme argument dans notre nouveau champ calculé.

Nous créons une mesure avec la formule suivante :

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

Cette formule indique : divisez le résultat de Total SalesAmount par la somme totale de SalesAmount sans aucun filtre de colonne ou de ligne autre 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 :

Ça a l’air mieux. Maintenant, notre pourcentage de ventes totales pour chaque catégorie de produits est calculé en 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 Année civile, 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 % des ventes totales produira 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 de COLONNES et de LIGNES, ainsi que par les filtres ou segments éventuellement appliqués. C’est le pouvoir des mesures.

Voici quelques recommandations pour vous aider à déterminer 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 apparaissent sur les LIGNES, les COLONNES ou les FILTRES d’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. Tout comme les colonnes de données normales, les colonnes calculées peuvent être utilisées comme un champ dans n’importe quelle zone, et si elles sont numériques, elles peuvent également être agrégées dans 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 voulez 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 du mois à partir des dates de la colonne Date. Par exemple, =MOIS('Date'[Date]).
• Si vous voulez ajouter une valeur de texte pour chaque ligne d’un tableau, utilisez une colonne calculée. Les champs contenant des valeurs de texte ne peuvent jamais être regroupés dans VALEURS. Par exemple, =FORMAT('Date'[Date],"mmmm ») indique le nom du mois pour chaque date dans la colonne Date de la table Date.

Mesures d’utilisation

• 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, tels que le calcul d’un nombre basé sur un filtre quelconque ou le calcul d’une année à l’autre ou d’une variance, 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 à les agréger dans un tableau croisé dynamique ou un rapport. C’est en fait un moyen très simple d’apprendre et de créer vos propres calculs. Au fur et à mesure que vous comprendrez 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 vous aidera. Il existe d’autres ressources très intéressantes qui peuvent vous aider aussi. En voici quelques-unes : 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 destiné aux professionnels de la comptabilité et de la finance, l’exemple Modélisation et analyse des données de profits et pertes avec Microsoft Power Pivot dans Excel regorge d’excellents exemples de modélisation de données et de formules.