Power BI – Power Pivot : La fonction SUMX en renfort 💪 (erreur courante chez les débutants)

Récemment sur le forum, quelqu’un a posé une question en répétant à deux reprises qu’Excel perdait la tête. Loin de moi l’idée d’intimider qui que ce soit, mais il est tout de même assez rare qu’Excel perde la tête! Bien sûr, il y a parfois certains bogues dans Excel, comme dans tout programme informatique. La plupart du temps, ces bogues sont documentés. Donc, à moins de bogue majeur, vous ne devriez pas conclure qu’Excel fait des erreurs de calcul. Vous devriez plutôt vous demander qu’est-ce qui explique, dans votre démarche, le résultat fourni par Excel. Dans le cas qui nous concerne ici, on fait référence à l’usage d’une fonction en langage DAX, dans Power Pivot, qui ne retourne pas de total. Nous allons examiner pourquoi et nous allons suggérer d’utiliser la fonction SUMX pour régler la situation.

Données de départ pour illustrer la fonction SUMX

De façon simpliste, on part avec une liste qui indique les présences d’étudiants en AM et en PM (1 indique que l’étudiant est présent et 0 qu’il est absent).

Résultat recherché par notre fonction SUMX

On souhaite présenter dans un tableau croisé dynamique, la liste des étudiants présents toute la journée et le grand total en bas du tableau. Donc, selon les données présentées ci-dessus, on cherche un total de 6 apprenants.

Démarche problématique utilisée par l’usager

L’usager mentionne avoir utilisé la fonction DAX suivante:

Cet usager précise que [MesureAM] et [MesurePM] sont en effet des mesures. On peut donc penser qu’elles ont été calculées par le biais d’une somme de la colonne AM et une somme de la colonne PM (cette portion n’est pas définie par l’usager).

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.

Démarche suggérée par le CFO masqué avec la fonction SUMX

Nous avons plutôt proposé à l’usager d’utiliser la formule suivante:

Comparaison des résultats

On peut très bien constater, dans le tableau croisé dynamique ci-dessous, que notre solution montre un total de 6 étudiants alors que la solution de l’usager ne montre aucun total. Mais est-ce vraiment Excel qui perd la tête?

Explications des fonctions DAX utilisées

Dans la solution proposée par l’usager, on fait la somme des 1 dans la colonne AM pour déterminer le nombre d’individus présents en AM et la somme des 1 de la colonne PM pour déterminer le nombre d’individus présents en PM. Ensuite, on imbrique ces 2 mesures dans une fonction IF. Le problème est le suivant. Pour chaque ligne qui présente l’information d’un étudiant, la mesure va fonctionner, puisqu’on dit, si la somme des 1 en AM pour cet individu est égal à 1 et si la somme des 1 en PM pour cet individu est égal à 1, renvoie 1, sinon, ne renvoie rien (voir image ci-dessous). Le problème survient après. D’abord, il faut bien comprendre que dans un tableau croisé dynamique, la ligne de total répond aussi au contexte d’évaluation, plus précisément au contexte de filtres. Ce faisant, Excel se pose la même question, est-ce que la somme des 1 en AM, i.e. 9 et égal à la somme des 1 en PM, i.e. 10. La réponse est non et donc Excel ne renvoie aucun résultat… À noter que si nous avions ajouté un troisième paramètre dans la fonction IF, pour indiquer à Excel de retourner une valeur quelconque lorsque la condition n’est pas satisfaite, cette valeur serait apparue dans la ligne de total du tableau croisé dynamique.

Encore une fois, nous sommes dans une situation où l’usager ne maîtrise pas bien les contextes d’évaluation. Il s’agit de la notion la plus importante rattachée au langage DAX. Sans maîtriser les contextes d’évaluation, les usagers du langage DAX, se buteront toujours sur ce type de problématiques. Vous n’avez qu’à visiter notre forum pour vous en convaincre. Voici d’ailleurs une liste d’articles similaires, qui répondent à des questions semblables posées sur le forum:

• Modèle de données : Problématique et résolution pas à pas
• Power BI : Valeurs cumulatives et contextes d’évaluation
• Power BI: Filtres avancés et encore ce fameux contexte d’évaluation!

Dans la solution que nous proposons, nous utilisons une fonction itérative SUMX. Dans cette fonction, Excel évalue ligne par ligne si le résultat doit être 1 ou rien. Imaginez ici qu’Excel créerait une colonne temporaire (invisible) et inscrirait un à un les résultat dans cette colonne.

Et une fois qu’Excel a créé cette colonne temporaire invisible, il en fait la somme. Dans ce cas-ci, on obtient donc 6.

Conclusion

Quand vient le temps d’apprendre le langage DAX, il faut prendre le temps de bien comprendre et étudier les contextes d’évaluation.

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

Nous traitons du langage DAX et des contextes d’évaluation en profondeur dans notre formation Introduction au langage DAX (Power BI et Power Pivot).

Voici quelques commentaires d’apprenants ayant suivi cette formation :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.