Support formation Rapports Power BI

Labs Microsoft

Lab 04 | Créer des calculs DAX dans Power BI Desktop

⁠

Sales Analysis.pbix

611.6 kB

⁠

Histoire de laboratoire

Dans cet atelier, vous allez créer des tables calculées, des colonnes calculées et des mesures simples à l’aide d’expressions d’analyse de données (DAX).

Dans cet atelier, vous allez apprendre à :

Créer des tableaux calculés

Créer des colonnes calculées

Créer des mesures

Ce laboratoire devrait prendre environ 45 minutes.

Créer des tables calculées

Dans cet exercice, vous allez créer deux tables calculées. La première sera la table Salesperson, pour permettre une relation directe entre elle et la table Sales. La seconde sera la table Date.

Important : Si vous reprenez le labo précédent (et que vous l’avez terminé avec succès), n’effectuez pas cette tâche ; Au lieu de cela, continuez à partir de la tâche suivante.

Ouvrez Power BI Desktop.

⁠

Pour ouvrir le fichier Power BI Desktop de démarrage, sélectionnez Ouvrir > Parcourir cet appareil.

Dans la fenêtre Ouvrir, accédez au dossier D :\Allfiles\Labs\04-create-dax-calculations-in-power-bi-desktop\Starter et ouvrez le fichier Sales Analysis. Remarque : À ce stade, Power BI vous demandera de vous connecter si vous ne l’avez pas déjà fait. Vous pouvez vous connecter ou sélectionner Annuler et poursuivre le labo.

Fermez toutes les fenêtres d’information qui peuvent s’ouvrir.

Remarquez le message d’avertissement sous le ruban. Ce message vous avertit que les requêtes n’ont pas été appliquées pour se charger en tant que tables de modèle. Vous appliquerez les requêtes plus tard dans cet atelier. Pour ignorer le message d’avertissement, à droite du message d’avertissement, sélectionnez X.

Pour créer une copie du fichier, accédez à Fichier > Enregistrer sous et enregistrez dans le dossier D :\Allfiles\MySolution.

Si vous êtes invité à appliquer les modifications, sélectionnez Appliquer plus tard.

Créer la table Salesperson (Vendeur)

Dans cette tâche, vous allez créer la table calculée Salesperson (relation directe avec Sales).

Une table calculée est créée en entrant d’abord le nom de la table, suivi du symbole égal (=), suivi d’une formule DAX qui renvoie une table. Le nom de la table ne peut pas déjà exister dans le modèle de données.

La barre de formule prend en charge la saisie d’une formule DAX valide. Il comprend des fonctionnalités telles que la saisie semi-automatique, Intellisense et le code couleur, vous permettant de saisir rapidement et précisément la formule.

Dans Power BI Desktop, en mode Rapport, dans le ruban Modélisation, dans le groupe Calculs, sélectionnez Nouvelle table.

⁠

Dans la barre de formule (qui s’ouvre directement sous le ruban lors de la création ou de la modification de calculs), tapez Salesperson =, appuyez sur Maj+Entrée, tapez 'Salesperson (Performance)', puis appuyez sur Entrée.

⁠

Pour plus de commodité, toutes les définitions DAX de cet atelier peuvent être copiées à partir du fichier d’extraits de code, situé dans D :\Allfiles\Labs\04-create-dax-calculations-in-power-bi-desktop\Assets\Snippets.txt. Cette définition de table crée une copie de la table Salesperson (Performance). Il copie uniquement les données, mais les propriétés du modèle telles que la visibilité, la mise en forme, etc. ne sont pas copiées. Conseil : Nous vous encourageons à saisir des « espaces blancs » (c’est-à-dire des retours chariot et des tabulations) pour écrire des formules dans un format intuitif et facile à lire, en particulier lorsque les formules sont longues et complexes. Pour saisir un retour chariot, appuyez sur Maj+Entrée. Les « espaces blancs » sont facultatifs.

Dans le volet Données, notez que l’icône de la table est précédée d’une calculatrice supplémentaire (indiquant une table calculée).

⁠

