Aggregationen in Formeln

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 Jahres 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 gab es in einem Monat?

Durchschnittsberechnung   Was waren die durchschnittlichen Verkäufe eines Vertriebsmitarbeiters in diesem Monat?

Mindest- und Höchstwerte   Welche Verkaufsgebiete zählen in Hinsicht auf verkaufte Einheiten zu den oberen fünf?

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 einer Excel-Flatfile hinzuzufügen. In einer PowerPivot-Arbeitsmappe können Sie jedoch die Produktkategorietabelle in die Arbeitsmappe 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 Beziehungen zwischen Tabellen.

Nachdem Sie die zu verwendenden Gruppen festgelegt und hinzugefügt haben, müssen Sie entscheiden, welche mathematischen Funktionen für die Aggregation verwendet werden sollen. Das Wort Aggregation wird oft als Synonym für die mathematischen oder statistischen Operationen verwendet, die in Aggregationen zum Einsatz kommen, wie z. B. Summen, Mittelwerte, Minimum oder Anzahl. Mit PowerPivot für Excel können Sie jedoch neben den Standardaggregationen von 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 Zählungen   Wie viele Transaktionen gab es in einem Monat ohne den Wartungszeitraum am Ende des Monats?

Verhältnisse mit Mittelwerten im Zeitverlauf   Wie hoch war das prozentuale Wachstum oder der Rückgang beim Vertrieb 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?

Wenn Sie eine PivotTable entwerfen, ziehen Sie Felder, die 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 PowerPivot für Excel das Problem zu beheben, indem Beziehungen automatisch erkannt und vorgeschlagen werden. Weitere Informationen finden Sie unter Verwenden von Beziehungen in PivotTables.

Sie können Felder auch in Datenschnitte ziehen, um bestimmte Gruppen von Daten zum Anzeigen auszuwählen. Datenschnitte sind ein neues Feature in Excel und PowerPivot für Excel, mit der Sie Ergebnisse in einer PivotTable interaktiv gruppieren, sortieren und filtern können.

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 für Suchvorgänge finden Sie im folgenden Thema: Beziehungen and Suchvorgänge in Formeln.

Ein neues Feature in PowerPivot ist die Möglichkeit, Filter auf Datenspalten und -tabellen anzuwenden, und dies kann nicht nur auf der Benutzeroberfläche und innerhalb einer PivotTable oder eines PivotCharts erfolgen, sondern auch in den Formeln, mit denen Sie Aggregationen berechnen. Filter können in Formeln sowohl in berechneten Spalten als auch in Measures verwendet werden.

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

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.

Obwohl diese Funktionen die gleichen Namen wie ihre Excel-Äquivalente aufweisen, verwenden sie das PowerPivot-VertiPaq-Modul 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 PowerPivot-Fenster und in PivotTables verwendet werden, die auf PowerPivot-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 Amount-Spalte der Sales-Tabelle 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 Amount-Spalte addiert. In PowerPivot jedoch wird die Formel so interpretiert, dass der Wert in Amount für jede Zeile der Tabelle Sales abgerufen werden soll, und anschließend werden die individuellen Werte addiert. In PowerPivot wird jede Zeile ausgewertet, für die die Aggregation durchgeführt wird, und eine einzelner Skalarwert wird für jede Zeile berechnet. Anschließend wird eine Aggregation für diese Werte durchgeführt. 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.