février 2, 2018Lire l'article
Lorsque l'on met en place un rapport pour suivre son activité sur Power BI, on est souvent amené à devoir créer des mesures, beaucoup de mesures et souvent trop de mesures ! Il existe une solution à ce problème : les groupes de calcul.
Effectivement, je veux mon chiffre d'affaires (CA) et mon nombre de produits vendus en Month-to-date (MTD), Quarter-to-date (QTD), Year-to-date (YTD)... Je peux aussi souhaiter faire de la time intelligence et chercher à comparer mon CA avec celui du mois dernier, trimestre dernier ou même de l'année dernière. Je peux aussi vouloir mes résultats en livres sterling, dollars ou tout simplement en euros. Rien que dans cet exemple, il me faudrait 2 (mesures de base : CA et unités vendues) x 6 (périodes) x3 (devises) mesures soit 36 mesures, soit des heures et des heures : un cauchemar ! Cela peut paraître trop beau pour être vrai mais on peut faire tout ça avec seulement 2 groupes de calculs et 2 mesures basiques !
Quand vous aurez lu cet article, vous serez en mesure de répondre à ces questions : Qu'est-ce qu'un groupe de calcul ? A quoi servent les groupes de calculs ? Quand faut-il les utiliser ? Comment bien les utiliser ? Qu'est-ce qu'on peut retenir comme avantages et quels sont leurs points faibles ?
Un groupe de calcul est une fonctionnalité assez récente de Power BI permettant de minimiser le nombre de mesures à créer.
Par exemple, je peux créer un groupe de calcul "Devises" et puis l'appliquer à mon CA, mon objectif, mes dépenses. Sans le groupe de calcul, il faudrait que je fasse au minimum deux fois plus de mesures si je n'avais que 2 devises (euro et dollar par exemple). Si on les veut en MTD, QTD, YTD... ça devient beaucoup plus compliqué. Avec 2 groupes de calcul, on verra qu'on peut couvrir une bonne partie des analyses sans avoir à créer 50 mesures !
Une image vaut mille mots. Ici, on a un échantillon d'une vingtaine de mesures d'un rapport RH et ce n'est que la partie émergée de l'iceberg. On aurait pu éviter d'en créer une bonne partie avec les groupes de calcul. Pourquoi utiliser les groupes de calcul ? Pour réduire le nombre de mesures et gagner du temps !
Quelques définitions utiles avant de parler de la méthode.
Passons à la méthode !
Nota bene : les mesures implicites deviennent impossibles après la création d'un groupe de calcul (on ne pourra plus glisser les champ sans créer de mesure directement).
On veut obtenir la moyenne, la somme, le minimum et le maximum de nos mesures, lesdites mesures étant notre CA et notre objectif de CA dans cet exemple.
Pour cela, on applique la méthode citée plus haut.
1. On va sur la vue Modèle (là où on fait les relations entre les tables) et on clique sur l'onglet Modèle dans le volet Données.
2. On clique sur les 3 petits points au niveau de Groupes de calcul (0) et on créée un nouveau groupe de calcul.
3. On renomme le groupe de calcul et la colonne du groupe de calcul. Comme ça on sait sur quoi on travaille.
4. Maintenant, on attaque les éléments de calcul.
On renomme et on change la formule comme pour une mesure classique...
Ici, faire directement la somme ne fonctionne pas car SELECTEDMEASURE() renvoie la mesure. Il faut appliquer un SUMX() pour l'itération sur chaque valeur de notre table de fait.
Somme = SUMX ( Fact_Infomax, SELECTEDMEASURE () )
On va faire de même pour la moyenne, le min et le max. Je vous mets directement les formules ci-dessous.
Moyenne = AVERAGEX ( Fact_Infomax, SELECTEDMEASURE () )
Minimum = MINX ( Fact_Infomax, SELECTEDMEASURE () )
Maximum = MAXX ( Fact_Infomax, SELECTEDMEASURE () )
Remarque : pour changer l'ordre d'affichage des éléments de calcul, c'est par ici.
5. On fait un segment et on glisse la colonne du groupe de calcul dans celui-ci.
Ici, on prend la colonne Agrégation du groupe de calcul Stats générales.
On peut aussi utiliser le volet filtre.
Exemple avec une matrice.
Voilà pour les stats générales. On passe maintenant à l'utilisation des groupes de calcul pour la Time Intelligence.
Pour ce cas, on va créer les éléments de calcul suivants : Actuel, MTD, QTD, YTD, MTD du mois précédent (MTD-1), variation MTD vs MTD-1 en pourcentage.
Les étapes sont toujours les mêmes : on va sur la vue modèle, on créée un nouveau groupe de calcul que l'on renomme Time Intelligence, puis on renomme la colonne de calcul en Période et on travaille sur nos éléments de calcul.
Expliquons chaque élément de calcul.
Actuel : c'est la ou les mesures utilisées dans nos visuels. Ici, on s'intéressera seulement au CA.
Formule :
Actuel = SELECTEDMEASURE ()
Explication : SELECTEDMEASURE() renvoie le CA dans notre exemple tout simplement.
MTD : c'est le CA cumulé pour chaque mois, le CA mensuel en somme.
Formule :
MTD = CALCULATE ( SELECTEDMEASURE (), DATESMTD ( Dim_Date[Date] ) )
Explication : c'est la formule classique pour obtenir un MTD sauf qu'à la place on à SELECTEDMEASURE() comme premier argument du CALCULATE(). On vous recommande cet article sur CALCULATE(). DATESMTD() permet d'obtenir le CA MTD en le combinant au CALCULATE().
QTD : même chose que MTD sauf que c'est pour les cumuls trimestriels.
Formule :
QTD = CALCULATE ( SELECTEDMEASURE (), DATESQTD ( Dim_Date[Date] ) )
YTD : Cumul du CA de tous les mois depuis le début de l'année. On utilise la fonction très populaire DATESYTD()
Formule :
YTD = CALCULATE ( SELECTEDMEASURE (), DATESYTD ( Dim_Date[Date] ) )
MTD-1 : C'est le CA du mois dernier. On utilise DATEADD().
Formule :
MTD-1 =
CALCULATE (
SELECTEDMEASURE (),
'Time Intelligence'[Période] = "MTD",
DATEADD ( Dim_Date[Date], -1, MONTH )
)
Variation MTD-1 (%) : Evolution du CA en pourcentage par rapport au mois dernier.
Variation MTD-1 (%) =
DIVIDE (
CALCULATE ( SELECTEDMEASURE (), 'Time Intelligence'[Période] = "MTD" )
- CALCULATE ( SELECTEDMEASURE (), 'Time Intelligence'[Période] = "MTD-1" ),
CALCULATE ( SELECTEDMEASURE (), 'Time Intelligence'[Période] = "MTD-1" ),
"NA"
)
Remarque : On réutilise les éléments de calcul pour ces 2 derniers calculs. Effectivement, CALCULATE (SELECTEDMEASURE(), colonne de calcul = "l'élément de calcul souhaité") renvoie l'élément de calcul !
Remarque 2 : On peut réutiliser les éléments de calcul pour faire des mesures. Il suffira de faire comme dans la formule ci-dessus mais avec notre mesure à la place.
Exemple pour le CA du mois précédent : CA MTD-1 = CALCULATE([CA],'Time Intelligence'[Période] = "MTD-1")
Regardons maintenant ce que l'on obtient après avoir créé nos groupes de calcul !
On voit bien que ça fonctionne à un détail près... Que s'est-il passé avec notre évolution du CA par rapport au mois précédent ? Exactement, c'est un problème de format !
Remarque 3 : Il ne faut pas oublier de bien gérer les formats de nos éléments de calcul. Heureusement pour nous, il y a une option appelée "Format dynamique" qui va simplifier les choses !
On retourne dans notre vue de modèle et on clique sur l'élément de calcul à reformater. On active l'option "Chaîne de format dynamique" et on tape "0.00%" dans la barre de formule pour appliquer le format de pourcentage à notre élément de calcul.
On retourne dans la vue de rapport pour constater les changements.
Le problème est résolu ! Passons au stade supérieur avec le cas des devises !
L'objectif est de convertir nos données en dollars et en livres sterling avec un groupe de calcul.
Pour cet exemple, on a utilisé les données de ce site ( connecteur web pour les récupérer ;)) : https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/index.en.html.
On applique les mêmes étapes que pour les stats basiques et la Time Intelligence : on créée le groupe de calcul, on renomme le groupe et la colonne de calcul.
Ensuite, on fait nos éléments de calcul.
Expliquons chaque élément de calcul.
€ : On est déjà en euros, donc on peut juste utiliser SELECTEDMEASURE().
€ = SELECTEDMEASURE()
$ : On convertit notre mesure en $.
$ =
SELECTEDMEASURE ()
* CALCULATE ( MAX ( Dim_Devise[Valeur _Devise] ), Dim_Devise[Devise] = "USD" )
£ : On convertit notre mesure en £.
£ =
SELECTEDMEASURE ()
* CALCULATE ( MAX ( Dim_Devise[Valeur _Devise] ), Dim_Devise[Devise] = "GBP" )
On applique un CALCULATE(), pour préciser la devise en se basant sur notre dimension contenant les devises et leur taux de change par rapport à l'euro.
Ensuite, on réordonne les éléments de calcul pour l'affichage.
Il ne faut pas oublier de modifier les formats selon les devises. On écrit "0,0 $" pour le format dollar (la virgule sert à mettre un séparateur de millier).
On n'a plus qu'à vérifier les résultats.
On constate que ça marche sauf pour la variation mensuelle !
On change la "précédence" (l'ordre) des groupes de calcul pour régler les problèmes de priorité de format. Le pourcentage de variation mensuelle ne devrait plus être converti en devise étrangère.
Et voilà, notre problème de format est maintenant résolu !
Les groupes de calculs sont une fonctionnalité pratique pour réduire le nombre de mesures et gagner du temps en théorie. Cependant, ils peuvent se montrer compliqués à créer et combiner. On se retrouve avec un casse-tête pour combiner les groupes de calcul. Effectivement, les subtilités de format, d'ordre de priorité et de calcul nous poussent à nous interroger en ce qui concerne leur mise en place pour des mesures pas si nombreuses et plus complexes. A vous de voir s'ils sont nécessaires dans vos projets !
En attendant, si vous voulez aller plus loin, notre équipe d'experts passionnés est prête à vous accompagner dans vos projets et dans votre prise en main de Power BI, que vous soyez débutant ou à un niveau plus avancé !