Tout savoir sur Power BI

Mesurer les indicateurs

Après avoir obtenu les données et relier les tables entre elles, il s’agira de procéder aux mesures complémentaires pour révéler les indicateurs nécessaires à un décisionnel efficace.

3️⃣ façons de calculer dans Power BI

  1. Calculer avec une transformation ou un ajout de colonne dans Power Query grâce aux onglets Transformer ou Ajouter une colonne, ou directement par une formule en langage M.
  2. Calculer avec un ajout de colonne par l’outil Nouvelle colonne de l’onglet Modélisation de Power BI.
  3. Calculer sans ajout de colonne par une mesure directe sur une table entière avec le langage DAX (Data Analysis eXpression), comme par exemple avec les fonctions avancées d’agrégation SUMX ou COUNTX, ou avec les fonctions de filtrage CALCULATE ou FILTER. Les nouveaux calculs de visuels permettent également de mesurer sans ajout de colonnes dans le modèle de données.

🔥 Dans tous les cas, pour optimiser les calculs il n’y a pas vraiment de règles, juste des bonnes pratiques à savoir mettre en œuvre.

5️⃣ choix de calculs

  1. Ajouter des colonnes à la source via Excel avant l’importation dans Power BI, ce qui sera toujours la moins bonne solution. C’est celle qui alourdira le plus le traitement, à tous les niveaux, même en utilisant Query et Pivot dans Excel, puisqu’elle oblige à renouveler l’opération à chaque modification de la source. Méthode à proscrire !
  2. Ajouter des colonnes via Query dans Power BI à l’aide des boutons de l’onglet Ajouter une colonne pour ne pas avoir à utiliser le langage M. Cette solution automatique, bien que meilleure que la première, alourdira le modèle de données avec des colonnes supplémentaires. A éviter.
  3. Ajouter des colonnes via DAX avec Power Pivot dans Power BI grâce à l’outil Nouvelle colonne de l’onglet Modélisation, équivalent aux calculs implicites effectués automatiquement par Power BI sur toutes les colonnes de type numérique. Méthode à privilégier si les besoins d’agrégations sur la colonne sont importants : somme, moyenne, nombre, min, max, écart type. Cette méthode évite alors la multiplication de mesures DAX de type SUM, AVERAGE, COUNT, MIN, MAX, etc..
  4. Effectuer des mesures, le meilleur choix, qui consiste à faire le minimum nécessaire et indispensable avec Query puis à se concentrer sur les mesures ou les calculs de visuels avec DAX. Le langage DAX permet de calculer directement sans créer de nouvelle colonne et donc sans alourdir les tables ni le modèle de données. En effet, les mesures ne sont pas stockées dans le modèle et recalculent à la demande. Elles sont plus performantes que les colonnes calculées. Les mesures rapides et les calculs de visuels permettront de simplifier la génération de code DAX pour les calculs courants.
  5. Ajouter des tables entières, si cela est justifié par les besoins du projet, en dupliquant des requêtes dans Power Query ou en utilisant des mesures de tables avec DAX.

🔥 C’est à cette étape, avant de commencer à calculer avec DAX, que sera créé une nouvelle table nommée _Mesures pour accueillir toutes les mesures DAX par clic droit sur la table puis Nouvelle mesure. Les calculs en colonne se feront dans la table correspondante par clic droit sur la table concernée puis Nouvelle Colonne.

🐱 Pensez à l’IA

Dans tous les cas, et quelque soit la formule à générer, pensez toujours à l’IA pour vous assister. Ce serait dommage de s’en passer.

Un prompt du type : « Formule DAX nommée CA TOTAL qui affiche la somme de la colonne Quantité de la table Ventes multipliée par la colonne Prix de la table liée Produits. » donnera toujours une réponse fiable que l’on pourra copier coller directement dans une nouvelle mesure. Il suffira d’être précis sur le nom des colonnes et des tables qui entrent en jeu dans le calcul souhaité.

L’IA, et Claude AI en particulier, sera également capable à partir de prompts précis et circonstancié de modifier le code M d’une requête dans Power Query (onglet Accueil ou Affichage puis Editeur avancé), ainsi que le script TMDL (Tabular Model Definition Language) d’un modèle sémantique (Vue TMDL puis Onglet Script sur les 3 petits points en regard de la table _Mesures). Il suffira alors de copier coller le nouveau code à la place de l’ancien pour voir les modifications s’appliquer instantanément.

🧮 Les Groupes de calculs

