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.
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).
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.
Par la suite, nous ajusterons notre tableau croisé dynamique, afin qu’il présente la marge brute par catégorie.
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.
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 %.
Les mesures en DAX, comme les colonnes calculées en DAX, deviennent un nouveau champs à utiliser dans vos tableaux croisés dynamiques.
Voici le résultat final:
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
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
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
Merci beaucoup pour ce blog
J’y passe au moins 02h par jour
Ping : Power Pivot | Pearltrees