Calculs visuels, ou comment éviter les formules DAX à rallonge dans Power BI ?

Introduction

Power BI est un merveilleux outil de visualisation des données. Effectivement, il dispose de nombreux visuels, l'interface est simple et efficace, l'ETL Power Query est redoutable pour traiter les données, la similarité avec des fonctionnalités des logiciels Microsoft fait qu'on se sent plus à l'aise qu'avec des outils comme Qlik ou Tableau... Cependant, l'adoption de Power BI à son plein potentiel est compliquée. Cela est notamment dû à la maîtrise du DAX, le langage de Power BI.

Vous pouvez être le meilleur sur Power Query, la modélisation des données et le design, vous ne pouvez pas produire des rapports précis, valides et cohérents sans connaître le DAX et ses subtilités. Vous allez peut-être réussir à mettre en production 1 rapport ou 2, mais dès qu'on a besoin de précision, on va avoir besoin d'aborder les notions de contexte de filtre, contexte de ligne, transition de contexte etc. Autant vous dire que vous allez devoir faire beaucoup d'efforts pour être serein dans Power BI.

En septembre 2024, Microsoft a mis en disponibilité générale une solution potentielle à ce problème de complexité du DAX : les calculs visuels. Dans cet article, on va répondre à plusieurs questions les concernant. Qu'est-ce qu'un calcul visuel ? Pourquoi utiliser des calculs visuels ? Comment les utiliser ? Quelles sont leurs limites ?

Qu'est-ce qu'un calcul visuel dans Power BI ?

Un calcul visuel est un calcul DAX défini et exécuté directement sur un visuel (Microsoft). Comme le résume la documentation officielle de Power BI, un calcul visuel, c'est juste un calcul DAX qui est réalisé directement dans un visuel. Il ne s'agit pas ici de créer ce qu'on appelle des mesures, calculs DAX réutilisables dans d'autres visuels ou mesures.

Mais alors, quel est l'intérêt de faire des calculs directement au niveau d'un visuel ? N'est-ce pas mieux de faire des mesures pour alimenter des visuels tout en ayant la possibilité de les réutiliser ailleurs ? Quelle est la plus-value des calculs visuels dans Power BI ?

Pourquoi utiliser les calculs visuels dans Power BI ?

• On écrit beaucoup moins de DAX. Un cumul, une moyenne mobile, un classement... ça tient en une ligne, là où il fallait 3 ou 4 fonctions imbriquées avant.
• On n'a plus besoin de se battre avec les contextes de filtre et de ligne pour des calculs de présentation basiques. RUNNINGSUM, PREVIOUS, MOVINGAVERAGE font le travail directement.
• Le modèle sémantique reste propre. Plus besoin de créer 10 mesures techniques juste pour afficher un cumul ou une variation sur un seul visuel. On encombre moins le volet Données.
• Les totaux et sous-totaux deviennent facilement manipulables avec COLLAPSE et COLLAPSEALL. C'est un vrai soulagement quand on connaît la galère des totaux recalculés en DAX classique.
• L'itération est ultra-rapide. On écrit, on voit le résultat sur le visuel, on ajuste. On se rapproche du confort d'Excel et de ses tableaux croisés dynamiques.
• Les profils qui viennent d'Excel s'y retrouvent vite. La logique "je calcule par rapport à la ligne précédente / au total de la colonne / au parent", ils la connaissent déjà !
• Côté performance, c'est positif. Contrairement à une mesure classique qui va chercher les données au niveau détaillé avant de les agréger, le calcul visuel travaille directement sur les données déjà agrégées du visuel.

La promesse de simplicité à la calcul comme dans Excel est ambitieuse. Toutefois, il faut nuancer et préciser qu'il y a un jargon à connaître dans le monde des calculs visuels.

Définitions et exemples

Les Modèles

Les modèles sont des formules pré-faites pour accomplir des tâches comme le calcul de parts ou de cumuls par exemple.

Voici la liste :

• Somme cumulée : le cumul, le fameux running total
• Moyenne mobile : la moyenne sur les x dernières valeurs, pour lisser des ventes et repérer les anomalies par exemple
• Pourcentage du parent : permet d'obtenir la part des éléments par rapport à un élément au-dessus dans la hiérarchie
• Pourcentage du total général : donne la part des éléments par rapport au total du visuel
• Moyenne d'enfants : la moyenne du niveau en-dessous, le sous-total affiche la moyenne des éléments plus bas dans la hiérarchie
• Par rapport au précédent / suivant / premier / dernier : les comparaisons ligne à ligne, on peut calculer le délai entre chaque ticket IT par exemple.
• Rechercher une valeur avec contexte / avec des totaux : RECHERVEV avec prise en compte des totaux ou non...

