Excel Power Pivot - Principes de base de DAX

DAX (Data Analysis eXpression)language est la langue de Power Pivot. DAX est utilisé par Power Pivot pour la modélisation des données et il est pratique pour vous de l'utiliser pour la BI en libre-service. DAX est basé sur des tables de données et des colonnes dans des tables de données. Notez qu'il n'est pas basé sur des cellules individuelles du tableau comme c'est le cas avec les formules et les fonctions dans Excel.

Vous apprendrez les deux calculs simples qui existent dans Modèle de données - Colonne calculée et Champ calculé dans ce chapitre.

Colonne calculée

La colonne calculée est une colonne du modèle de données qui est définie par un calcul et qui étend le contenu d'une table de données. Il peut être visualisé comme une nouvelle colonne dans un tableau Excel défini par une formule.

Extension du modèle de données à l'aide de colonnes calculées

Supposons que vous ayez des données de ventes de produits par région dans les tableaux de données et également un catalogue de produits dans le modèle de données.

Créez un tableau croisé dynamique avec ces données.

Comme vous pouvez le constater, le Power PivotTable a résumé les données de vente de toutes les régions. Supposons que vous souhaitiez connaître la marge brute réalisée sur chacun des produits. Vous connaissez le prix de chaque produit, le coût auquel il est vendu et le nombre d'unités vendues.

Cependant, si vous devez calculer la marge brute, vous devez avoir deux colonnes supplémentaires dans chacun des tableaux de données des régions - Prix total du produit et Bénéfice brut. En effet, le tableau croisé dynamique nécessite des colonnes dans les tables de données pour résumer les résultats.

Comme vous le savez, le prix total du produit correspond au prix du produit * Nombre d'unités et le profit brut correspond au montant total - Prix total du produit.

Vous devez utiliser les expressions DAX pour ajouter les colonnes calculées comme suit -

Cliquez sur l'onglet East_Sales dans l'affichage des données de la fenêtre Power Pivot pour afficher la table de données East_Sales.
Cliquez sur l'onglet Conception sur le ruban.
Cliquez sur Ajouter.

La colonne sur le côté droit avec l'en-tête - Ajouter une colonne est mise en évidence.

Type = [Product Price] * [No. of Units] dans la barre de formule et appuyez sur Enter.

Une nouvelle colonne avec en-tête CalculatedColumn1 est inséré avec les valeurs calculées par la formule que vous avez saisie.

Double-cliquez sur l'en-tête de la nouvelle colonne calculée.
Renommez l'en-tête en TotalProductPrice.

Ajoutez une colonne calculée supplémentaire pour le profit brut comme suit -

Cliquez sur l'onglet Conception sur le ruban.
Cliquez sur Ajouter.
La colonne sur le côté droit avec l'en-tête - Ajouter une colonne est mise en évidence.
Type = [TotalSalesAmount] − [TotaProductPrice] dans la barre de formule.
Appuyez sur Entrée.

Une nouvelle colonne avec en-tête CalculatedColumn1 est inséré avec les valeurs calculées par la formule que vous avez saisie.

Double-cliquez sur l'en-tête de la nouvelle colonne calculée.
Renommez l'en-tête en tant que bénéfice brut.

Ajoutez les colonnes calculées dans le North_Salestable de données de la même manière. En regroupant toutes les étapes, procédez comme suit -

Cliquez sur l'onglet Conception sur le ruban.
Cliquez sur Ajouter. La colonne sur le côté droit avec l'en-tête - Ajouter une colonne est mise en évidence.
Type = [Product Price] * [No. of Units] dans la barre de formule et appuyez sur Entrée.
Une nouvelle colonne avec en-tête CalculatedColumn1 est insérée avec les valeurs calculées par la formule que vous avez entrée.
Double-cliquez sur l'en-tête de la nouvelle colonne calculée.
Renommez l'en-tête en TotalProductPrice.
Cliquez sur l'onglet Conception sur le ruban.
Cliquez sur Ajouter. La colonne sur le côté droit avec l'en-tête - Ajouter une colonne est mise en évidence.
Type = [TotalSalesAmount] − [TotaProductPrice]dans la barre de formule et appuyez sur Entrée. Une nouvelle colonne avec en-têteCalculatedColumn1 est inséré avec les valeurs calculées par la formule que vous avez entrée.
Double-cliquez sur l'en-tête de la nouvelle colonne calculée.
Renommez l'en-tête en Gross Profit.

Répétez les étapes ci-dessus pour la table de données South Sales et la table de données West Sales.

Vous disposez des colonnes nécessaires pour résumer la marge brute. Maintenant, créez le Power PivotTable.

Vous êtes en mesure de résumer Gross Profit cela est devenu possible avec les colonnes calculées dans Power Pivot et tout peut être fait en quelques étapes sans erreur.

Vous pouvez le résumer par région pour les produits comme indiqué ci-dessous également -

Champ calculé

Supposons que vous souhaitiez calculer le pourcentage de profit réalisé par chaque région par produit. Vous pouvez le faire en ajoutant un champ calculé à la table de données.

Cliquez sous la colonne Bénéfice brut dans le East_Sales table dans la fenêtre Power Pivot.
Type EastProfit: = SUM ([Gross Profit]) / sum ([TotalSalesAmount]) dans la barre de formule.
Appuyez sur Entrée.

Le champ calculé EastProfit est inséré sous la colonne Gross Profit.

Cliquez avec le bouton droit sur le champ calculé - EastProfit.
Sélectionner Format dans la liste déroulante.

La boîte de dialogue Formatage s'affiche.

Sélectionner Number sous Catégorie.
Dans la zone Format, sélectionnez Pourcentage et cliquez sur OK.

Le champ calculé EastProfit est formaté en pourcentage.

Répétez les étapes pour insérer les champs calculés suivants -

NorthProfit dans la table de données North_Sales.
SouthProfit dans la table de données South_Sales.
WestProfit dans la table de données West_Sales.

Note - Vous ne pouvez pas définir plus d'un champ calculé avec un nom donné.

Cliquez sur le Power PivotTable. Vous pouvez voir que les champs calculés apparaissent dans les tableaux.

Sélectionnez les champs - EastProfit, NorthProfit, SouthProfit et WestProfit dans les tables de la liste Champs de tableau croisé dynamique.
Organisez les champs de manière à ce que le profit brut et le pourcentage de profit s'affichent ensemble. Le Power PivotTable se présente comme suit:

Note - Le Calculate Fields ont été appelés Measures dans les versions antérieures d'Excel.