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

Publié le 16 mai 2019
par Sophie Marchand M.Sc., CPA, CGA, MVP
Mal de tête

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

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).

DAX - Données départ

 

Résultat recherché

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:

Erreur DAX

 

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é

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

Solution DAX

 

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?

Langage DAX

 

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.

Explication de la problématique DAX

 

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:

 

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.

SUMX colonne temporaire

 

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 :

Edith Lemay
Écrit il y a 3 semaines
Explications claires et bien structurées.

Super intéressant! Les exercices pratiques aide beaucoup à assimiler la matière.

Nicolas Grandclaude
Écrit il y a 2 mois
Je recommande vivement cette formation.

Une formation indispensable pour la mise en place d'un modèle de données automatique et performant.

David Bansard
Écrit il y a 2 mois
De nombreuses portes se sont ouvertes

Etant, moi-même, dans le métier de la formation, j'avais besoin d'un apprentissage beaucoup plus pertinent et professionnel que se débrouiller tout seul chez soi. De nombreuses portes se sont ouvertes sur la manière de comprendre l'outil Power BI et donc de correctement l'utiliser.

Remi Martinato
Écrit il y a 3 mois
Superbe formation même si le langage Dax est très complexe à comprendre.

Cette formation clôture mon parcours « Excel tableaux de bord » qui a été pour moi extrêmement enrichissant. Cela m a permis de découvrir des fonctions d Excel, de prendre plaisir à les utiliser et de développer ma créativité afin de produire des tableaux de bord encore plus percutants. La formatrice maîtrise sur le bout des ongles les différents outils.

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top