Aggregationen sind eine Möglichkeit zum Zusammenführen, Zusammenfassen oder Gruppieren von Daten. Wenn Sie mit Rohdaten aus Tabellen oder anderen Datenquellen beginnen, sind die Daten häufig flach, d. h., es gibt viele Details, aber sie wurden in keinem Fall organisiert oder gruppieren. Das Fehlen von Zusammenfassungen oder Strukturen macht es möglicherweise schwierig, Muster in den Daten zu finden. Ein wichtiger Teil der Datenmodellierung ist das Definieren von Aggregationen, die Muster vereinfachen, abstrahieren oder zusammenfassen, um eine bestimmte Geschäftsfrage zu beantworten.
Die am häufigsten verwendeten Aggregationen, z. B. diejenigen, die MITTELWERT, COUNT,DISTINCTCOUNT,MAX,MINoder SUM verwenden, können mithilfe von AutoSumme automatisch in einem Measure erstellt werden. Andere Aggregationstypen wie AVERAGEX,COUNTX,COUNTROWSoder SUMXgeben eine Tabelle zurück und erfordern eine Formel, die mithilfe von Data Analysis Expressions (DAX) erstellt wurde.
Grundlegendes zu Aggregationen in Power Pivot
Auswählen von Gruppen zur Aggregation
Beim Aggregieren von Daten gruppieren Sie die Daten nach Attributen, wie z. B. Produkt, Preis, Region oder Datum, und definieren dann eine Formel, mit der alle Daten in der Gruppe verarbeitet werden. Beispielsweise erstellen Sie mit einem Gesamtbetrag für ein Jahr eine Aggregation. Wenn Sie dann das Verhältnis des aktuellen Jahrs gegenüber dem vorherigen Jahr erstellen und die zugehörigen Prozentsätze darstellen, ist dies eine andere Art von Aggregation.
Die Entscheidung über die Gruppierung der Daten ist von der Geschäftsfrage abhängig. Mit Aggregationen können z. B. die folgenden Fragen beantwortet werden:
Zählungen Wie viele Transaktionen wurden in einem Monat ausgeführt?
Durchschnittsberechnung Wie hoch waren die durchschnittlichen Verkaufszahlen pro Vertriebsmitarbeiter in diesem Monat?
Mindest- und Höchstwerte In welchen fünf Verkaufsgebieten wurden die meisten Einheiten verkauft?
Um eine Berechnung zu erstellen, die diese Fragen beantwortet, müssen Sie über detaillierte Daten mit den zu zählenden oder zu addierenden Werten verfügen, und diese numerischen Daten müssen in einer Beziehung mit den Gruppen stehen, die Sie zum Organisieren der Ergebnisse verwenden.
Wenn die Daten noch keine Werte zum Gruppieren enthalten, z. B. eine Produktkategorie oder den Namen des geografischen Orts, an dem sich das Geschäft befindet, sollten Sie durch Hinzufügen von Kategorien Ihre Daten gruppieren. Wenn Sie Gruppen in Excel erstellen, müssen Sie die Gruppen, die Sie aus den Spalten im Arbeitsblatt verwenden möchten, manuell eingeben oder auswählen. In einem relationalen System werden jedoch Hierarchien, wie z. B. Kategorien für Produkte, oft in einer anderen Tabelle als der Fakten- oder Wertetabelle gespeichert. Normalerweise wird die Kategorietabelle durch eine Art von Schlüssel mit den Faktendaten verknüpft. Angenommen, Ihre Daten enthalten Produkt-IDs, jedoch nicht die Namen oder Kategorien der Produkte. Sie müssten den Namen in die Spalte mit den Kategorienamen kopieren, um die Kategorie einem einfachen Excel-Arbeitsblatt hinzuzufügen. In Power Pivot können Sie die Produktkategorietabelle in das Datenmodell importieren, eine Beziehung zwischen der Tabelle mit den Zahlendaten und der Produktkategorieliste erstellen und anschließend die Kategorien zum Gruppieren von Daten verwenden. Weitere Informationen finden Sie unter Erstellen einer Beziehung zwischen Tabellen.
Auswählen einer Funktion zur Aggregation
Nachdem Sie die zu verwendenden Gruppierungen festgelegt und hinzugefügt haben, müssen Sie entscheiden, welche mathematischen Funktionen für die Aggregation verwendet werden sollen. Der Begriff Aggregation wird oft als Synonym für die mathematischen oder statistischen Operationen verwendet, die in Aggregationen zum Einsatz kommen, z. B. zum Ermitteln von Summen, Mittelwerten, Mindestwerten oder Anzahlen. Mit Power Pivot können Sie jedoch neben den Standardaggregationen in Power Pivot und Excel auch benutzerdefinierte Aggregationsformeln erstellen.
Beispielsweise könnten Sie mit demselben Satz von Werten und Gruppierungen aus den vorherigen Beispielen benutzerdefinierte Aggregationen erstellen, um die folgenden Fragen zu beantworten:
Gefilterte Anzahl Wie viele Transaktionen wurden in einem Monat mit Ausnahme des Wartungszeitraums am Ende des Monats ausgeführt?
Verhältnisse mit Mittelwerten im Zeitverlauf Wie hoch war das prozentuale Wachstum oder der Rückgang bei den Vertriebszahlen im Vergleich zum selben Zeitraum des letzten Jahres?
Gruppierte Mindest- und Höchstwerte Welche Verkaufsgebiete wurden für einzelne Produktkategorien oder Werbeaktionen am besten bewertet?
Hinzufügen von Aggregationen zu Formeln und PivotTables
Wenn Sie eine allgemeine Vorstellung davon haben, wie die Daten sinnvoll gruppiert werden sollten und mit welchen Werten Sie arbeiten möchten, können Sie eine PivotTable erstellen oder Berechnungen innerhalb einer Tabelle durchführen. Power Pivot erweitert und verbessert die programmeigene Fähigkeit von Excel, Aggregationen wie Summen, Anzahlen oder Mittelwerte zu erstellen. Sie können benutzerdefinierte Aggregationen in Power Pivot entweder innerhalb des Power Pivot-Fensters oder innerhalb des PivotTable-Bereichs in Excel erstellen.
-
In einer berechneten Spalte können Sie Aggregationen erstellen, die den aktuellen Zeilenkontext berücksichtigen, um verknüpfte Zeilen aus einer anderen Tabelle abzurufen, und diese Werte in den verknüpften Zeilen dann addieren, zählen oder deren Mittelwert bilden.
-
In einem Measure können Sie dynamische Aggregationen erstellen, die sowohl in der Formel definierte Filter als auch Filter verwenden, die durch den Entwurf der PivotTable und die Auswahl von Datenschnitten, Spaltenüberschriften und Zeilenüberschriften vorgegeben werden. Measures, die Standardaggregationen verwenden, können in einer Power Pivot mithilfe von AutoSumme oder durch Erstellen einer Formel erstellt werden. Sie können auch implizite Measures mithilfe von Standardaggregationen in einer PivotTable in Excel.
Hinzufügen von Gruppierungen zu einer PivotTable
Wenn Sie eine PivotTable entwerfen, ziehen Sie Felder, die Gruppierungen, Kategorien oder Hierarchien darstellen, in die Spalten- und Zeilenabschnitte der PivotTable, um die Daten zu gruppieren. Anschließend ziehen Sie Felder mit numerischen Werten in den Wertebereich, damit diese gezählt werden können oder deren Mittelwert oder Summe gebildet werden kann.
Wenn Sie einer PivotTable Kategorien hinzufügen, die Kategoriedaten jedoch keinen Bezug zu den Faktendaten haben, werden möglicherweise Fehler oder unerwartete Ergebnisse angezeigt. Normalerweise versucht Power Pivot, das Problem zu beheben, indem Beziehungen automatisch erkannt und vorgeschlagen werden. Weitere Informationen finden Sie unter Arbeiten mit Beziehungen in PivotTables.
Sie können Felder auch in Datenschnitte ziehen, um bestimmte Gruppen von Daten zum Anzeigen auszuwählen. Mit Datenschnitten können die Ergebnisse in einer PivotTable interaktiv gruppiert, sortiert und gefiltert werden.
Arbeiten mit Gruppierungen in einer Formel
Sie können auch Gruppierungen und Kategorien verwenden, um in Tabellen gespeicherte Daten zu aggregieren, indem Sie Beziehungen zwischen Tabellen erstellen und dann Formeln angeben, die anhand dieser Beziehungen nach verknüpften Werten suchen.
Wenn Sie eine Formel erstellen möchten, die Werte nach einer Kategorie gruppiert, verknüpfen Sie mithilfe einer Beziehung zuerst die Tabelle, die die ausführlichen Daten enthält und die Tabellen, die die Kategorien enthalten, und erstellen Sie dann die Formel.
Weitere Informationen zum Erstellen von Formeln, in denen Suchen verwendet werden, finden Sie unter Suchvorgänge in PowerPivot-Formeln.
Verwenden von Filtern in Aggregationen
Ein neues Feature in Power Pivot ist die Möglichkeit, Filter auf Spalten und Tabellen mit Daten anzuwenden, und zwar nicht nur auf der Benutzeroberfläche und innerhalb einer PivotTable oder eines Diagramms, sondern auch in den Formeln, die Sie zum Berechnen von Aggregationen verwenden. Filter können sowohl in berechneten Spalten als auch in s in Formeln verwendet werden.
Beispielsweise können Sie in den neuen DAX-Aggregationsfunktionen anstelle von Werten, die addiert oder gezählt werden sollen, eine ganze Tabelle als Argument angeben. Wenn Sie keine Filter auf diese Tabelle angewendet haben, werden mit der Aggregationsfunktion alle Werte in der angegebenen Spalte der Tabelle verarbeitet. In DAX können Sie jedoch dynamische oder statische Filter für die Tabelle erstellen, sodass die Aggregation je nach Filterbedingung und aktuellem Kontext für unterschiedliche Teilmengen ausgeführt wird.
Durch Kombinieren von Bedingungen und Filtern in Formeln können Sie Aggregationen erstellen, die je nach den in den Formeln bereitgestellten Werten oder je nach Auswahl von Zeilen- oder Spaltenüberschriften in einer PivotTable geändert werden.
Weitere Informationen finden Sie unter Filtern von Daten in Formeln.
Vergleich von Excel-Aggregationsfunktionen und DAX-Aggregationsfunktionen
Die folgende Tabelle enthält einige der Standardaggregationsfunktionen, die von Excel bereitgestellt werden, und stellt Links zur Implementierung dieser Funktionen in Power Pivot. Die DAX-Version dieser Funktionen verhält sich im Großen und Großen wie die Excel-Version, mit einigen geringfügigen Unterschieden bei der Syntax und der Behandlung bestimmter Datentypen.
Standardaggregationsfunktionen
|
Funktion |
Operator |
|
Gibt den Mittelwert (arithmetisches Mittel) aller Zahlen in einer Spalte zurück. |
|
|
Gibt den Mittelwert (arithmetisches Mittel) aller Werte in einer Spalte zurück. Unterstützt Text und nicht numerische Werte. |
|
|
Zählt die numerischen Werte in einer Spalte. |
|
|
Zählt die Werte in einer Spalte, die nicht leer sind. |
|
|
Gibt den größten numerischen Wert in einer Spalte zurück. |
|
|
Gibt den größten Wert in einem Satz von Ausdrücken zurück, die für eine Tabelle ausgewertet wurden. |
|
|
Gibt den kleinsten numerischen Wert in einer Spalte zurück. |
|
|
Gibt den kleinsten Wert in einem Satz von Ausdrücken zurück, die für eine Tabelle ausgewertet wurden. |
|
|
Addiert alle Zahlen in einer Spalte. |
DAX-Aggregationsfunktionen
DAX enthält Aggregationsfunktionen, mit denen Sie eine Tabelle angeben können, für die die Aggregation ausgeführt werden soll. Statt die Werte in einer Spalte lediglich zu addieren oder ihren Mittelwert zu berechnen, können Sie mit diesen Funktionen einen Ausdruck erstellen, der die zu aggregierenden Daten dynamisch definiert.
In der folgenden Tabelle sind die in DAX verfügbaren Aggregatfunktionen aufgelistet.
|
Funktion |
Operator |
|
Ermittelt den Mittelwert der für eine Tabelle ausgewerteten Ausdrücke. |
|
|
Zählt die für eine Tabelle ausgewerteten Ausdrücke. |
|
|
Zählt die leeren Werte in einer Spalte. |
|
|
Ermittelt die Gesamtanzahl der Zeilen in einer Tabelle. |
|
|
Ermittelt die Anzahl der Zeilen, die von einer geschachtelten Tabellenfunktion zurückgegeben wird, z. B. einer Filterfunktion. |
|
|
Gibt die Summe einer Reihe von Ausdrücken zurück, die für eine Tabelle ausgewertet werden. |
Unterschiede zwischen Aggregationsfunktionen in DAX und in Excel
Obwohl diese Funktionen die gleichen Namen wie ihre Excel-Äquivalente aufweisen, verwenden sie das In-Memory-Analysemodul von Power Pivot und wurden umgeschrieben, um die Kompatibilität mit Tabellen und Spalten zu gewährleisten. Sie können keine DAX-Formel in einer Excel-Arbeitsmappe verwenden (und umgekehrt). Sie können nur im Power Pivot-Fenster und in PivotTables verwendet werden, die auf Power Pivot-Daten basieren. Darüber hinaus kann das Verhalten dieser Funktionen leicht abweichen, obwohl sie die gleichen Namen haben. Weitere Informationen finden Sie in den Themen zu den einzelnen Funktionen.
Die Auswertung von Spalten in einer Aggregation unterscheidet sich ebenfalls von der Behandlung von Aggregationen in Excel. Durch folgendes Beispiel wird dies veranschaulicht.
Angenommen, Sie möchten die Summe der Werte in der Spalte "Amount" der Tabelle "Sales" berechnen, weshalb Sie die folgende Formel erstellen:
=SUM('Sales'[Amount])
Im einfachsten Fall ruft die Funktion die Werte in einer einzelnen ungefilterten Spalte ab, und das Ergebnis entspricht dem Ergebnis in Excel, das lediglich die Werte in der Spalte "Amount" addiert. In Power Pivot wird die Formel jedoch folgendermaßen interpretiert: "Rufe den Wert in der Spalte 'Amount' für jede Zeile der Tabelle 'Sales' ab, und addiere dann diese einzelnen Werte". Power Pivot wertet jede Zeile aus, für die die Aggregation durchgeführt wird, berechnet für jede Zeile einen einzelnen Skalarwert und führt dann für diese Werte eine Aggregation durch. Daher kann das Ergebnis einer Formel abweichen, wenn Filter auf eine Tabelle angewendet wurden oder wenn die Werte auf Grundlage anderer Aggregationen berechnet werden, die möglicherweise gefiltert sind. Weitere Informationen finden Sie unter Kontext in DAX-Formeln.
DAX-Zeitintelligenzfunktionen
Neben den im vorherigen Abschnitt beschriebenen Tabellenaggregationsfunktionen verfügt DAX über Aggregationsfunktionen, die mit von Ihnen bereitgestellten Datums- und Uhrzeitangaben arbeiten, um integrierte Zeitintelligenzfunktionen zu unterstützen. Bei diesen Funktionen werden Datumsbereiche verwendet, um verknüpfte Werte abzurufen und die Werte zu aggregieren. Außerdem können Werte über Datumsbereiche hinweg verglichen werden.
In der folgenden Tabelle sind die Zeitintelligenzfunktionen aufgeführt, die zur Aggregation verwendet werden können.
|
Funktion |
Operator |
|
Berechnet einen Wert am Kalenderende des angegebenen Zeitraums. |
|
|
Berechnet vor dem angegebenen Zeitraum einen Wert am Kalenderende des Zeitraums. |
|
|
Berechnet einen Wert für das Intervall, das mit dem ersten Tag des Zeitraums beginnt und mit dem letzten Datum in der angegebenen Datumsspalte endet. |
Die anderen Funktionen im Abschnitt mit den Zeitintelligenzfunktionen (Zeitintelligenzfunktionen) sind Funktionen, die zum Abrufen von Datumsangaben oder benutzerdefinierten Datumsbereichen verwendet werden können, die in Aggregation verwendet werden. Sie können mithilfe der DATESINPERIOD-Funktion einen Datumsbereich zurückgeben und diesen Satz von Datumsangaben als Argument für eine andere Funktion verwenden, um eine benutzerdefinierte Aggregation ausschließlich für diese Datumsangaben zu berechnen.