Remarque : Les tables calculées sont définies à l’aide d’une formule DAX qui renvoie une table. Il est important de comprendre que les tables calculées augmentent la taille du modèle de données car elles matérialisent et stockent des valeurs. Elles sont recalculées chaque fois que les dépendances de formule sont actualisées, comme ce sera le cas pour ce modèle de données lorsque de nouvelles valeurs de date (futures) sont chargées dans les tables. Contrairement aux tables provenant de Power Query, les tables calculées ne peuvent pas être utilisées pour charger des données à partir de sources de données externes. Elles ne peuvent transformer les données qu’en fonction de ce qui a déjà été chargé dans le modèle de données.

Basculez vers la vue Modèle et notez que la table Salesperson est disponible (vous devrez peut-être réinitialiser la vue pour trouver la table).

Créer une relation à partir du Salesperson | EmployeeKey de la colonne Sales | Colonne EmployeeKey.

Cliquez avec le bouton droit sur la relation inactive entre les tables Salesperson (Performances) et Sales, puis sélectionnez Supprimer. Lorsque vous êtes invité à confirmer la suppression, sélectionnez Oui.

Dans le tableau Salesperson, sélectionnez plusieurs colonnes suivantes, puis masquez-les (définissez la propriété Est masqué sur Oui):

ID de l’employé

Clé de l’employé

UPN

Dans le diagramme du modèle, sélectionnez la table Salesperson.

Dans le volet Propriétés, dans la zone Description, entrez : Vendeur associé aux ventes. Vous vous souvenez peut-être que les descriptions apparaissent sous forme d’info-bulles dans le volet Données lorsque l’utilisateur passe son curseur sur une table ou un champ.

Pour le tableau Salesperson (performances), définissez la description sur : Vendeur associé à la ou aux régions

Le modèle de données offre désormais deux alternatives lors de l’analyse des vendeurs. La table Vendeur permet d’analyser les ventes réalisées par un vendeur, tandis que la table Vendeur (Performance) permet d’analyser les ventes réalisées dans la ou les régions de vente affectées au vendeur.

Créer la table Date

Dans cette tâche, vous allez créer la table Date.

Passez en mode Tableau. Sous l’onglet Accueil du ruban, dans le groupe Calculs, sélectionnez Nouvelle table.

⁠

Dans la barre de formule, entrez ce qui suit : CALENDARAUTO(6) La fonction CALENDARAUTO() renvoie une table à une seule colonne composée de valeurs de date. Le comportement « auto » analyse toutes les colonnes de date du modèle de données pour déterminer les valeurs de date les plus anciennes et les plus récentes stockées dans le modèle de données. Il crée ensuite une ligne pour chaque date de cette plage, étendant la plage dans les deux sens pour garantir le stockage des années complètes de données. Cette fonction peut prendre un seul argument facultatif qui est le numéro du dernier mois d’une année. Lorsqu’elle est omise, la valeur est 12, ce qui signifie que décembre est le dernier mois de l’année. Dans ce cas, 6 est saisi, ce qui signifie que juin est le dernier mois de l’année

Notez la colonne des valeurs de date.

⁠

Les dates affichées sont mises en forme à l’aide des paramètres régionaux des États-Unis (c’est-à-dire mm/jj/aaaa).

Dans le coin inférieur gauche, dans la barre d’état, remarquez les statistiques du tableau, confirmant que 1826 lignes de données ont été générées, ce qui représente les données de cinq années complètes.

⁠

Créer des colonnes calculées

Dans cette tâche, vous allez ajouter d’autres colonnes pour activer le filtrage et le regroupement par différentes périodes. Vous allez également créer une colonne calculée pour contrôler l’ordre de tri des autres colonnes.

⁠

Snippets.txt

2.1 kB

⁠

Dans le ruban contextuel Outils de table, dans le groupe Calculs, sélectionnez Nouvelle colonne.

⁠

Dans la barre de formule, tapez ce qui suit (ou copiez à partir du fichier d’extraits de code), puis appuyez sur Entrée: Year = "FY" & YEAR('Date'[Date]) + IF(MONTH('Date'[Date]) > 6, 1) Une colonne calculée est créée en entrant d’abord le nom de la colonne, suivi du symbole égal (=), suivi d’une formule DAX qui renvoie un résultat à valeur unique. Le nom de la colonne ne peut pas déjà exister dans la table. La formule utilise la valeur de l’année de la date, mais ajoute un à la valeur de l’année lorsque le mois est après juin. C’est ainsi que les exercices financiers d’Adventure Works sont calculés.

