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

Lorsque vous découvrez pour la première fois comment utiliser Power Pivot, la plupart des utilisateurs découvrez que la puissance réelle consiste à agréguer ou à calculer un résultat d’une certaine façon. Si vos données sont regroupées dans une colonne avec des valeurs numériques, vous pouvez facilement les agréger en les sélectionnant dans un tableau croisé dynamique ou dans Power View de champs. Par nature, étant donné qu’elle est numérique, elle est automatiquement additionée, moyenne, comptée ou tout autre type d’agrégation que vous sélectionnez. Cette mesure est appelée mesure implicite. Les mesures implicites sont parfaits pour une agrégation simple et rapide, mais elles ont des limites, et ces limites peuvent presque toujours être dépassés par des mesures explicites et des colonnes calculées.

Commençons par examiner un exemple dans lequel nous utilisons une colonne calculée pour ajouter une nouvelle valeur de texte pour chaque ligne d’une table nommée Produit. Chaque ligne de la table Produit 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, etc. Nous avons une autre table liée nommée Product Category qui contient une colonne ProductCategoryName. Nous voulons que chaque produit de la table Produit inclue le nom de catégorie de produit de la table Product Category. Dans notre table Produit, nous pouvons créer une colonne calculée nommée Catégorie de produit comme ceci :

Notre nouvelle formule Product Category utilise la fonction RELATED DAX 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.

C’est un exemple formidable de la façon dont nous pouvons utiliser une colonne calculée pour ajouter une valeur fixe pour chaque ligne que nous pouvons utiliser plus tard dans la zone LIGNES, COLONNES ou FILTREs du tableau croisé dynamique ou dans un Power View rapport.

Créons 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 didacticiels. Dans notre modèle de données, nous avons une table Ventes 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 possède des montants de ventes et une autre colonne qui a des coûts.

Nous pouvons créer une colonne calculée qui calcule le montant des bénéfices de chaque ligne en soustrayant les valeurs de la colonne ROUAGES des valeurs de la colonne SalesAmount, 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 de Profit. Il s’agit d’un montant agrégé de valeurs provenant 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 deions 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 COLONNES, LIGNES ou FILTREs. Il a seulement du sens 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, dans laquelle 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 tout d’abord calculé un bénéfice pour chaque ligne de la table Sales, puis nous avons ajouté Profit à la zone VALEURS dans laquelle elle a été agrégée pour chacune des catégories de produits. Si vous pensez également que nous n’avons pas réellement besoin de créer la colonne calculée Profit, vous êtes également correct. Toutefois, comment ensuite calculer notre bénéfice sans créer de colonne calculée Profit ?

Les bénéfices, seraient vraiment mieux calculés comme une mesure explicite.

Pour l’instant, nous allons laisser notre colonne calculée Profit dans la table Sales et la catégorie de produit dans COLUMNS et Profit dans 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 Bénéfice total(pour éviter les conflits de noms). Au final, il produit les mêmes résultats que ce que nous avons fait auparavant, mais sans colonne calculée Profit.

Tout d’abord, dans la table Sales, nous sélectionnons la colonne SalesAmount, puis cliquons sur Somme automatique pour créer une mesure explicite sum of SalesAmount. Rappelez-vous 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 ROUAGES. Nous allons renommer ces total SalesAmount et Total ROUAGES pour les rendre plus faciles à identifier.

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

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

Après avoir changé 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 Profit total renvoie les mêmes résultats que la création d’une colonne calculée Profit et que vous la placez dans VALEURS. La différence est que notre mesure du total des bénéfices est beaucoup plus efficace et rend notre modèle de données plus propre et moins performant, car nous calculons au moment de la recherche et uniquement pour les champs que nous sélectionnons pour notre tableau croisé dynamique. Après tout, nous n’avons pas vraiment besoin de cette colonne calculée Profit.

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 de prendre des ressources comme celle-ci, car nous voulons vraiment calculer notre bénéfice lorsque nous sélectionnons les champs dont nous avons besoin de Profit dans le tableau croisé dynamique, tels que les catégories de produits, les régions ou les dates.

