Power Pivot : Colonnes vs mesures en DAX

Publié le 26 mai 2021
par Sophie Marchand M.Sc.
Marge brute %

Si vous devez travailler avec Power Pivot (ou avec SQL Server Analysis Services Tabular ou maintenant avec Power BI), sachez qu’il existe un langage de programmation dédié qui se nomme DAX (pour Data Analysis Expression). Ce langage puise ses racines dans les fonctions Excel mais comporte néanmoins certaines différences majeurs et il s’utilise sur des bases de données (des tables et des colonnes), plutôt que sur des cellules. Il faut donc s’ajuster en conséquence. Parmi les possibilités offertes, on retrouve les colonnes et les mesures en DAX (ou si vous préférez les champs calculés), semblables en quelque sorte, aux champs et aux items calculés des tableaux croisés dynamiques d’Excel.

 

Colonnes calculées en DAX

Comme son nom l’indique, une colonne calculée, est une colonne de calculs, que l’on peut ajouter à une table source dans un modèle de données Power Pivot.  Pour illustrer le concept de colonnes calculées DAX, nous allons travailler avec le modèle de données suivant.

PowerPivot - Schéma en étoile

 

Pour illustrer le principe des colonnes calculées en DAX, nous allons tenter de calculer la marge brute par catégorie de produits/services.

 

Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation  des données avec nos formations sur Power Query et Power Pivot dans Excel.

 

Étapes d’élaboration de colonnes calculés en DAX

D’abord, nous allons préparer un tableau croisé dynamique avec les ventes et les coûts des ventes par catégorie par année (voir image suivante).

DAX: Colonnes calculées

 

Pour déterminer la marge brute par catégorie, nous devrons effectuer une soustraction entre les ventes et le coût des ventes. Pour ce faire, nous ajouterons une colonne de données dans la table des ventes.

DAX: Colonnes calculées

 

Par la suite, nous ajusterons notre tableau croisé dynamique, afin qu’il présente la marge brute par catégorie.

DAX: Colonnes calculées

 

Il est donc possible d’utiliser les colonnes calculées, lorsque le calcul s’applique à chaque ligne d’une table et qu’il peut par la suite être agrégé selon une dimension donnée.

 

Champs calculés ou mesures en DAX

 

Quand utiliser une mesure en DAX ?

Dans certaines situations, toutefois, les calculs que vous devrez faire ne s’appliqueront pas à une logique de lignes individuelles d’une table mais plutôt à une aggrégation de lignes. Dans ce cas, vous devrez avoir recours aux champs calculés ou mesures en DAX. Poursuivons notre exemple avec le calcul de la marge brute par catégorie mais cette fois-ci, en % des ventes au lieu d’en $.

Vous pourriez ajouter une colonne avec un calcul supplémentaire dans la table des ventes pour obtenir la marge brute en %, ligne par ligne, mais lorsque viendrait le temps d’aggréger le tout par catégorie, Excel ferait la somme des % de marges, ce qui donnerait des résultats complètement erronés. Ce que l’on cherche à obtenir, c’est plutôt le % de la somme des marges par catégorie.

Voilà pourquoi vous devez utiliser les mesures en DAX plutôt que les colonnes calculées en DAX, dans ce contexte bien précis.

 

Comment ajouter une mesure en DAX ?

Pour ajouter une mesure en DAX, vous pouvez aller dans le menu Power Pivot et repérer l’option Champs calculés, puis cliquer sur Nouveau champs calculé (devenu Mesures dans les plus récentes versions d’Excel). Notons que vous pourriez aussi créer la mesure directement depuis l’interface Power Pivot.

DAX: Champs calculé

 

Vous pourrez ensuite indiquer dans quelle table vous souhaitez insérer ce nouveau champs calculé et vous pourrez le nommer et le décrire. Ensuite, il vous suffira d’inscrire la formule recherchée. Dans notre cas, nous souhaitons qu’Excel fasse d’abord la somme des marges en $ et divise ensuite le tout par la somme des ventes, de façon à obtenir une marge en %.

DAX - Champs calculé

 

Les mesures en DAX, comme les colonnes calculées en DAX, deviennent un nouveau champs à utiliser dans vos tableaux croisés dynamiques.

Marge brute %

 

Voici le résultat final:

Marge brute %

 

Formation complémentaire

Pour découvrir tout ce que pourra vous apporter Power Pivot, nous vous recommandons fortement notre formation Excel – Introduction à Power Pivot et à la modélisation de données.

 

Voici quelques commentaires d’apprenants ayant suivi cette formation

Le CFO masqué - Commentaires d'apprenants ayant suivi la formation : Excel - Introduction à Power Pivot et à la modélisation de données

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

4 réflexions sur “Power Pivot : Colonnes vs mesures en DAX”

  1. Yamin (From France)

    J’utilise quand c’est possible les exemples que vous venez de montrer, cela dit, ça se complique quand la table de données provient d’une comptabilité (lignes : x comptes comptables), on ne peut plus créer de colonne “coût” car la logique relationnelle (clé primaire) ne fonctionne que par lignes… Bref, il faut prévoir d’autres retraitements > Comptes en colonnes par exemples dans la table… Cdt

    1. Bonjour Yamin,

      Évidemment, afin de créer des tables de données en colonnes, il faut parfois (voire souvent) faire quelques manipulations d’abord mais généralement, ce processus de transformation peut être automatisé assez facilement.

      Merci,

      Sophie

  2. Ping : Power Pivot | Pearltrees

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut