Maîtrisez CALCULATE : la fonction DAX la plus importante sur Power BI !

CALCULATE(calculer en français), si cette fonction ne vous dit rien, c'est que vous ne développez pas assez sur Power BI !

Pour ceux qui savent, cette fonction vous paraît simple mais elle est en fait bien plus compliquée qu'on le pense quand on creuse le sujet.

Après avoir lu cet article, vous serez en mesure de répondre à ces questions :  Qu'est-ce que CALCULATE ? A quoi sert CALCULATE  et pourquoi c'est si important ? Comment fonctionne CALCULATE ? Comment bien utiliser CALCULATE ?

Définition de CALCULATE

CALCULATE est une fonction en DAX (Data Analysis Expressions), le langage de programmation pour les mesures, calculs sur Power BI, qui "évalue une expression dans un contexte de filtre modifié" (Microsoft).

Cette définition nous fait savoir que ce n'est pas Microsoft qui va nous aider à comprendre cette fonction fondamentale. Essayons d'éclaircir tout ça !

CALCULATE, c'est une fonction qui va vous permettre de faire des calculs plus spécifiques, de leur appliquer un filtre.

Exemple : au lieu de calculer votre chiffre d'affaires (CA) global, vous voulez calculer votre votre CA provenant seulement des voitures rouges. Comment faire ? Utilisez CALCULATE.

CALCULATE donne ainsi un contexte (de filtre) à vos expressions, calculs aussi complexes soient-ils.

Nous allons maintenant voir son intérêt et son fonctionnement de manière plus explicite !

Pourquoi et quand utiliser CALCULATE ?

Comme vous pouvez le voir, chez MYPE, on considère que la fonction CALCULATE est la fonction la plus importante sur Power BI, au sommet de notre top 10 ! Ce n'est pas pour rien, je vous le garantis.

Comme on l'a évoqué précédemment, CALCULATE va donner un contexte à vos calculs. Cela veut grossièrement dire que pour tout calcul complexe, vous allez en avoir besoin, c'est extrêmement puissant.

Cas d'utilisation

• Vous voulez calculer le CA (ou tout autre indicateur) généré par un produit spécifique, une catégorie en particulier et/ou pays ? Prenez votre clavier et tapez CALCULATE !
• Vous voulez faire une analyse faisant appel à de la time intelligence, calculer votre CA sur les 3 derniers mois, celui de l'année précédente, faire le cumul Year-to-date de votre CA, une moyenne mobile etc. ? Prenez votre clavier et tapez CALCULATE !
• Vous voulez faire un pourcentage classique, une part de CA d'un produit par rapport à un CA global ? CA spécifique/CA global ? Vous voulez donc probablement calculer le CA global sans qu'il soit affecté par les filtres visuels et calculer le CA pour un produit spécifique ? Prenez votre claviez et tapez CALCULATE !
•  Vous voulez calculer la somme des CA des produits avec un CA supérieur à 100 000 € ? Il y a une histoire de Booléen (TRUE/FALSE, retourne TRUE si CA >100 000 €, sinon FALSE) ? Prenez votre clavier et tapez CALCULATE !

Somme toute, il faudra CALCULATE pour tout ce qui est booléen, time intelligence et contexte de filtre !

Vous comprenez ainsi qu'on ne peut pas échapper à l'utilisation de cette fonction. Nous allons maintenant voir comment elle fonctionne.

Comment fonctionne CALCULATE ?

Syntaxe : 

CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

Décryptons les arguments de cette formule fournie par Microsoft avant de donner des exemples concrets d'application de CALCULATE.

expression : ce que vous voulez calculer. Exemples : le CA, le nombre de salariés,...

filter1,filter2,...filtern : votre contexte de filtre souhaité. Exemples : produit = voiture, couleur = rouge, pays = France, CA >100 000 €, l'année dernière, sur l'ensemble des 3 derniers mois...

Les virgules : considérez que les virgules sont des AND (ET), des opérateurs logiques. Exemples : produit = voiture, couleur = rouge revient à dire produit = voiture ET couleur = rouge. Pour les OU, il faudra insérer "||" dans nos amis les filtern, produit = voiture || couleur = rouge...

Exemples et explication

Cas avec 1 seul filtre

Ici on veut calculer le CA des produits de la marque Nvidia pour notre entreprise Infomax qui fournit du matériel informatique. On a donc 1 filtre à appliquer.

On créée ainsi une mesure CA_Nvidia.

CA_Nvidia = CALCULATE([CA], 'Dim Marque'[Marques]) = "Nvidia")

Explication : CALCULATE peut se diviser en 2 parties. L'expression (le calcul) et le contexte qu'on veut mettre en place.

