PowerPivot: Colonnes et champs calculés en DAX

Publié le 10 juin 2014
par Sophie Marchand M.Sc., CPA, CGA, MVP
Marge brute %

PowerPivot: Colonnes et champs calculés en DAX

Si vous devez travailler avec PowerPivot (ou avec SQL Server Analysis Services Tabular), sachez qu’il existe un langage de programmation dédié qui se nomme DAX (pour Data Analysis Expression). Ce language puise ses racines dans les fonctions Excel mais comporte néanmoins certaines différences 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 par le language DAX, on retrouve les colonnes et 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

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 PowerPivot.  Pour illustrer le concept de colonnes calculées, nous allons travailler avec le modèle de données suivant.

PowerPivot - Schéma en étoile

.

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

.

Étapes d’élaboration

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

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. 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 de 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 champs calculés plutôt que les colonnes calculées, dans ce contexte bien précis.

Pour ajouter un champs calculé, vous devez aller dans le menu PowerPivot et repérer l’option Champs calculés, puis cliquer sur Nouveau champs calculé.

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 champs calculés, comme les colonnes calculées, deviennent un nouveau champs à utiliser dans vos tableaux croisés dynamiques.

Marge brute %

.

Voici le résultat final:

Marge brute %

 

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

4 réflexions sur “PowerPivot: Colonnes et champs calculés 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 de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top