Les agrégations permettent de réduire, de synthétiser ou de regrouper des données. Lorsque vous démarrez avec des données brutes provenant de tables ou d’autres sources de données, elles sont souvent à deux dimensions, ce qui signifie qu’elles contiennent de nombreux détails, mais qu’elles n’ont pas été organisées ou regroupées. L’absence de résumés ou de structure peut rendre difficile l’identification de modèles dans les données. Un travail important de la modélisation des données consiste à définir des agrégations qui simplifient, synthétisent ou résument des modèles en réponse à une question métier spécifique.
Les agrégations les plus courantes, telles que celles utilisant AVERAGE, COUNT, DISTINCTCOUNT, MAX, MIN ou SUM , peuvent être créées automatiquement dans une mesure à l’aide de la somme automatique. D’autres types d’agrégations, tels que AVERAGEX, COUNTX, COUNTROWS ou SUMX, retournent une table et nécessitent une formule créée à l’aide de DAX (Data Analysis Expressions).
Fonctionnement des agrégations dans Power Pivot
Choix de groupes pour l’agrégation
Lorsque vous agrégez des données, vous les regroupez par attributs, tels que le produit, le prix, la région ou la date, puis vous définissez une formule qui fonctionne sur toutes les données du groupe. Par exemple, lorsque vous créez un total pour une année, vous créez une agrégation. Si vous créez par la suite le ratio année en cours/année précédente et que vous présentez ces données sous forme de pourcentages, il s’agit d’un autre type d’agrégation.
Le choix du mode de regroupement des données est régi par la question métier. Par exemple, les agrégations peuvent répondre aux questions suivantes :
Nombres Combien de transactions a-t-on effectuées en un mois ?
Moyennes Quelles ont été les ventes moyennes du mois par vendeur ?
Valeurs minimales et maximales Quels ont été les cinq premiers secteurs de vente en termes d’unités vendues ?
Pour créer un calcul qui répondra à ces questions, vous devez avoir détaillé les données qui contiennent les nombres à comptabiliser ou à additionner, et ces données numériques doivent être associées d’une façon quelconque aux groupes que vous utiliserez pour organiser les résultats.
Si les données ne contiennent pas encore de valeurs que vous pouvez utiliser pour le regroupement, comme une catégorie de produits ou le nom de la région géographique où le magasin se trouve, vous pouvez introduire des groupes dans vos données en ajoutant des catégories. Lorsque vous créez des groupes dans Excel, vous devez taper ou sélectionner manuellement les groupes que vous souhaitez utiliser parmi les colonnes de votre feuille de calcul. Toutefois, dans un système relationnel, les hiérarchies telles que les catégories de produits sont généralement stockées dans une autre table que la table de faits ou de valeurs. Habituellement, la table de catégories est liée aux données de faits par une clé quelconque. Par exemple, supposons que vous découvriez que vos données contiennent les ID des produits, mais pas les noms des produits, ni leurs catégories. Pour ajouter la catégorie à une feuille de calcul Excel à deux dimensions, vous devez copier la colonne contenant les noms de catégories. Avec Power Pivot, vous pouvez importer la table des catégories de produits dans votre modèle de données, créer une relation entre la table contenant les données numériques et la liste des catégories de produits, puis utiliser les catégories pour regrouper les données. Pour plus d’informations, consultez Create une relation entre les tables.
Choix d’une fonction pour l’agrégation
Après avoir identifié et ajouté les regroupements à utiliser, vous devez déterminer les fonctions mathématiques à utiliser pour l’agrégation. Le terme « agrégation » est fréquemment employé comme synonyme des opérations mathématiques ou statistiques utilisées dans les agrégations, telles que les sommes, les moyennes, les valeurs minimales ou les nombres. Toutefois, Power Pivot vous permet de créer des formules personnalisées pour l’agrégation, en complément des agrégations standard fournies dans Power Pivot et Excel.
Par exemple, à partir de l’ensemble de valeurs et de regroupements utilisé dans les exemples précédents, vous pouvez créer des agrégations personnalisées qui répondent aux questions suivantes :
Nombres filtrés Combien de transactions a-t-on effectuées en un mois, en excluant la période de maintenance de fin de mois ?
Ratios utilisant des moyennes dans le temps Quel est le taux d’augmentation ou de diminution des ventes par rapport à la même période de l’année précédente ?
Valeurs minimales et maximales groupées Quels sont les premiers secteurs de vente par catégorie de produit ou par vente promotionnelle ?
Ajout d’agrégations aux formules et aux tableaux croisés dynamiques
Lorsque vous avez une idée générale de la façon dont vos données doivent être regroupées pour être pertinentes, ainsi que des valeurs avec lesquelles vous souhaitez travailler, vous pouvez décider s’il faut générer un tableau croisé dynamique ou créer des calculs dans une table. Power Pivot enrichit et améliore la capacité native d’Excel à créer des agrégations telles que les sommes, les comptages ou les moyennes. Vous pouvez créer des agrégations personnalisées dans Power Pivot, soit dans la fenêtre Power Pivot, soit dans la zone du tableau croisé dynamique Excel.
-
Dans une colonne calculée, créez des agrégations qui prennent en considération le contexte de ligne actuel pour récupérer les lignes associées d’une autre table, puis additionnez, comptabilisez ou calculez la moyenne des valeurs des lignes associées.
-
Dans une mesure, vous pouvez créer des agrégations dynamiques qui utilisent à la fois des filtres définis dans la formule et des filtres imposés par la conception du tableau croisé dynamique et la sélection de segments, d’en-têtes de colonne et d’en-têtes de ligne. Les mesures utilisant des agrégations standard peuvent être créées dans Power Pivot à l’aide de somme automatique ou en créant une formule. Vous pouvez également créer des mesures implicites à l’aide d’agrégations standard dans un tableau croisé dynamique dans Excel.
Ajout de regroupements à un tableau croisé dynamique
Lorsque vous concevez un tableau croisé dynamique, vous faites glisser les champs représentant des regroupements, des catégories ou des hiérarchies vers la section de colonnes et de lignes du tableau croisé dynamique pour regrouper les données. Vous faites ensuite glisser les champs qui contiennent des valeurs numériques vers la zone de valeurs pour permettre le comptage, le calcul de la moyenne ou l’addition de ces valeurs.
Si vous ajoutez des catégories à un tableau croisé dynamique, mais que les données de catégorie ne sont pas associées aux données de fait, vous risquez d’obtenir une erreur ou des résultats incongrus. Power Pivot tentera généralement de résoudre le problème en détectant et suggérant automatiquement des relations. Pour plus d’informations, consultez Utilisation des relations dans les tableaux croisés dynamiques.
Vous pouvez également faire glisser des champs dans des segments de façon à sélectionner certains groupes de données pour l’affichage. Les segments vous permettent de regrouper, de trier et de filtrer interactivement les résultats dans un tableau croisé dynamique.
Utilisation de regroupements dans une formule
Vous pouvez également utiliser des regroupements et des catégories pour agréger des données stockées dans des tables en créant des relations entre les tables, puis en concevant des formules qui exploitent ces relations pour rechercher des valeurs associées.
En d’autres termes, si vous souhaitez créer une formule qui regroupe des valeurs par catégorie, vous devez commencer par utiliser une relation pour connecter la table contenant les données de détail et les tables contenant les catégories, puis générer la formule.
Pour plus d’informations sur la création de formules utilisant des recherches, voir Recherches dans les formules PowerPivot.
Utilisation de filtres dans les agrégations
Une nouvelle fonctionnalité de Power Pivot offre la possibilité d’appliquer des filtres à des colonnes et à des tables de données, non seulement dans l’interface utilisateur et dans un tableau croisé dynamique ou un graphique, mais également dans les formules que vous utilisez pour calculer des agrégations. Les filtres peuvent être utilisés dans les formules à la fois dans les colonnes calculées et dans les s.
Par exemple, vous pouvez spécifier une table entière en tant qu’argument dans les nouvelles fonctions d’agrégation du langage DAX (Data Analysis Expression), au lieu de spécifier des valeurs à additionner ou à comptabiliser. Si vous n’appliquez aucun filtre à cette table, la fonction d’agrégation opérera sur toutes les valeurs figurant dans la colonne spécifiée de la table. Toutefois, dans DAX, vous pouvez créer un filtre dynamique ou statique sur la table afin que l’agrégation s’applique à un autre sous-ensemble de données selon la condition de filtre et le contexte actuel.
La combinaison de conditions et de filtres dans les formules vous permet de créer des agrégations qui changent selon les valeurs fournies dans les formules, ou selon la sélection de titres de lignes et d’en-têtes de colonne dans un tableau croisé dynamique.
Pour plus d’informations, voir Filtrer des données dans des formules.
Comparaison entre les fonctions d’agrégation DAX et Excel
Le tableau suivant répertorie quelques-unes des fonctions d’agrégation standard fournies par Excel et fournit des liens pour l’implémentation de ces fonctions dans Power Pivot. La version DAX de ces fonctions se comporte pour l’essentiel de la même manière que la version Excel, à quelques petites différences près en matière de syntaxe et de gestion de certains types de données.
Fonctions d’agrégation standard
Fonction |
Utiliser |
Retourne la moyenne (arithmétique) de tous les nombres d’une colonne. |
|
Retourne la moyenne (arithmétique) de toutes les valeurs d’une colonne. Gère des valeurs texte et non numériques. |
|
Compte le nombre de valeurs numériques dans une colonne. |
|
Compte le nombre de valeur d’une colonne qui ne sont pas vides. |
|
Retourne la valeur numérique la plus grande dans une colonne. |
|
Retourne la plus grande valeur d’un jeu d’expressions évalué sur une table. |
|
Retourne la plus petite valeur numérique dans une colonne. |
|
Retourne la plus petite valeur d’un jeu d’expressions évalué sur une table. |
|
Additionne tous les nombres d’une colonne. |
Fonctions d’agrégation DAX
DAX inclut des fonctions d’agrégation qui vous permettent de spécifier une table sur laquelle l’agrégation doit être effectuée. Par conséquent, au lieu de simplement calculer la somme ou la moyenne des valeurs d’une colonne, ces fonctions vous permettent de créer une expression qui définit de manière dynamique les données à agréger.
Le tableau ci-après répertorie les fonctions d’agrégation disponibles dans DAX.
Fonction |
Utiliser |
Calcule la moyenne d’un ensemble d’expressions évaluées sur une table. |
|
Compte un ensemble d’expressions évaluées sur une table. |
|
Compte le nombre de valeurs vides dans une colonne. |
|
Compte le nombre total de lignes d’une table. |
|
Compte le nombre de lignes retourné par une fonction de table imbriquée, comme une fonction de filtre. |
|
Retourne la somme d’un jeu d’expressions évalué sur une table. |
Différences entre les fonctions DAX et les fonctions d’agrégation d’Excel
Bien que ces fonctions portent les mêmes noms que leurs équivalents Excel, elles utilisent le moteur d’analyse en mémoire de Power Pivot et ont été réécrites pour pouvoir être utilisées avec des tables et des colonnes. Vous ne pouvez pas utiliser une formule DAX dans un classeur Excel et inversement. Elles ne peuvent être utilisées que dans la fenêtre Power Pivot et dans les tableaux croisés dynamiques reposant sur les données Power Pivot. Leur comportement peut aussi être légèrement différent. Pour plus d’informations, consultez les rubriques de référence des différentes fonctions.
La façon dont les colonnes sont évaluées dans une agrégation est également différente de la manière dont Excel gère les agrégations. Nous allons illustrer ce point à l’aide d’un exemple.
Supposons que vous souhaitez calculer la somme des valeurs de la colonne Amount de la table Sales. Vous créez à cet effet la formule suivante :
=SUM('Sales'[Amount])
Dans le cas le plus simple, la fonction obtient les valeurs d’une colonne non filtrée unique, et le résultat est le même que dans Excel qui ne fait toujours qu’additionner les valeurs de la colonne Amount. Dans Power Pivot, toutefois, la formule est interprétée comme « Obtenir la valeur dans Amount pour chaque ligne de la table Sales, puis additionner ces valeurs individuelles ». Power Pivot évalue chaque ligne sur laquelle l’agrégation est effectuée et calcule une valeur scalaire unique pour chaque ligne, puis effectue une agrégation sur ces valeurs. Par conséquent, le résultat d’une formule peut être différent si des filtres ont été appliqués à une table, ou si les valeurs sont calculées en fonction d’autres agrégations qui peuvent être filtrées. Pour plus d’informations, consultez Contexte dans les formules DAX.
Fonctions Time Intelligence DAX
Outre les fonctions d’agrégation de table décrites dans la section précédente, DAX propose des fonctions d’agrégation qui fonctionnent avec les dates et heures que vous spécifiez pour offrir une fonctionnalité Time Intelligence intégrée. Ces fonctions utilisent des plages de dates pour obtenir des valeurs associées et agréger ces valeurs. Vous pouvez également comparer les valeurs de plusieurs plages de dates.
Le tableau ci-après répertorie les fonctions Time Intelligence utilisables pour l’agrégation.
Fonction |
Utiliser |
Calcule une valeur à la fin de la période donnée. |
|
Calcule une valeur à la fin de la période avant la période donnée. |
|
Calcule une valeur sur l’intervalle qui démarre le premier jour de la période et se termine à la date la plus récente dans la colonne de date spécifiée. |
Les autres fonctions de la section fonction Time Intelligence (Fonctions Time Intelligence) sont des fonctions qui peuvent être utilisées pour récupérer des dates ou des plages de dates personnalisées à utiliser dans l’agrégation. Par exemple, vous pouvez utiliser la fonction DATESINPERIOD pour retourner une plage de dates et utiliser cette plage de dates comme argument d’une autre fonction pour calculer une agrégation personnalisée juste pour ces dates.