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 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.

Fonctions d’agrégation standard

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.

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.