DAX: Des calculs de moyennes trompeurs!

Publié le 22 juillet 2019
par Sophie Marchand M.Sc., CPA, CGA, MVP
Nombre distinct

DAX: Des calculs de moyennes trompeurs!

L’article suivant fait suite à une question posée sur le forum et  se veut un complément à l’article Power BI – Power Pivot : La fonction SUMX en renfort (erreur courante chez les débutants). L’usager demande comment calculer une moyenne en fonction de certains éléments distincts.

 

Données de départ

Voici les données qui ont été présentées par l’usager. On constate que certains agents ont visionné des vidéos à certaines dates et heures. Un agent peut avoir visionné plusieurs fois la même vidéo. Ce que l’on souhaite calculer, c’est la moyenne de vidéos distinctes écoutées par agent.

 

Données départ moyenne vs dimension

 

Fonction DISTINCTCOUNT dans Power Pivot

D’abord, sachez qu’il existe une façon de présenter le nombre de vidéos distinctes par agent dans un tableau croisé dynamique, lorsque celui-ci est construit à partir de données d’un modèle Power Pivot. Cette option n’est pas disponible pour les tableaux croisés dynamiques créés directement à partir des données d’une feuille Excel. Ce faisant, la première étape consiste donc à ajouter les données de départ à Power Pivot.

 

Power Pivot ajout données

 

La deuxième étape exige de placer la colonne ID dans les lignes du tableau croisé dynamique et les ID vidéos dans les valeurs.

 

Lignes et valeurs tableau croisé dynamique

 

Finalement, il faut demander de présenter le Total distinct, dans les paramètres des champs de valeurs.

 

Nombre distinct

 

Ceci nous permettrait d’obtenir le tableau croisé ci-dessous. On voit bien que l’agent 1 n’a pas écouté 7 vidéos distinctes mais bien 3 vidéos distinctes, puisqu’il a écouté 4 fois la vidéo 5698 et deux fois la vidéo 5875. Notez que le total général de ce tableau croisé dynamique ne présente pas le nombre de vidéos distinctes visionnées par agent mais plutôt le nombre de vidéos distinctes au total dans la base de données. En effet, si vous additionnez le nombre de vidéos distinctes par agent, vous obtiendrez un total de 16 vidéos (et non pas 15).

 

Vidéos distinctes par agent

 

Il pourrait donc être tentant de calculer la moyenne de vidéos distinctes par agent en divisant 15 par 7 (le nombre distinct d’agents), c’est-à-dire via la formule DAX =DIVIDE(DISTINCTCOUNT(ID video),DISTINCTCOUNT(Agent),0), mais dans ce cas, on obtiendrait 2.14 vidéos par agent (15/7). Alors que si vous calculez bien la moyenne de 3-1-3-2-1-4-2, vous obtiendrez plutôt 2.29 vidéos par agent (16/7).
 
 

Vous devez analyser de grandes quantités de données et les présenter dans des rapports et tableaux de bord, avec des indicateurs de performance pertinents ? Développez vos compétences avec nos formations en Power BI.

 

Pas de résolution sans DAX

Pour adresser correctement ce genre de calcul, on doit donc automatiquement se retourner vers le langage DAX. Dans un premier temps, il faut créer un modèle de données. Au minimum, dans ce cas-ci, il nous faudrait deux tables de dimensions, soit une table de dimension pour les agents et une table de dimension pour les vidéos.

 

Modèle de données

 

Notez que dans la table DimAgents, la colonne calculée présentant le nombre de vidéos distinctes n’est pas du tout requise. Je l’ai insérée pour illustrer le concept suivant.

 

D’abord, j’ai défini le nombre de vidéos distinctes avec la mesure suivante:

 

Nombre distinct vidéos

 

J’aimerais vous faire remarquer qu’en plaçant ensuite cette mesure dans ma table d’agents, j’obtiens ce qui suit, soit le nombre de vidéos distinctes visionnées par agent:

 

Nombre vidéos distinctes table agents

 

Si j’avais plutôt utilisé la formule suivante, elle se serait retrouvée sans contexte de ligne et elle aurait plutôt présenté le total de vidéos distinctes dans la base de données, soit 15.

 

Sans contexte de ligne

 

La mesure que nous recherchons et qui ramène 2.59 est donc la mesure suivante:

 

Moyenne vidéo par agent

 

J’ai déjà expliqué le concept des fonctions itératives en DAX, dont font partie les fonctions d’agrégation qui se terminent par un X. Ici, il faut imaginer que dans la table DimAgents (passée en premier paramètre), on place la mesure [NombreVideosDistinctes], comme on l’a fait avec notre première colonne calculée dans l’exemple ci-dessus et qu’ensuite seulement, on demande de faire la moyenne des valeurs de cette colonne.

 

Si on avait plutôt utilisé la formule suivante, on aurait fait la moyenne des valeurs d’une colonne équivalente à la deuxième colonne calculée dans l’exemple ci-dessus:

 

 

On se serait alors retrouvé avec la valeur 15.

 

Moyenne de 2.29

 


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

Pour une introduction au langage DAX, qui permet de créer des tableaux de bord flexibles et faciles à mettre à jour en plus de créer des visualisations de données évoluées et pertinentes, suivez la 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 2 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

 

1 réflexion sur “DAX: Des calculs de moyennes trompeurs!”

Laisser un commentaire

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

Scroll to Top