1. On calcule QUOI ? Le CA (qui est lui-même la mesure somme de Réalisé de notre table de faits...).
2. Dans quel CONTEXTE ? Provenant seulement de nos produits de la marque Nvidia. Il faut donc vérifier la condition "Marque = Nvidia" et qu'on obtienne TRUE. Encore cette histoire de booléen... Et.. c'est tout !

CALCULATE, c'est simplement appliquer un contexte de filtre sur ce qu'on appelle une expression, un calcul.

Vous pouvez voir que sur l'image du dessus la mesure CA_Nvidia renvoie bien la valeur du CA pour Nvidia en vérifiant avec notre petit tableau affichant les CA des marques.

Attention : Il s'agit bien du CA généré par nos produits Nvidia, mais seulement pour 2020 ! Regardez bien, on a un filtre activé sur l'année 2020 (en haut à gauche). Cela signifie que le CALCULATE va modifier le contexte de filtre déjà existant, 2020 en l'occurrence. CALCULATE peut aussi faire ignorer les filtres "extérieurs" (les filtres provenant des visuels) avec la fonction ALL, on vous montre comment après quelques autres cas.

Cas avec plusieurs filtres

Cette fois, on veut le chiffre d'affaires des produits Nvidia EN FRANCE. On a donc 2 filtres à appliquer.

On créée ainsi une mesure CA_Nvidia_France.

CA_Nvidia_France = CALCULATE([CA],'Dim Marque'[Marques]="Nvidia",'Dim Pays'[Pays]="France")

Explication : 

Analysons encore les 2 parties qui composent CALCULATE.

1. On calcule quoi ? Le CA.
2. Dans quel contexte ? Seulement pour Nvidia ET en France. On rappelle que la virgule dans le CALCULATE signifie ET. En programmation, le ET (AND) signifie que les 2 conditions doivent être respectées. Ce n'est pas le CA de Nvidia OU (OR) en France, c'est Nvidia ET en France : les ventes de Nvidia en France. Il faut s'habituer à cette façon de voir le ET et le OU sur Power BI.

Remarque : "||" permet d'exprimer ce "OU" sur Power BI. C'est le prochain cas que l'on va aborder.

Remarque 2 :  Encore une chose sur ces histoires de contexte de filtre, sélectionnez un autre pays sur le visuel segment à gauche ne va pas impacter notre carte avec la mesure CA_Nvidia_France. Cela révèle que CALCULATE modifie bien le contexte de filtre existant et a une priorité sur les filtres extérieurs (segments et visuels filtrant).

Une capture d'écran vaut mille mots :

On voit bien que l'on a un filtre extérieur sur le pays. Effectivement, on a filtré sur le Japon et pourtant notre mesure reste intact. Le contexte de filtre a bien été ajusté par la fonction CALCULATE ! On a bien le CA de Nvidia en France et non pas au Japon comme en témoigne la différence entre notre visuel carte et le tableau de débogage.

Cas avec "OU"

On veut le chiffre d'affaires des produits Nvidia en France et au Japon, la somme des CA en France et au Japon pour les produits de la marque Nvidia.

Je vous montre ici ce qu'il ne faut pas faire. Je rappelle que les virgules dans le CALCULATE sont équivalents à des ET (pas OU), c'est TRUE si et seulement si les 2 conditions sont respectées. Or, on ne peut être à la fois au Japon et en France : une boutique de Infomax, fournisseur de matériel informatique, est géographiquement ancré, logique ! D'où le vide, la condition ne peut être respectée. Il nous faut donc le fameux "OU". Réessayons !

On veut toujours le CA en provenance des 2 pays pour la marque Nvidia.

CA_Nvidia_France_Japon = CALCULATE([CA],'Dim Marque'[Marques] = "Nvidia",'Dim Pays'[Pays] = "France" || 'Dim Pays'[Pays] = "Japon")

Explication : 

1. On calcule quoi ? Toujours le CA.
2. Dans quel contexte de filtre ? En France et au Japon pour la marque Nvidia. Pour exprimer cela, il faut utiliser ||, logique du "OU", pas de la virgule et du "ET", en ce qui concerne les pays. On garde toutefois la virgule entre lacondition sur les pays et la condition sur la marque.

On obtient bien le bon résultat comme on peut le voir sur le tableau.

Remarque : le "OU" sème la confusion quand on est pas habitué à la logique booléenne en informatique. On peut le confondre avec "ET".

Voici un petit schéma pour vous aider à retenir cette logique booléenne.

Passons à l'application de CALCULATE sur la time intelligence.

Cas de la time intelligence