Vérifiez que la nouvelle colonne a été ajoutée.

⁠

Utilisez les définitions du fichier Snippets.txt (ou utiliser le code ci-dessous) pour créer les deux colonnes calculées suivantes pour la table Date :

Quarter Quarter = 'Date'[Year] & " Q" & IF( MONTH('Date'[Date]) <= 3, 3, IF( MONTH('Date'[Date]) <= 6, 4, IF( MONTH('Date'[Date]) <= 9, 1, 2 ) ) )

Month Month = FORMAT('Date'[Date], "yyyy MMM")

⁠

Pour valider les calculs, passez en mode Rapport.

Pour créer une nouvelle page de rapport, sélectionnez l’icône plus en regard de Page 1.

⁠

Pour ajouter un visuel matriciel à la nouvelle page de rapport, dans le volet Visualisations, sélectionnez le type de visuel matriciel. Conseil : Vous pouvez placer le curseur sur chaque icône pour afficher une info-bulle décrivant le type de visuel.

⁠

Dans le volet Données, à partir de la table Date, faites glisser le champ Année dans le cadre/zone Lignes.

⁠

Faites glisser le champ Month dans le cadre/zone Lignes, directement sous le champ Year.

⁠

En haut à droite du visuel matriciel (ou en bas, selon l’emplacement du visuel), sélectionnez l’icône en forme de double flèche fourchue (qui s’étendra toutes les années d’un niveau vers le bas).

⁠

Notez que les années s’étendent en mois, et que les mois sont triés par ordre alphabétique plutôt que chronologique.

⁠

Par défaut, les valeurs de texte sont triées par ordre alphabétique, les nombres sont triés du plus petit au plus grand et les dates sont triées du plus ancien au plus récent.

Pour personnaliser l’ordre de tri des champs Mois, passez en mode Tableau.

Ajoutez la colonne MonthKey à la table Date. MonthKey = (YEAR('Date'[Date]) * 100) + MONTH('Date'[Date]) Cette formule calcule une valeur numérique pour chaque combinaison année/mois.

En mode Tableau, vérifiez que la nouvelle colonne contient des valeurs numériques (par exemple, 201707 pour juillet 2017, etc.).

⁠

Revenez à la vue Rapport. Dans le volet Données, assurez-vous que le champ Mois est sélectionné (lorsqu’il est sélectionné, il aura un arrière-plan gris foncé).

Dans le ruban contextuel Outils de colonne, dans le groupe Trier, sélectionnez Trier par colonne, puis sélectionnez Clé du mois.

⁠

Dans le visuel de la matrice, notez que les mois sont maintenant triés chronologiquement.

⁠

Terminer la table Date

Dans cette tâche, vous allez terminer la conception de la table Date en masquant une colonne et en créant une hiérarchie. Vous créerez ensuite des relations avec les tables Sales et Targets.

Passez à la vue Modèle. Dans la table Date, masquez la colonne MonthKey (définissez Est masqué sur Oui).

Dans le volet droit Données, sélectionnez la table Date, sélectionnez la colonne Year avec le bouton droit de la souris, puis sélectionnez Créer une hiérarchie.

Renommez la hiérarchie nouvellement créée en Fiscal en sélectionnant avec le bouton droit de la souris et en renommant.

Ajoutez les deux champs suivants à la hiérarchie fiscale en les sélectionnant dans le volet Données, cliquez avec le bouton droit de la souris, sélectionnez Ajouter à la hiérarchie -> Fiscal.

Quarter

Month

⁠

Créez les deux relations de modèle suivantes :

Date | Date à Sales | OrderDate

Date | Date à Targets | TargetMonth

Masquez les deux colonnes suivantes :

Sales | OrderDate

Targets | TargetMonth

Marquer la table Date

Dans cette tâche, vous allez marquer la table Date en tant que table de dates.

Passez en mode Rapport. Dans le volet Données, sélectionnez la table Date (et non le champ Date).

Dans le ruban contextuel Outils de tableau, dans le groupe Calendriers, sélectionnez Marquer comme table de dates.

