Inkl. Video + Übungsdateien
veröffentlicht am 02.07.2024 überprüft/ aktualisiert am 18.07.2024
Measures in Power BI oder Excel Power Pivot berechnen ein Ergebnis mithilfe einer Ausdrucksformel 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 und in Microsoft Power BI. 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 Excelfunktionen nicht nur zum Verwechseln ähnlich, sie funktionieren auch genauso. Der größte Unterschied zu Excelfunktionen ist, dass es DAX-Funktionen nur in englischer Sprache gibt. Ansonsten werden sie genau wie Excelfunktionen 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 oder als Diagramm in Power Pivot oder Power BI darstellen.
Wir haben zusätzlich ein Infovideo zum Thema „Measures und DAX-Formeln“ für Sie erstellt. Hier gelangen Sie direkt zum Video … hier klicken
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 oder einem Power BI Diagramm 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 beziehungsweise Power BI „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 zweier Beispieldateien, die zum Download für Sie bereits stehen, damit Sie die nachfolgenden Erklärungen direkt im jeweiligen Programm nachvollziehen können:
… Power BI Datei downloaden
… 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 sowohl in Power Pivot als auch Power BI 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 „=“. Berücksichtigen Sie, dass Sie bei der Erstellung von Measures in Excel Power Pivot zusätzlich ein „:“ vor dem „=“ einfügen müssen.
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.
Ebenfalls merken wir an, wenn eine Tabelle in das Datenmodell von Power BI geladen wird, die eine Datumsspalte enthält, so wird standardmäßig eine Datumshierarchie erstellt, deren Felder in den Visualisierungen verwendet werden können. In Power Pivot entsteht eine ähnliche Datumshierarchie, sobald man aus der in das Datenmodell geladenen Tabelle eine Pivot Tabelle erstellt.
Verwenden wir die Felder dieser Datumshierarchien gemeinsam mit unserem „Gesamtumsatz“-Measure in der Berichtsansicht, erhalten wir folgendes Ergebnis. In Abb. 2 sehen Sie die Power Pivot-Tabelle in Excel und in Abb. 3 ein Matrix-Visual in Power BI Desktop (die Diagramme in Power Bi werden Visuals genannt).
In Abb. 4 und 5 haben wir der Pivot Tabelle und dem Matrix-Visual 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 Datumshierarchien 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 Formel in Power BI 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. 6 und 7 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. 8 und 9 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.
Dauer des Videos: 3:02
Hier gelangen Sie direkt zu den beiden Übungsdateien … hier klicken
Damit Sie in Power Pivot in Excel oder in der Softwarelandschaft Power BI 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 erlernen Sie in unseren beiden Lernpfaden:
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 und in der Matrix bei Power BI Desktop.
Laden Sie die beiden Dateien herunter ( hier klicken ) und experimentieren Sie damit!
Wir wünschen viel Erfolg bei der Anwendung!
Auflistung ALLER Kurse in Tabellenform
Excel Kurse ... hier klicken
Power BI Kurse ... hier klicken