Je vais juste ici vous montrer le cas pour calculer un cumul year-to-date (YTD), cumul des mois, de janvier à aujourd'hui. Le cas du mois précédent sera aussi présenté. La time intelligence n'est pas le sujet principal de cet article. Donc, on ne va pas s'attarder longtemps sur ces cas.

Le cas YTD

On veut calculer le CA cumulé depuis janvier de la marque Nvidia en France. On créée la mesure CA_Nvidia_France_YTD et on l'applique au visuel matrice et au visuel carte (le fameux carte 123 pour ceux qui savent).

CA_Nvidia_France_YTD = CALCULATE([CA],'Dim Marque'[Marques]="Nvidia",'Dim Pays'[Pays]="France",DATESYTD('Date'[Date]))
//CALCULATE([CA_Nvidia_France],DATESYTD('Date'[Date]))


Explication : 

1. On calcule quoi ? Le CA.
2. Dans quel contexte ? Pour Nvidia ET en France ET pour les dates de janvier à aujourd'hui en 2020. Pour avoir cet intervalle de dates de janvier au mois actuel, on utilise une fonction DAX nommée DATESYTD. Cliquez sur le terme pour être redirigé sur notre lexique et avoir plus d'informations sur cette fonction DATESYTD.

On voit bien grâce à notre tableau que l'on a ce fameux cumul CA YTD pour la France et Nvidia.

Le cas année précédente

On veut le CA de l'année précédente (2019) pour Nvidia et France.

On créée donc la mesure CA_Nvidia_France_Y-1, Y-1 comme Year -1.

CA_Nvidia_France_Y-1 = CALCULATE([CA_Nvidia_France],DATEADD('Date'[Date],-1,YEAR))

Explication : 

1. On calcule quoi ? Le CA de la marque Nvidia en France. Et oui, on peut réutiliser nos mesures précédentes pour aller plus vite ! On va juste modifier le contexte de filtre.
2. Dans quel contexte ? L'année dernière. Pour obtenir l'année dernière, utilisons la fonction DAX DATEADD. En ce qui concerne cette fonction, mettez juste votre colonne de dates en premier argument, le nombre de jours, mois, trimestres ou années que vous voulez ajouter ou retirer (1 pour ajouter et -1 retirer un mois par exemple), enfin, précisez ce que vous voulez ajouter/retirer : DAY/MONTH/QUARTER/YEAR.

Voici une infographie bonus pour mieux comprendre.

Et ça marche, regardez juste le tableau du screen au-dessus.

Bonus : si vous voulez le CA du dernier mois en date, utilisez la fonction MAX dans votre CALCULATE avec votre colonne mois à l'intérieur. Idem respectivement pour les années, jours, trimestres...

Passons au dernier cas : l'interaction avec les filtres extérieurs.

Cas de la modification du contexte de filtre :  ignorer les filtres extérieurs

Ici on veut la part de la marque Nvidia dans le CA global. Pour obtenir ce résultat, on créée la mesure Part_CA_Nvidia.

Part_CA_Nvidia = DIVIDE([CA_Nvidia],CALCULATE([CA],ALL('Dim Marque'[Marques])))

Explication : 

1. D'abord on applique la fonction DIVIDE qui divise un numérateur par un dénominateur. Une part c'est l'élément divisé par le total général.
2. Qu'est-ce qu'on cherche à calculer au niveau du CALCULATE ? Le CA.
3. Dans quel contexte ? Pour toutes les marques en ignorant les filtres extérieurs sur la colonne marque. La fonction ALL, équivalente à REMOVEFILTERS, est utilisées pour imposer le contexte de filtre suivant : toutes les marques sans filtre. Cela permet d'ignorer les filtres extérieurs somme toute.

On a fait un article sur ALL, ALLSELECTED et ALLEXCEPT. Cliquez sur les termes pour être redirigé vers le lexique et avoir plus de détails sur ces fonctions. Cliquez ici pour accéder à l'article sur ces fonctions.

Ce screen montre une mesure %CA qui ignore les filtres sur les différentes dimensions pour avoir une part du CA fiable.

 Bonus : Utilisez KEEPFILTERS pour garder les filtres extérieurs voir screen ci-dessus.

Conclusion

Vous savez maintenant ce qu'est CALCULATE, pourquoi et comment l'utiliser. Comme on l'a vu, cette fonction est centrale et peut amener à l'erreur si on ne comprend pas son fonctionnement. On espère que vous allez CALCULATE plus sereinement maintenant !

Pour aller plus loin et bénéficier d'un accompagnement par une équipe d'experts passionnés sur Power BI, rejoignez nos formations Power BI sur-mesure de qualité en présentiel. Consultez notre chaîne YouTube, notre lexique et le blog sur lequel vous vous trouvez !