Agrégation et itération

Généralités

Les fonctions d’agrégation - SUM, AVERAGE, MIN, MAX, COUNT et leurs variantes - ont pour argument une (et une seule) colonne, soit par exemple MAX(transactions[quantité]).

Lorsqu’elles sont utilisées pour créer une nouvelle colonne, elles font l’agrégation de toutes les lignes de la colonne passée en argument.

Lorsqu’elles sont utilisées pour créer une mesure, elles sont soumises au contexte de filtre dans lequel elles sont utilisées.

Dans les deux exemples ci-dessous, la même formule - SUM(transactions[quantité]) - a servi à créer d’abord une colonne (qté (col)), puis une mesure (qté), avec des résultats très différents :

Ici, la colonne qté (col) est affichée dans la table Client

Ici, la colonne qté (col) et la mesure qté sont affichées dans un tableau

Une utilisation majeure de la fonction MAX

La fonction MAX apparaît dans un motif très fréquent, lié aux dates. Par motif, j’entends une formule-type d’usage courant.

Lorsque nous avons marqué la table...

Généralités

Les fonctions d’itération - SUMX, AVERAGEX, MINX, MAXX, COUNTX et leurs variantes, pour les fonctions destinées à calculer une agrégation - ont le plus souvent pour argument une table (ou une expression de table, le résultat d’une fonction FILTER par exemple), et une expression (un calcul) :

montant facture = 
SUMX( 
    transactions,                             -- la table 
    [qté] * RELATED(produits[prix unitaire])  -- l'expression 
) 

D’un point de vue plus générique, leur structure est donc :

Fonction( 
      table, 
      expression 
) 

Elles ont pour particularité de pouvoir calculer l’expression sur plusieurs tables. La fonction RELATED permet de se promener dans le modèle et de remonter les relations.

Outre le fait qu’elles calculent une expression, les fonctions SUMX et variantes présentent un intérêt majeur : la table qui leur est passée en argument. Vous verrez en effet qu’il peut s’agir d’une table « physique », une table présente dans le modèle ou d’une table « virtuelle », ce que j’appelle une vue pour faire plus court. Il existe de très nombreux moyens de créer des vues en DAX, par exemple avec la fonction FILTER, la fonction ALL, etc.

Par exemple, parce qu’elle s’appuie sur une table filtrée, la formule suivante calcule le montant uniquement pour les produits rouges :...

Les fonctions d’agrégation

Les exercices sont présentés dans Power BI, bien que la plupart des manipulations puissent aussi être exécutées dans Excel.

Utilisez les fichiers chap5_or.pbix et chap5_librairie.pbix présents dans le répertoire des exemples correspondant au chapitre Agrégation et itération.

Si pour une raison quelconque vous ne pouvez pas utiliser ces fichiers, notamment parce qu’ils ne seraient pas compatibles avec votre version de Power BI Desktop - les fichiers créés dans une version de l’outil ne peuvent en effet pas être ouverts dans une version plus ancienne, précisons que dans chap5_or.pbix une colonne basée sur la formule suivante a été ajoutée :

anneesem = FORMAT(CoursOr[Date],"YYYY") 
&FORMAT(WEEKNUM(CoursOr[Date]), "0#") 

Cette formule retourne une semaine au format AAAASS, par exemple 201901 pour la première semaine de 2019.

Une deuxième colonne a été ajoutée, qui retourne le numéro du jour (de 1 pour dimanche à 7 pour samedi) :

jour# = WEEKDAY(CoursOr[Date]) 

Calculer le cours de l’or à fin de semaine

Nous utilisons ici le fichier chap5_or.pbix.

L’objectif de cet exercice est simple, la réalisation un peu moins : il s’agit de créer un tableau pour afficher pour chaque semaine le cours de clôture du dernier jour de la semaine. 

Ainsi, dans le tableau ci-dessus, nous souhaitons récupérer 1689,20 € pour la semaine 202016 et 1723,50 € pour la semaine 202017.

Vous pourriez arriver à un résultat intermédiaire comme ceci :

Mais vous...

Les fonctions d’agrégation

Calculer le cours de l’or à fin de semaine

Comme vous pouvez le voir sur la première capture d’écran, le dernier jour de cotation de la semaine est le vendredi, soit le jour n°6.

Une première idée est donc de récupérer le cours de clôture lorsque le numéro du jour est égal à 6.

Dans un tableau construit à l’aide de la colonne anneesem, le contexte de filtre contient le numéro (unique) de la semaine (par exemple 201947). Pour cette semaine-là, il y a en général 6 jours de cotation : l’idée est donc de compléter le contexte de filtre avec une condition portant sur le numéro du jour. La fonction CALCULATE va être particulièrement utile pour ça :

 Créez la formule suivante :

cours fin de semaine #1 = 
CALCULATE( 
    MAX(CoursOr[Clôture]) , 
    CoursOr[jour#] = 6 
) 

 Créez le tableau avec pour colonnes anneesem et cours fin de semaine #1.

Ce qui est un peu déroutant ici, c’est que le premier argument de CALCULATE doit être une expression (ça ne peut pas simplement être une colonne) : c’est la raison pour laquelle il faut ajouter une fonction d’agrégation, MAX par exemple (mais SUM ou MIN pourrait aussi être utilisées puisque de toute façon il n’y a qu’une valeur).

Mais pourquoi manque-t-il certaines semaines ? Si vous regardez de près les données, vous verrez que parfois, la semaine ne se termine par le vendredi, mais avant ce jour. C’est le cas de la semaine 201948, qui se termine un mercredi. Et cette situation se répète souvent.

La première idée n’est donc pas suffisante. Il faut pouvoir...