Inklusive Übungsdatei
veröffentlicht am 02.07.2024 überprüft/ aktualisiert am 06.03.2025
Measures in Excel Power Pivot berechnen Ergebnisse mithilfe von Ausdrucksformeln basierend auf der DAX-Formelsprache.
Nehmen wir an, Sie verkaufen viele verschiedene Artikel: Measures können Ihnen sagen, wie viele Artikel Sie schon verkauft haben, wie hoch der Verkaufserlös ist oder wie hoch der Wert einer durchschnittlichen Kundenbestellung ist. Und natürlich können Ihnen Measures auch sagen, wie viele Artikel in roter Farbe Kunde XY letzten April bei Ihnen gekauft hat und wie sich seine Bestellmengen im Vergleich zum April des Vorjahres entwickelt haben.
Und wie können Sie das alles berechnen? Mithilfe von DAX-Formeln.
DAX-Formeln funktionieren gleich wie Formeln in Microsoft Excel, unterscheiden sich aber leicht in der Schreibweise und den verfügbaren Funktionen. Denn sie verwenden eine eigene Formelsprache, die auch für die Namensgebung verantwortlich ist – der Data Analysis Expressions.
Sie werden angewendet in Excel Power Pivot. Um berechnete Spalten oder Measures erstellen zu können, benötigen auch die DAX-Formeln eigene Funktionen, genau wie Excelformeln.
Die DAX-Funktionen selbst sehen Funktionen in Excel nicht nur zum Verwechseln ähnlich, sie funktionieren auch sehr ähnlich. Der größte Unterschied zu Excel-Funktionen ist, dass es DAX-Funktionen nur in englischer Sprache gibt. Ansonsten werden sie genau wie Funktionen im „klassischen“ Excel verwendet, um spezifische Aufgaben zu erledigen.
DAX beinhaltet eine Bibliothek mit über 200 Funktionen, Operatoren und Konstrukten. Diese Bibliothek bietet große Flexibilität beim Berechnen von Ergebnissen für nahezu jede benötigte Datenanalyse durch die Erstellung von Measures.
Also ist ein Measure eine DAX-Formel, die mithilfe von DAX-Funktionen in Kombination mit Ihren Daten eine Berechnung anstellt. Und das Ergebnis dieser Berechnung können Sie dann als Zahl, Pivot-Tabelle oder als Diagramm darstellen.
Beim alltäglichen Arbeiten mit Pivot Tabellen oder Diagrammen ist ihnen dieser Unterschied eventuell noch gar nicht bewusst aufgefallen, aber tatsächlich gibt es zwei Arten von Measures.
Wenn Sie zum Beispiel bei einer Power Pivot Tabelle die Umsatzspalte aus ihrem Datenmodell verwenden, indem Sie sie in die Feldliste ziehen, dann bildet sich automatisch die Gesamtsumme. Sie haben zwar keinerlei Formel geschrieben, sehen aber trotzdem die Summen fürs Quartal, fürs Jahr usw. Je nach Anwendung können Sie auch auswählen, ob der Sie den Durchschnitt, das Maximum oder das Minimum anzeigen wollen. Das sind die impliziten Measures. Power Pivot „nimmt an“, „impliziert“ also, dass diese Summen gebildet werden sollen.
Jetzt wissen Sie auch, was explizite Measures sind. Das sind die Formeln, bei denen Sie selbst „ausdrücklich“ – also „explizit“ – bestimmen, was genau berechnet werden soll. Also alle DAX-Formeln die sie selber geschrieben haben.
Aber jetzt genug von der Theorie, gehen wir zur Praxis über.
Den Unterschied zwischen einem impliziten und einem expliziten Measure erklären wir anhand einer Beispieldatei, die zum Download für Sie bereits steht, damit Sie die nachfolgenden Erklärungen direkt nachvollziehen können:
… Excel Datei downloaden
Schauen wir uns zuerst einmal die Summenformel im Datenmodell an. Wir wollen den „Gesamtumsatz“ ermitteln, also die Spalte [Umsatz] im Datenmodell summieren. Eine Umsatztabelle mit dem Namen ‘Umsätze 2021-2023‘ bildet die Ausgangsbasis. Sie wurde in das jeweilige Datenmodell geladen und ist folgendermaßen strukturiert (Abb. 1):
Die Summenformel im Datenmodell hat grundsätzlich folgende Syntax:
=SUM(<column>)
Die Summen-Formel für unser Beispiel sieht daher so aus:
Gesamtumsatz:=SUM(‘Umsätze 2021-2023‘[Umsatz])
Wichtiger Hinweis zum Aufbau des Measures: Der Name eines Measures steht immer vor dem „:=“.
In unserem nachfolgenden Beispiel stellen wir den Measures noch die verwendeten Formeln in Anführungszeichen voran (z.B. „Sum“ Gesamtumsatz = …), um die Formeln unterscheidbar zu machen und Ihnen das Experimentieren zu erleichtern. Sie sehen dann sofort in der Spaltenüberschrift im der jeweiligen Tabelle bzw. im Diagramm, welche Version einer Formel Sie gerade verwenden, ohne umständlich nachschauen zu müssen.
Verwenden wir die Felder der Datumsspalten gemeinsam mit unserem „Gesamtumsatz“-Measure in einer Pivot-Tabelle, erhalten wir ein Ergebnis, wie Sie es in Abb. 2 sehen:
In Abb. 3 haben wir der Pivot Tabelle zusätzlich zu unserem Measure „Gesamtumsatz“ noch das Feld „Umsatz“ aus unserer Tabelle hinzugefügt. Sie sehen, die Summen sind identisch, wir haben also mit der Formel =SUM() ein explizites Measure hergestellt, dessen Ergebnis mit dem Feld „Umsatz“ in der Tabelle schon implizit vorhanden war, ganz ohne Formel.
Wenn wir zusätzlich den Umsatz für 2021 berechnen wollen, können wir zwar weiterhin die automatisch erstellten Datumsspalten verwenden. Allerdings findet sich dazu kein implizites Measure mehr in unserer Tabelle, sondern wir müssen mit einer DAX-Funktion ein explizites Measure erstellen, in dem unsere Berechnung an eine Bedingung geknüpft ist, in unserem Fall die Jahreszahl.
Die geläufigste DAX-Funktion, die mit Bedingungen arbeitet, ist die Funktion =CALCULATE().
In Worten würde die entsprechende Formel lauten:
„Summiere alle Umsätze, bei denen in der Datumsspalte das Jahr 2021 steht.“
Die DAX-Formel in Power Pivot selbst sieht dann folgendermaßen aus:
Umsatz 2021:=CALCULATE(SUM(‚Umsätze 2021-2023′[Umsatz]), ‚Umsätze 2021-2023′[Datum].[Jahr]=2021)
Oder in unserem Fall:
Umsatz 2021:=CALCULATE([Gesamtumsatz], ‚Umsätze 2021-2023′[Datum].[Jahr]=2021)
Denn wir haben ja schon das Measure Gesamtumsatz erstellt, das die Summenformel bereits beinhaltet und unser Measure kürzer und lesbarer macht.
Diese Formel liefert grundsätzlich das richtige Ergebnis, das in weiterer Folge verwendet werden kann, um z. B. Zeiträume zu vergleichen. Aber für eine Darstellung in einer Tabelle oder Matrix ist es eher ungeeignet und kann zu einer Falschinterpretation führen, wie wir in Abb. 4 sehen können.
Die Umsätze werden tatsächlich für alle Jahre berechnet, allerdings werden aufgrund unserer Bedingung für alle angezeigten Jahre nur jeweils die Werte für das Jahr 2021 angezeigt. Wenn wir möchten, dass die Werte des ausgewählten Jahres tatsächlich nur im entsprechenden Tabellenbereich (in unserem Fall im Jahr 2021) angezeigt werden, müssen wir unserer Formel noch einen Schritt hinzufügen, also eine verschachtelte Funktion erzeugen. Und dazu benutzen wir die FILTER-Funktion.
Die Formel sieht dann folgendermaßen aus:
Umsatz 2021:=CALCULATE([Gesamtumsatz], FILTER(‚Umsätze 2021-2023‘,’Umsätze 2021-2023′[Datum].[Jahr]=2021))
Die interne Tabelle, die durch diese Berechnung erstellt wird, enthält im Gegensatz zu vorher jetzt nur noch die Werte des entsprechenden Jahres, kann also keine anderen Jahre mehr anzeigen.
In Abb. 5 sehen wir die Auswirkung der Filterfunktion. Zusätzlich haben wir noch die Jahre 2022 und 2023 berechnet. Dafür haben wir die Formel einfach kopiert und dann im Titel wie in der Formel einfach nur das Jahr geändert.
Damit Sie in Power Pivot in Excel Daten professionell analysieren können, ist es wichtig, eigene Measures erstellen zu können und den Unterschied zwischen impliziten und expliziten Measures zu kennen. Immer wenn Sie eigene Measures erstellen, dann erstellen Sie „automatisch“ explizite Measures. Andere bereits erstellte Measures werden häufig in neue Measures integriert. Ebenso werden für leichtere Bearbeitung und bessere Lesbarkeit die gängigsten impliziten Measures häufig als explizite Measures erstellt. In unserem Beispiel treffen die obigen Aussagen auf das Measure „Gesamtumsatz“ zu.
Es gibt eine Vielzahl an DAX-Funktionen für alle denkbaren Berechnungen. Desto mehr Funktionen Sie kennen und anwenden können, desto einfacher fällt Ihnen die Erstellung von Measures und komplexere Analysen werden möglich. Den Umgang mit häufig eingesetzten DAX-Funktionen (und noch viel Mehr zum Thema Datenanalyse mit Excel) erlernen Sie in unserem Lernpfad:
Zuletzt möchten wir anmerken, dass es nicht nur entscheidend ist, dass Ihre Berechnungen korrekt sind, sondern auch, in welchem Kontext Sie Ihre Ergebnisse präsentieren. Hätten sie in unserem Beispiel eine andere Form der Darstellung gewählt, dann wäre Ihnen der Unterschied bei einigen Formeln im Berichtskontext eventuell gar nicht aufgefallen. Beachten Sie zum Beispiel die Darstellungsunterschiede beim CALCULATE-Measure in der Power Pivot Tabelle
Laden Sie die beiden Beispiel-Datei herunter ( hier klicken ) und experimentieren Sie damit!
Wir wünschen viel Erfolg bei der Anwendung!
Auflistung ALLER Kurse in Tabellenform
Zu den Excel Kursen