Les groupes de calcul vous permettent de définir une seule fois un modèle d’analyse temporelle et de l’appliquer dynamiquement à n’importe quelle mesure de votre modèle.

Par exemple, au lieu d’écrire les 3 mesures DAX suivantes qui permettent de calculer les montants totaux de CA, de Profit et de Coût depuis le début de l’année :

DAX
YTD CA = TOTALYTD( [CA Total] , Calendrier[Date] )
YTD Profit = TOTALYTD( [Profit Total] , Calendrier[Date] )
YTD Coût = TOTALYTD( [Coût Total] , Calendrier[Date] )

Vous ne créez qu’un seul élément de Groupe de calcul du type suivant :

DAX
YTD = TOTALYTD( SELECTEDMEASURE() , Calendrier[Date] )

La fonction SELECTEDMEASURE() référence dynamiquement la mesure affichée dans votre visualisation. Ajoutez quelques éléments supplémentaires pour le cumul mensuel MTD, le cumul trimestriel QTD et l’année précédente, glissez la colonne Groupe de calcul correspondant dans un segment, et vos utilisateurs pourront basculer instantanément entre les perspectives temporelles.

Comment configurer les Groupes de calculs

Vous pouvez le faire directement dans Power BI Desktop ; aucun outil externe n’est requis.

Étape 1 :  Passez en mode Modèle dans Power BI Desktop (l’icône dans la barre latérale gauche qui ressemble à un diagramme de base de données).

Étape 2 :  Dans le ruban, cliquez sur Groupe de calcul. Power BI crée une nouvelle table de groupe de calcul et vous fournit automatiquement votre premier élément de calcul.

Étape 3 :  Renommez l’élément (par exemple, YTD) et définissez son expression DAX dans le volet Propriétés :

  • YTD :  TOTALYTD( SELECTEDMEASURE() , Calendrier[Date] )
  • QTD : TOTALQTD( SELECTEDMEASURE() , Calendrier[Date] )
  • MTD :  TOTALMTD( SELECTEDMEASURE() , Calendrier[Date] )
  • PY :  CALCULATE( SELECTEDMEASURE() , SAMEPERIODLASTYEAR( Calendrier[Date] ) )

Étape 4 :  Ajoutez d’autres éléments de calcul en cliquant avec le bouton droit sur le nœud Éléments de calcul dans le volet Propriétés. Nommez-les, collez l’expression, et c’est terminé.

Étape 5 :  Revenez à l’affichage Rapport. Un nouveau tableau apparaît dans le volet Champs, avec une colonne contenant les noms de vos articles : YTD, QTD, MTD, PY. Glissez-déposez-le dans un segment ou dans les lignes/colonnes d’une matrice.

5 minutes de configuration remplacent des heures de création répétitive de mesures par copier coller, réduisent le nombre de calculs et optimisent le modèle de données.

Conseil : Si vous êtes un utilisateur qui privilégie les scripts ou a besoin d’opérations par lots, Tabular Editor est une solution d’édition avancée des modèles Power BI sous format PBIP. Elle devient encore plus puissante avec l’ajout d’une API IA. Mais pour la plupart des équipes, l’interface utilisateur native suffira pour créer des groupes de calculs efficaces.

💡 Les commandes VAR et RETURN

La combinaison des commandes VAR et RETURN à l’intérieur d’une expression permet de limiter le nombre de mesures DAX dans le modèle, et donc de l’optimiser et d’en accélérer l’actualisation.

Par exemple, la mesure suivante affichera la variation en + ou en – entre la valeur de la somme des Quantités du mois en cours et celle du mois précédent en passant par un calcul intermédiaire.

DAX
QTE MoM = 
VAR
Mois en cours =
SUM(
Ventes[Quantité]
)
VAR
Mois -1 =
CALCULATE(
SUM(Ventes[Quantité]), PREVIOUSMONTH(
Calendrier[Date])
)
RETURN
[Mois en cours]
/
[Mois -1]
-1

💡 On peut appliquer à cette mesure un format personnalisé du type à copier/coller ci-dessous dans la partie Modélisation pour voir apparaître le signe « + » devant les variations positives, et le signe « – » devant les variations négatives, tout en conservant un format numérique au pourcentage et pouvoir lui appliquer une mise en forme conditionnelle :
+0.00\ %;-0.00\ %;0.00\ %

