Contact: 514-605-7112 / info@lecfomasque.com

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

Publié le : 16 mai 2019

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

 

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. Nous traitons de ce sujet en profondeur dans notre formation Introduction au langage DAX.

 


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

Laisser un commentaire