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

DAX: Des calculs de moyennes trompeurs!

Publié le : 22 juillet 2019

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

 

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

 


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.

  • réponse Quentin ,

    Très bien démystifié! Merci pour l’explication très claire.

    Laisser un commentaire