La mesure suivante affichera dans un visuel Carte simple le TOP PAYS, c’est à dire le nom du pays dont la quantité cumulée vendue est la plus importante. Il suffira de modifier le paramètre DESC de la fonction DAX TOPN en le remplaçant par l’option ASC pour obtenir le FLOP PAYS, c’est à dire le pays pour lequel la même quantité cumulée est la plus petite.

DAX
TOP PAYS =
VAR
TOP_PAYS = 
TOPN(
1,
SUMMARIZECOLUMNS(
Ventes[Pays],
"CUMUL QUANTITE",
SUM(Ventes[Quantité])
),
[CUMUL QUANTITE],
DESC
)
RETURN
CONCATENATEX( 
TOP_PAYS,
Ventes[Pays]
)

💡 Les valeurs TOP peuvent également être affichées sans calcul supplémentaire à partir du volet latéral de Filtres à l’aide du type de filtrage avancé N premiers.

👁️ Vue de requête DAX

Comme un « bac à sable » expérimental, la Vue de requête DAX dans le volet latéral de gauche de Power BI Desktop permet d’évaluer des mesures dans une table avant de les intégrer au modèle de données. Le code suivant, puis l’appui sur le bouton Exécuter, permet par exemple de vérifier si la mesure CA TOTAL est correcte avant d’être éventuellement copiée collée dans le modèle. Le bouton Format permet d’incrémenter automatiquement la mesure évaluée pour améliorer sa lisibilité.

DAX
EVALUATE
SUMMARIZECOLUMNS(
"CA TOTAL",
SUMX(
Ventes,
Ventes[Quantité]*
RELATED(
Produits[Prix])
))

C’est l’occasion de tester la fonction DAX SUMMARIZE qui permet de créer un tableau croisé dynamique dans Power BI, comme on peut le faire de façon automatique dans Excel à partir de l’onglet Insertion ou de la nouvelle fonction GROUPER.PAR. Avec DAX, il s’agit de la fonction SUMMARIZE qui crée une table de valeurs cumulées, par exemple ici la table résumée des catégories de produits et de leur quantité cumulée, que l’on pourra ensuite créer dans le modèle de données sous le nom CumulQte.

DAX
EVALUATE
SUMMARIZE(
Ventes,
Ventes[Catégorie],
"Quantité",
SUM(
Ventes[Quantité]
))

💡 L’ajout dans la même table d’une mesure de type :

DAX
TOP CATEGORIE =
LOOKUPVALUE( CumulQte[Catégorie], CumulQte[Quantité], 
MAX(
CumulQteProduit[Quantité]
))

permettra d’afficher dans un visuel Carte le Top Produit de la table Cumul Qté créée précédemment. Remarque : LOOKUPVALUE, équivalent DAX des fonctions RECHERCHEV ou X dans Excel.

Inversement, un clic droit dans sur une mesure ou une colonne existante dans le volet Données de la vue Affichage du rapport, puis option Requêtes rapides, permet d’afficher et d’évaluer les valeurs de la colonne ou de la mesure sans passer par un visuel.

🎰 Calculs avancés

Pour les utilisateurs qui privilégient les scripts ou ont besoin d’opérations par lots, Tabular Editor est une solution d’édition avancée des modèles Power BI sous format PBIP. Elle devient encore plus puissante avec l’ajout d’une API IA quelconque.

🌐 Aller plus loin
👉 C’est à vous !
  • Créez une table vide nommée _Mesures pour accueillir toutes les mesures DAX.
  • Ajoutez une nouvelle colonne CA dans la table Ventes.
  • Calculez les valeurs de QUANTITE TOTALE et de CA TOTAL par des mesures globales en DAX à l’aide des fonctions SUM et SUMX.
  • Calculez les valeurs de QUANTITE MOYENNE et de CA MOYEN par des mesures globales en DAX à l’aide des fonctions AVERAGE et AVERAGEX.
  • Affichez les TOP de chaque dimension, Catégorie, Pays et Continent, à partir du volet de Filtres ou de mesures DAX.
  • Utilisez la Vue de requêtes DAX ou des visuels Carte simple pour vérifier les résultats, à partir des colonnes ou des mesures calculées.
  • Testez la bonne répartition de ces valeurs par Catégorie, par Pays ou par Continent à l’aide de visuels Table.
  • Comparez vos résultats avec la page Mesurer du rapport de référence POWER-BI.FR.pbix.
❓Testez-vous sur cette partie

Le test se trouve à la fin de la prochaine partie Filtrer les données

💪 Autoformation

Suivez pas à pas les ateliers 6 et 7 pour vous muscler sur cette partie

In

, ,
, , , ,