Dans la fenêtre Marquer comme table de dates, faites glisser la propriété Marquer comme table de dates sur Oui et dans la liste déroulante Choisir une colonne de date, sélectionnez Date. Sélectionnez Enregistrer.

⁠

Enregistrez le fichier Power BI Desktop. Power BI Desktop comprend maintenant que cette table définit la date (heure). C’est important lorsque l’on s’appuie sur des calculs d’intelligence temporelle. Vous allez utiliser des calculs d’intelligence temporelle dans le laboratoire Créer des calculs DAX avancés dans Power BI Desktop. Cette approche de conception pour une table de dates convient lorsque vous n’avez pas de table de dates dans votre source de données. Si vous disposez d’un entrepôt de données, il serait approprié de charger des données de date à partir de sa table de dimension de date plutôt que de « redéfinir » la logique de date dans votre modèle de données.

Créez des mesures simples

Dans cette tâche, vous allez créer des mesures simples. Les mesures simples regroupent les valeurs dans une seule colonne ou comptent les lignes d’une table.

En mode Rapport, sur la page 2, dans le volet Données, faites glisser le bouton Sales | Unit Price dans le visuel de matrice.

⁠

Vous vous souvenez peut-être que dans le laboratoire Données de modèle dans Power BI Desktop, vous définissez la colonne Unit Price pour qu’elle soit résumée par Moyenne. Le résultat que vous voyez dans le visuel de matrice est le prix unitaire moyen mensuel (somme des valeurs de prix unitaires divisée par le nombre de prix unitaires).

Dans le volet Champs visuels (situé sous le volet Visualisations), dans le champ/zone Valeurs, notez que Unit Price est répertorié.

⁠

Sélectionnez la flèche vers le bas pour Unit Price, puis remarquez les options de menu disponibles.

⁠

Les colonnes numériques visibles permettent aux auteurs de rapports au moment de la conception du rapport de décider comment les valeurs des colonnes seront résumées (ou non). Cela peut entraîner des rapports inappropriés. Cependant, certains modélisateurs de données n’aiment pas laisser les choses au hasard et choisissent de masquer ces colonnes et d’exposer à la place une logique d’agrégation définie dans des mesures. C’est l’approche que vous allez maintenant adopter dans ce laboratoire.

Pour créer une mesure, dans le volet Données, cliquez avec le bouton droit sur la table Sales, puis sélectionnez Nouvelle mesure.

Dans la barre de formule, ajoutez la définition de mesure suivante : Avg Price = AVERAGE(Sales[Unit Price])

Ajoutez la mesure Avg Price au visuel de matrice.

Notez qu’il produit le même résultat que la colonne Unit Price (mais avec une mise en forme différente).

Dans la zone Valeurs, ouvrez le menu contextuel du champ Unit Price et notez qu’il n’est pas possible de modifier la technique d’agrégation.

⁠

Il n’est pas possible de modifier le comportement d’agrégation d’une mesure.

Utilisez les définitions de fichier d’extraits de code pour créer les cinq mesures suivantes pour la table Sales :

Median Price (Prix médian)

Min Price (Prix minimal)

Max Price (Prix maximal)

Orders (Commandes)

Order Lines (Lignes de commande) La fonction DISTINCTCOUNT() utilisée dans la mesure Orders ne comptera les commandes qu’une seule fois (en ignorant les doublons). La fonction COUNTROWS() utilisée dans la mesure Order Lines fonctionne sur une table. Dans ce cas, le nombre de commandes est calculé en comptant les valeurs de colonne SalesOrderNumber distinctes, tandis que le nombre de lignes de commande est simplement le nombre de lignes de table (chaque ligne est une ligne d’une commande).

Passez à la vue Modèle, puis sélectionnez les quatre mesures de prix : Avg Price (Prix moyen), Max Price (Prix maximal), Median Price (Prix médian) et Min Price (Prix minimal).

Pour la sélection multiple de mesures, configurez les exigences suivantes :

Définissez le format à deux décimales

Affecter à un dossier d’affichage nommé Pricing (Tarifs)

⁠