La difficulté est de savoir comment bien les utiliser et trouver les bons arguments.

Les Champs

Les champs sont juste les colonnes/mesures qui composent votre calcul. Exemple : Marge = [CA] * [Taux de Marge], ici on multiplie les champs CA et Taux de Marge.

Les Axes

Petite subtilité importante à connaître : la plupart des fonctions de calcul visuel ont un paramètre optionnel appelé Axe. Ce paramètre dit à Power BI dans quel sens il doit parcourir les données pour faire son calcul.

Dans un simple tableau, on n'a qu'un seul sens possible : de haut en bas. Pas de débat. Mais dans une matrice avec des lignes ET des colonnes, la question se pose : on cumule verticalement ? horizontalement ? les deux ? Et dans quel ordre ?

• ROWS : vous lisez la première colonne de haut en bas, puis vous oubliez tout et vous recommencez avec la deuxième colonne.
• COLUMNS : vous lisez la première ligne de gauche à droite, puis vous oubliez tout et vous recommencez avec la deuxième ligne.
• ROWS COLUMNS : vous lisez toute la première colonne de haut en bas, puis vous continuez tout en bas en passant à la deuxième colonne, sans jamais oublier ce que vous avez additionné.
• COLUMNS ROWS : pareil, mais vous lisez en zigzag horizontal d'abord — toute la première ligne, puis toute la deuxième ligne, en continuant à additionner.

Dans 90% des cas, on utilise ROWS ou COLUMNS. Bien entendu, le mode "par défaut" est ROWS. Essayez de préciser l'axe pour éviter l'ambigüité dans vos calculs visuels.

Les Resets

Le paramètre Reset : quand remettre le compteur à zéro ?

Le paramètre Reset permet de dire à Power BI quand on veut que le calcul se remette à zéro pendant qu'il parcourt la matrice. C'est utile notamment pour les cumuls : on peut vouloir un cumul qui redémarre à chaque nouvelle année, à chaque nouveau trimestre, à chaque nouveau pays...

Par défaut, Reset vaut NONE, ce qui veut dire : on ne remet jamais à zéro. Le calcul cumule sur tout ce qu'il rencontre.

Imaginons une matrice avec une hiérarchie en lignes : Année > Trimestre > Mois > Jour. On calcule un RUNNINGSUM sur le CA.

On peut choisir parmi plusieurs comportements :

• Redémarrer le cumul à chaque nouvelle année → on obtient le YTD (Year-to-Date)
• Redémarrer à chaque nouveau trimestre → on obtient le QTD (Quarter-to-Date)
• Redémarrer à chaque nouveau mois → on obtient le MTD (Month-to-Date)
• Ne pas redémarrer du tout → cumul global sur toute la période

Deux modes : absolu ou relatif

C'est là que ça se corse un peu. Reset propose deux façons différentes de spécifier à quel niveau on veut redémarrer.

Mode absolu : on compte les niveaux depuis le haut de la hiérarchie. On utilise des entiers positifs ou on référence directement la colonne.

RUNNINGSUM([CA], HIGHESTPARENT)    -- redémarre au niveau le plus haut (Année)
RUNNINGSUM([CA], 1)                -- pareil, niveau 1 = Année
RUNNINGSUM([CA], [Année])          -- pareil, en référençant la colonne
RUNNINGSUM([CA], 2)                -- niveau 2 = Trimestre
RUNNINGSUM([CA], 3)                -- niveau 3 = Mois

Mode relatif : on compte les niveaux depuis le niveau courant, avec des entiers négatifs.

RUNNINGSUM([CA], LOWESTPARENT)     -- redémarre au parent immédiat
RUNNINGSUM([CA], -1)               -- pareil, parent direct
RUNNINGSUM([CA], -2)               -- grand-parent

 

Les Fonctions

Famille 1 : les cumuls et moyennes mobiles

RUNNINGSUM : somme cumulée. Notre grand classique, déjà vu. Pratique pour les calculs de type YTD, QTD, MTD...

Cumul CA = RUNNINGSUM([CA])

MOVINGAVERAGE : moyenne mobile sur une fenêtre glissante. Le deuxième argument, c'est la taille de la fenêtre.

Moyenne 3 mois = MOVINGAVERAGE([CA], 3)

Pratique pour lisser une courbe et voir la tendance plutôt que le bruit.

Famille 2 : la navigation ligne à ligne

Ces fonctions permettent de pointer des lignes précises dans le visuel.

PREVIOUS : valeur de la ligne précédente.

Variation M-1 = [CA] - PREVIOUS([CA])

NEXT : valeur de la ligne suivante. Plus rare en pratique mais ça existe.

