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. Voici donc un article qui vous indique comment calculer une moyenne pondérée en DAX.
Données de départ pour notre calcul de moyenne pondérée en DAX
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.
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.
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.
Finalement, il faut demander de présenter le Total distinct, dans les paramètres des champs de valeurs.
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).
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 de moyenne pondérée, 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.
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:
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:
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.
La mesure que nous recherchons et qui ramène 2.59 est donc la mesure suivante:
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.
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 :
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é.
Très bien démystifié! Merci pour l’explication très claire.