Office: Aggregationen in Power Pivot

Aggregationen in Power Pivot
Excel für Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Mehr... Weniger Aggregationen sind eine Möglichkeit zum reduzieren, zusammenfassen oder Gruppieren von Daten. Wenn Sie mit unformatierten Daten aus Tabellen oder anderen Datenquellen beginnen, sind die Daten oft flach, was bedeutet, dass viele Details vorhanden sind, die jedoch nicht in irgendeiner Weise organisiert oder gruppiert wurden. Dieser Mangel an Zusammenfassungen oder Strukturen kann es schwierig machen, Muster in den Daten zu erkennen. Ein wichtiger Bestandteil der Datenmodellierung besteht darin, Aggregationen zu definieren, die Muster als Antwort auf eine bestimmte geschäftliche Frage vereinfachen, abstrahieren oder zusammenfassen.

Die meisten gängigen Aggregationen, wie beispielsweise die Verwendung von Average, count, DistinctCount, Max, Minoder Sum , 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 mit Data Analysis Expressions (DAX)erstellte Formel.

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 Measurekönnen Sie dynamische Aggregationen erstellen, die beide in der Formel definierten Filter verwenden, und Filter, die durch den Entwurf der PivotTable und die Auswahl von datenschnitten, Spaltenüberschriften und Zeilenüberschriften erzwungen werden. Measures, die Standardaggregationen verwenden, können in Power Pivot mithilfe von AutoSumme oder durch Erstellen einer Formel erstellt werden. Sie können implizite Measures auch mithilfe von Standardaggregationen in einer PivotTable in Excel erstellen.
  
  

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 Datentabellen anzuwenden, und zwar nicht nur auf der Benutzeroberfläche und in einer PivotTable oder einem Diagramm, sondern auch in den Formeln, die Sie zum Berechnen von Aggregationen verwenden. Filter können in Formeln sowohl in berechneten Spalten als auch in s 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
In der folgenden Tabelle sind einige der von Excel bereitgestellten Standard Aggregationsfunktionen aufgelistet, und es werden Links zur Implementierung dieser Funktionen in Power Pivot bereitgestellt. Die DAX-Version dieser Funktionen verhält sich ähnlich wie die Excel-Version, mit einigen geringfügigen Unterschieden bei der Syntax und der Behandlung bestimmter Datentypen.

Standardaggregationsfunktionen

Funktion	Operator
Durchschnittliche	Gibt den Mittelwert (arithmetisches Mittel) aller Zahlen in einer Spalte zurück.
Mittelwert	Gibt den Mittelwert (arithmetisches Mittel) aller Werte in einer Spalte zurück. Unterstützt Text und nicht numerische Werte.
Anzahl	Zählt die numerischen Werte in einer Spalte.
COUNTA	Zählt die Werte in einer Spalte, die nicht leer sind.
Max	Gibt den größten numerischen Wert in einer Spalte zurück.
MAXX	Gibt den größten Wert in einem Satz von Ausdrücken zurück, die für eine Tabelle ausgewertet wurden.
Min	Gibt den kleinsten numerischen Wert in einer Spalte zurück.
Biest	Gibt den kleinsten Wert in einem Satz von Ausdrücken zurück, die für eine Tabelle ausgewertet wurden.
Summe	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
AVERAGEX	Ermittelt den Mittelwert der für eine Tabelle ausgewerteten Ausdrücke.
COUNTAX	Zählt die für eine Tabelle ausgewerteten Ausdrücke.
ANZAHLLEEREZELLEN	Zählt die leeren Werte in einer Spalte.
COUNTX	Ermittelt die Gesamtanzahl der Zeilen in einer Tabelle.
CountRows	Ermittelt die Anzahl der Zeilen, die von einer geschachtelten Tabellenfunktion zurückgegeben wird, z. B. einer Filterfunktion.
SUMX	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:

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
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR	Berechnet einen Wert am Kalenderende des angegebenen Zeitraums.
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR	Berechnet vor dem angegebenen Zeitraum einen Wert am Kalenderende des Zeitraums.
TOTALMTD TOTALYTD TOTALQTD	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 Zeitintelligenz Funktion (zeitintelligenzfunktionen) sind Funktionen, die verwendet werden können, um Datumsangaben oder benutzerdefinierte Datumsbereiche abzurufen, die in Aggregation verwendet werden sollen. 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.

:)