Wer zusätzliche Informationen aus einer Pivot-Tabelle gewinnen möchte, kann die erforderlichen Berechnungen über sogenannte berechnete Felder realisieren. Diese sind allerdings nur bei bestimmten Berechnungen einsetzbar. Eine Multiplikation beispielsweise kann unsinnige Ergebnisse liefern.
Hier schaffen die leistungsfähigen DAX-Measures Abhilfe. Mit ihnen lassen sich auch komplexe Berechnungen korrekt durchführen. Bis Excel 2013 war zur Formelerstellung noch das Add-In Power Pivot notwendig. In Excel 2016 ist das Hinzufügen von DAX-Measures auch ohne Power Pivot möglich.
Wo die Probleme berechneter Feldern liegen und wie einfach die Lösung mit DAX-Measures ist, zeige ich im folgenden Beispiel.
Der Pivot-Tabelle ein berechnetes Element hinzufügen
Auf Basis der folgenden Daten möchte ich mit Hilfe einer Pivot-Tabelle die Umsätze der beiden Produkte ermitteln.
Die Tabelle enthält die Bestellmengen und Stückpreise, aber keine (redundante) Spalte »Umsatz«
Den Umsatz ermittele ich mit Hilfe eines berechneten Feldes, das ich über die Registerkarte PivotTable-Tools/Analysieren anlege.
Mit einem berechneten Feld lässt sich die Datenbasis schnell ergänzen
Warum das berechnete Feld falsche Ergebnisse liefert
Was auf den ersten Blick wie eine einfache Lösung aussieht, entpuppt sich als falsch: Das berechnete Feld multipliziert die Summen von Anzahl und Einzelpreis, anstatt die einzelnen Umsätze je Produkt aufzuaddieren.
Anstatt jeweils Anzahl und Einzelpreis zu multiplizieren und am Ende aufzuaddieren, multipliziert das berechnete Feld die jeweiligen Summen
Um zum richtigen Ergebnis zu kommen, muss ich in der Datenbasis eine neue Spalte hinzufügen und dort zeilenweise den Umsatz berechnen. Dieses Feld verwende ich anschließend in der Pivot-Tabelle:
Nur mit Hilfe einer zusätzlichen Spalte in der Datenbasis lässt sich der Umsatz korrekt ermitteln
So geht’s ohne Hilfsspalte: Mit einem DAX-Measure den Umsatz korrekt berechnen
Bei der oben gezeigten Methode füge ich der Bestelltabelle überflüssige Daten hinzu, weil mit der Anzahl und dem Einzelpreis bereits alle notwendigen Informationen vorliegen.
Darüber hinaus stellt sich die Frage, ob überhaupt der Umsatz für jede Zeile der Tabelle gebraucht wird? Schließlich interessiert doch nur der aggregierte Umsatz, z. B. nach Monat, Kalenderjahr oder Warengruppe.
Mit einem DAX-Measure lässt sich der Umsatz auch ohne Hilfsspalte berechnen.
Das entscheidende kleine Häkchen: So klappt der Zugriff auf DAX-Formeln
Um DAX-Measures verwenden zu können, füge ich beim Erstellen der Pivot-Tabelle die Daten dem Datenmodell hinzu.
Um die leistungsfähige Formelsprache DAX nutzen zu können, müssen sich die Daten im Datenmodell befinden
In 2 Schritten das Measure erstellen
In der PivotTable-Feldliste wähle ich per Rechtsklick auf den Tabellennamen im Kontextmenü Measure hinzufügen.
Schnell und einfach lässt sich der Pivot-Tabelle per Rechtsklick ein Measure hinzufügen
Im Dialogfeld Measure gebe ich Speicherort (1) und Name (2) des Measures, die DAX-Formel (3) zur Umsatzberechnung sowie das Zahlenformat (4) ein.
Mit Hilfe der SUMX-Formel wird der Umsatz korrekt berechnet
Sicher ist sicher: Die Formel auf Fehler prüfen lassen
Um sicherzugehen, dass mir bei der Formeleingabe kein Fehler unterlaufen ist, klicke ich auf DAX-Formel überprüfen (5).
Während IntelliSense das Semikolon als Listentrennzeichen akzeptiert, verlangt die Syntaxprüfung das in den USA gängige Komma
Darum lehnt Excel die korrekte Formel ab
Bei der Formeleingabe habe ich natürlich das in Windows festgelegte Semikolon als Trennzeichen für die Parameter verwendet. Genauso wie es IntelliSense von mir verlangt hat. Die Syntaxprüfung erwartet jedoch das in den USA übliche Komma.
Ersetze ich das Semikolon durch ein Komma, akzeptiert Excel meine DAX-Formel. Hoffentlich behebt Microsoft diesen Bug schnellstmöglich im Rahmen eines der nächsten Updates.
Mit dem Komma als Listentrennzeichen wird die Formel als korrekt akzeptiert
Die Werkzeuge für Power BI auch live kennenlernen
Hier noch ein Tipp für alle, die ihr Know-how zum Umgang mit Power Pivot und Power Query strukturiert und anhand praxisnaher Beispiele auf- und ausbauen wollen: Besuchen Sie meinen Workshop Zeitsparende Profi-Lösungen mit Power Query & Power Pivot oder buchen Sie ein Inhouse-Training.
Die nächsten Termine meiner offenen Trainings finden Sie hier.