Sur Power BI, vous pouvez grâce au langage DAX réaliser des calculs de somme, moyenne, max etc… à différents niveaux de détail.
Cependant, certains résultats peuvent parfois paraitre surprenant, et une connaissance approfondie du langage DAX est nécessaire pour éviter les confusions.
1. Calculer une moyenne mensuelle avec AVERAGEX
Un exemple typique est le montant moyen des ventes par jour. Dans mon cas, je souhaitais calculer le nombre d’accident moyen par jour à partir d’une table qui ressemble à ceci :
Mon modèle de données est le suivant :
Je créé donc ma première mesure pour compter le nombre d’accident :
Nombre Accident = DISTINCTCOUNT(T_ACCIDENT[identifiant accident])
Puis je décide de réaliser 2 mesures pour calculer la moyenne mensuelle :
Moyenne Accident 1 = DIVIDE([Nombre Accident];COUNTROWS(T_CALENDRIER);0)
Moyenne Accident 2 = AVERAGEX(
VALUES(T_CALENDRIER[JOUR]);
[Nombre Accident]
)
2. Comprendre des différences sur des mesures DAX
La matrice suivante reprend les mesures précédentes, et on peut s’apercevoir que sur certains mois, les résultats des deux moyennes sont légèrement différents :
Et sur le mois de Janvier par exemple :
On s’aperçoit que le 30 Janvier, il n’y a eu aucun accident. Or, DISTINCTCOUNT (tout comme COUNTROWS) return BLANK() dans le cas où aucune valeur n’est trouvée.
De plus, tout comme dans Excel, les fonctions AVERAGE ne considèrent pas BLANK() comme un 0. La Moyenne Accident 2 est donc calculée sur 30 jours et pas 31 !
On peut donc en déduire que :
Moyenne Accident 1 = 286/31 = 9,22581
Moyenne Accident 2 = 286/30 = 9,53333
3. Gérer correctement les BLANK() grâce au DAX
Une première solution très simple à mettre en place est d’ajouter « +0 » à notre 1ère mesure :
Nombre Accident = DISTINCTCOUNT(T_ACCIDENT[identifiant accident])+0
On obtient alors un résultat cohérent et une valeur visible au 30 Janvier :
Mais cette solution n’est pas satisfaisante. Dans le cas où mes données ne sont pas encore présentes dans ma table, je préfèrerai avoir BLANK() et ne réaliser le calcul que sur les jours valides.
Dans mon exemple, mes données s’arrêtent au 11/12/2017. Avec la mesure précédente, j’obtiens :
Ici, le résultat attendu serait Moyenne = 83/11 = 7,54.
Pour répondre à l’ensemble de la problématique, il faut donc modifier notre mesure DAX et vérifier s’il existe des données postérieures dans notre table :
Nombre Accident =
VAR MaxJourCal = MAX(T_CALENDRIER[JOUR])
VAR MaxJourAccident =
CALCULATE(
MAX(T_ACCIDENT[date]);
ALL(T_ACCIDENT)
)
RETURN
DISTINCTCOUNT(T_ACCIDENT[identifiant accident])
+IF(
MaxJourAccident>=MaxJourCal;
0
)
On obtient alors la matrice suivante :
On remarque finalement que notre mesure Moyenne Accident 1 devient fausse ! On a en effet :
Moyenne Accident 1 = 83/31 = 2,67742
Moyenne Accident 2 = 83/11 = 7,54545
4. Conclusion
Les 2 mesures les plus robustes sont donc :
Nombre Accident =
VAR MaxJourCal = MAX(T_CALENDRIER[JOUR])
VAR MaxJourAccident =
CALCULATE(
MAX(T_ACCIDENT[date]);
ALL(T_ACCIDENT)
)
RETURN
DISTINCTCOUNT(T_ACCIDENT[identifiant accident])
+IF(
MaxJourAccident>=MaxJourCal;
0
)
Moyenne Accident = AVERAGEX(
VALUES(T_CALENDRIER[JOUR]);
[Nombre Accident]
)
Ce problème montre bien qu’à partir d’une question très simple (quel est le nombre d’accident moyen par jour ?), on peut obtenir des résultats très différents. Une bonne maitrise du langage DAX peut s’avérer nécessaire même pour des calculs très simples.
Si vous rencontrez des problèmes avec vos mesures DAX où que vous vous posez des questions sur la validité d’un résultat, n’hésitez pas à contacter nos experts Power BI !
N’hésitez pas à faire appel à nos experts pour vos besoins de développement, de formation, d’expertise ou d’assistance sur Power BI en nous contactant au 09.72.54.52.00 ou par mail à l’adresse contact@experts-powerbi.fr !