Masquer la colonne Unit Pricing. La colonne Unit Pricing n’est plus disponible pour les auteurs de rapports. Ils doivent utiliser les mesures de tarification que vous avez ajoutées au modèle. Cette approche de conception garantit que les auteurs de rapports n’agrègeront pas les prix de manière inappropriée, par exemple en les additionnant.

Sélectionnez les mesures Order Lines (Lignes de commandes) et Orders (Commandes), puis configurez les exigences suivantes :

Définir le format à l’aide du séparateur de milliers

Affecter à un dossier d’affichage nommé Counts

⁠

En mode Rapport, dans la zone Valeurs du visuel de matrice, pour le champ Unit Price, sélectionnez X pour le supprimer.

⁠

Augmentez la taille du visuel de matrice pour remplir la largeur et la hauteur de la page.

Ajoutez les cinq mesures suivantes au visuel de matrice :

Median Price (Prix médian)

Min Price (Prix minimal)

Max Price (Prix maximal)

Orders (Commandes)

Order Lines (Lignes de commande)

Vérifiez que les résultats sont sensés et correctement formatés.

⁠

Créer des mesures supplémentaires

Dans cette tâche, vous allez créer d’autres mesures qui utilisent des formules plus complexes.

En mode Rapport, sélectionnez Page 1 et examinez le visuel de table, en notant le total de la colonne Cible.

⁠

Sélectionnez le visuel de table, puis dans le volet Visualisations, supprimez le champ Cible.

Renommer les Targets | Target en Targets | TargetAmount. Conseil : Il existe plusieurs façons de renommer la colonne en mode Rapport : dans le volet Données, vous pouvez cliquer avec le bouton droit sur la colonne, puis sélectionner Renommer, ou double-cliquer sur la colonne ou appuyer sur F2. Vous êtes sur le point de créer une mesure nommée Cible. Il n’est pas possible d’avoir une colonne et une mesure dans la même table avec le même nom.

Créez la mesure suivante dans la table Cibles : Target = IF( HASONEVALUE('Salesperson (Performance)'[Salesperson]), SUM(Targets[TargetAmount]) ) La fonction HASONEVALUE() teste si une seule valeur de la colonne Vendeur est filtrée. Lorsque la valeur est true, l’expression renvoie la somme des montants cibles (pour ce vendeur uniquement). Lorsque la valeur est false, BLANK est renvoyé.

Mettez en forme la mesure cible pour zéro décimale. Conseil : Vous pouvez utiliser le ruban contextuel Outils de mesure.

Masquez la colonne TargetAmount. Conseil : Vous pouvez cliquer avec le bouton droit sur la colonne dans le volet Données, puis sélectionner Masquer.

Ajoutez la mesure Target au visuel de table.

Notez que le total de la colonne cible est maintenant VIDE.

⁠

Utilisez les définitions de fichier d’extraits de code pour créer les deux mesures suivantes pour la table Cibles :

Variance

Marge de variance

Mettez en forme la mesure de variance pour zéro décimale.

Formatez la mesure de la marge de variance en pourcentage avec deux décimales.

Ajoutez les mesures Variance et Marge de variance au visuel de table.

Redimensionnez le visuel de tableau afin que toutes les colonnes et lignes puissent être vues.

⁠

Bien qu’il semble que tous les vendeurs n’atteignent pas l’objectif, n’oubliez pas que le visuel du tableau n’est pas encore filtré par période spécifique. Vous allez générer des rapports sur les performances des ventes qui filtrent en fonction d’une période sélectionnée par l’utilisateur dans le laboratoire Concevoir un rapport dans Power BI Desktop.

Notez que le tableau Cibles apparaît maintenant en haut de la liste.

⁠

Les tableaux qui ne comprennent que des mesures visibles sont automatiquement répertoriés en haut de la liste.

Finir

Enregistrez le fichier Power BI Desktop.

Vous allez améliorer le modèle de données avec des calculs plus avancés à l’aide de DAX dans le laboratoire Créer des calculs DAX avancés dans Power BI Desktop.

Histoire de laboratoire

Créer des tables calculées

Créer la table Salesperson (Vendeur)

Créer la table Date

Créer des colonnes calculées

Terminer la table Date

Marquer la table Date

Créez des mesures simples

Créer des mesures supplémentaires

Finir

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.