Examinons un autre exemple. Un résultat dans lequel 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 mesure du pourcentage des ventes totales. Nous créons une colonne calculée nommée % des ventes dans notre table Sales, comme ceci :

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

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

Bien. Cela semble bien à ce jour. Nous allons toutefois ajouter un slicer. Nous ajoutons l’année civile, puis sélectionnons une année. Dans ce cas, nous sélectionnons 2007. C’est ce que nous recevons.

À première vue, cela peut toujours sembler correct. Toutefois, nos pourcentages devraient réellement 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 mal passé ?

Notre colonne % des ventes 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 dans une colonne calculée sont fixes. Il s’agit d’un résultat immutable pour chaque ligne du tableau. Lorsque nous avons ajouté le pourcentage des 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 % des ventes sera toujours de 100 %.

Nous pouvons supprimer notre colonne calculée % des ventes, car cela ne nous sera d’une aide. Au lieu de cela, nous allons créer une mesure qui calcule correctement notre pourcentage des ventes totales, indépendamment des filtres ou des secteurs appliqués.

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

Pour créer une mesure, nous avons créé la formule suivante :

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

Cette formule indique : Diviser le résultat de Total SalesAmount par la somme totale 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 % des ventes totales au tableau croisé dynamique, nous recevons :

Voilà qui est mieux. À présent, notre pourcentage des ventes totales pour chaque catégorie de produit est calculé sous la mesure du 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 secteur CalendarYear, nous recevons de nouveaux pourcentages pour nos catégories de produits, mais notre total total est toujours de 100 %. Nous pouvons également ajouter d’autres slicers et filtres. Notre mesure % des ventes totales produira toujours un pourcentage des ventes totales, quels que soient les slicers ou 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 les slicers appliqués. C’est le pouvoir des mesures.

Voici quelques conseils pour vous aider dans le choix d’une colonne calculée ou d’une mesure pour un calcul particulier :

Utiliser des colonnes calculées

• Si vous souhaitez que vos nouvelles données apparaissent sur DES LIGNES, DES COLONNES ou dans des 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. Tout comme des colonnes normales de données, les colonnes calculées peuvent être utilisées en tant que 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 qu’une autre colonne contienne 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 de la colonne Date. Par exemple, =MOIS('Date'[Date]).

• Si vous voulez ajouter une valeur de texte pour chaque ligne à un tableau, 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 ») donne le nom de mois de 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 devez faire des calculs plus complexes, comme calculer un nombre en fonction d’un filtre, ou calculer une variance ou une année sur une année, utilisez un champ calculé.

• Si vous souhaitez conserver au minimum la taille de votre workbook et optimiser les performances, créez le plus de calculs possible, le plus de mesures possible. Dans de nombreux cas, tous vos calculs peuvent être des mesures, ce qui a pour effet de réduire de façon significative la taille du workbook et d’accélérer le temps d’actualisation.

N’oubliez pas que vous créez des colonnes calculées comme nous l’avons fait avec notre colonne Profit, puis que vous les agrégez dans un tableau croisé dynamique ou un rapport. Il s’agit en réalité d’un excellent moyen simple et efficace de découvrir et de créer vos propres calculs. À 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. Ce que vous avez appris ici peut vous aider. D’autres ressources très appropriées peuvent également vous aider. En voici quelques-unes : Contexte dans les formules DAX,agrégationsdans Power Pivot et Centre de ressources DAX. De plus, bien qu’elle soit un peu plus avancée et destinée aux professionnels de la comptabilité et de la finance, la modélisation et l’analyse des données pour les bénéfices et la perte avec Microsoft Power Pivot dans Excel sont remplies d’exemples de modélisation des données et de formules.