CA du mois suivant = NEXT([CA])

FIRST : valeur de la première ligne du visuel.

Ecart vs janvier = [CA] - FIRST([CA])

LAST : valeur de la dernière ligne.

Ecart vs decembre = [CA] - LAST([CA])

Famille 3 : les plages de lignes

RANGE : permet de récupérer une tranche de lignes, pour ensuite appliquer un calcul dessus.

Moyenne des 3 derniers = AVERAGEX(RANGE(-3), [CA])

C'est un peu plus avancé, à réserver aux cas spécifiques.

Famille 4 : les fonctions hiérarchiques (matrices avec niveaux)

Celles-là n'ont de sens que dans une matrice avec plusieurs niveaux de détail.

COLLAPSE : remonte d'un niveau dans la hiérarchie (vers le parent direct).

% du parent = DIVIDE([CA], COLLAPSE([CA], ROWS))

Exemple : dans une matrice Région > Pays > Ville, sur la ligne "Paris", COLLAPSE renvoie le total de "France".

COLLAPSEALL : remonte jusqu'au niveau total général.

% du total general = DIVIDE([CA], COLLAPSEALL([CA], ROWS))

EXPAND : descend d'un niveau vers les enfants.

Moyenne enfants = EXPAND(AVERAGE([CA]), ROWS)

Moins utilisée que COLLAPSE dans les cas concrets.

EXPANDALL : descend jusqu'au niveau le plus détaillé (les feuilles).

Famille 5 : les recherches de valeurs

LOOKUP : le RECHERCHEV d'Excel appliqué au visuel, cherche dans les lignes de détail.

CA 2020 = LOOKUP([CA],[Année], 2020)

LOOKUPWITHTOTALS : même principe mais inclut aussi les lignes de total/sous-total dans la recherche.

Part sur total = DIVIDE([CA], LOOKUPWITHTOTALS([CA], [Pays], BLANK()))

Famille 6 : le classement

RANK : classement des valeurs dans le visuel. Arguments : type de classement (DENSE, SKIP) et critère de tri (ORDERBY).

DENSE : pas de saut de rang contrairement à SKIP.

DESC = descendant, ASC = ascendant.

Rang = RANK(DENSE, ORDERBY([CA], DESC))

Famille 7 : l'utilitaire de structure

ISATLEVEL : renvoie VRAI si la colonne spécifiée est présente au niveau courant du visuel. Utile pour conditionner un affichage selon le niveau de détail.

Est au niveau Mois = ISATLEVEL([Mois])

Comment utiliser les calculs visuels dans Power BI ?

1.Sélectionner votre visuel puis cliquer sur les ...

2.Choisir un modèle de calcul ou directement sélectionner Personnalisé

3. remplir la barre de formules en précisant l'axe (le périmètre du calcul, on somme la ligne ou la colonne par exemple) et le reset (le moment où on cesse de somme pour repartir à 0 comme un changement d'année dans un YTD) si nécessaire.

4. On valide et on change le format dans le pinceau de mise en forme puis Général et enfin Format de données.

Et c'est tout ! Le calcul visuel CA YTD est ainsi crée et au bon format.

Limites des calculs visuels dans Power BI ?

• La première limite est la non réutilisabilité des calculs dans d'autres visuels. Par définition le calcul se fait au niveau d'un visuel seulement et n'est pas recyclable pour d'autres visuels comme le sont les mesures
• Si vous donnez un modèle sémantique pour faciliter le travail de visualisation par la suite sans réinventer la route, les calculs ne seront pas disponibles contrairement aux mesures
• Tous les visuels ne bénéficient pas des calculs visuels
• Impossibilité de publier sur le web des rapports avec des calculs visuels
• Les formats dynamiques ne sont pas disponibles pour les calculs visuels
• Les calculs visuels ne dispensent pas d'avoir un bon niveau en DAX malgré tout

Conclusion

Le DAX est la bête noire de beaucoup de développeurs dans Power BI. Les calculs visuels sont une solution de contournement à de nombreuses difficultés liées au DAX, comme la répétition de CALCULATE, ALL, ALLSELECTED, AVERAGEX ou encore SUMMARIZE.

Comme on l'a vu dans cet article, l'intérêt des calculs visuels est principalement d'éviter les écueils classiques du DAX.

Cependant, la maîtrise des calculs visuels n'est pas si évidente et certaines limites concernant l'industrialisation des calculs ou la disponibilité de ceux-ci nous poussent à affirmer que les mesures restent le must have de tout rapport.

Ainsi, la compréhension du DAX et de ses subtilités reste nécessaire mais Microsoft nous offre une fonctionnalité pour accélérer certaines analyses et rendre un plus accessible Power BI.