In Excel 2016 liefern berechnete Felder endlich richtige Ergebnisse

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.

 

01_Tabelle_Bestellungen

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.

 

02_03_berechnetes_Feld_anlegen

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.

 

04_fehlerhafte_Pivot

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:

 

05_Hilfsspalte

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.

 

06_Dem_Datenmodell_hinzufuegen

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.

 

07_Measure_hinzufuegen_Feldliste

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.

 

08_Measure_Dialog

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).

 

09_Syntaxfehler

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.

 

10_Formel_fehlerfrei

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.

Bald als Video: Pivot-Berichte revolutionieren mit dem Excel Datenmodell

Wer Daten flexibel, kompakt und mit hoher Aussagekraft auswerten möchte, findet in Pivot-Tabellen und Pivot-Diagrammen die geeigneten Hilfsmittel.

 

Pivot-Auswertungen konnten bisher nur aus den Daten einer einzigen Tabelle erstellt werden. Sind dann weitere Tabellen hinzugekommen, wurden diese per SVERWEIS in die Datenbasis geholt. Das war umständlich und fehleranfällig. Zudem wurde Excel bei großen Datenmengen spürbar langsamer.

 

Mit den neuen Möglichkeiten der Datenanalyse in Excel beherrsche ich die Datenflut noch besser und entlocke den Daten meiner Kunden die wirklich wichtigen und entscheidungsrelevanten Informationen.

 

Die revolutionäre Neuerung seit Excel 2013: Das Datenmodell

 

Im Datenmodell führe ich Daten aus mehreren Quellen zusammen und verknüpfe die Tabellen miteinander wie in einer relationalen Datenbank. Das Datenmodell ist eine Sammlung von Tabellen, die miteinander in Beziehung stehen. Es stellt die Basis meiner Pivot-Tabellen und Pivot-Diagramme dar.

 

In meinen Excel-Seminaren stelle ich aber immer wieder fest, dass vielen das Datenmodell gänzlich unbekannt ist. Obwohl es seit Excel 2013 jedem zur Verfügung steht.

 

Grund genug für mich, ab heute bei Video2Brain in Graz ein entsprechendes Video-Training aufzuzeichnen!

 

Darum geht es in meinem neuen Video

 

Zuerst demonstriere ich, wie mir das Datenmodell den umständlichen SVERWEIS erspart und ich stattdessen mit nur einem Mausklick beliebig viele Tabellen als Datenbasis für meine Pivot-Berichte heranziehe.

 

Aufbauend darauf zeige ich, welche neuen Analysemöglichkeiten sich mit dem Datenmodell eröffnen: Von der neuen Aggregationsfunktion Diskrete Anzahl über die einfache Navigation mittels Schnelleinblick bis hin zum Erstellen leistungsfähiger berechneter Felder, sogenannter Measures, mit der Formelsprache DAX.

 

Schließlich werde ich im Video vormachen, wie sich das Datenmodell mit Hilfe des Spezialwerkzeugs Power Pivot noch verfeinern, die Benutzerfreundlichkeit von Analysen erhöhen, die Reporterstellung dank Hierarchien schneller erledigen und Pivot-Diagramme noch interaktiver machen lassen.

 

Und natürlich gibt es auch noch jede Menge Tipps & Tricks, die ich in meiner täglichen Arbeit mit Excel, dem Datenmodell und Power Pivot gesammelt habe.

Happy Birthday, Power BI User Group Frankfurt!

Die Freude war groß, als ich am 13. Januar 2016 die E-Mail von Microsoft erhielt und die Power BI User Group Frankfurt gründen durfte. Für mich ist es unglaublich: An ihrem 1. Geburtstag zählt die Gruppe bereits 123 Mitglieder…und wächst ständig weiter!

 

pug_approval_from_microsoft

Die offizielle Erlaubnis von Microsoft zur Gründung der Power BI User Group Frankfurt

 

Ich bin besonders stolz, dass die Teilnehmer nicht nur aus dem Rhein-Main-Gebiet kommen, sondern regelmäßig auch aus Köln, Aachen & Karlsruhe! Mit Power BI Desktop habe ich eine interaktive Auswertung der Gruppenmitglieder erstellt, die sogar von Samuel Lester in Rahmen seiner Blogreihe 12 Days of Dashboards vorgestellt wurde.

 

Auch beim nächsten Treffen am 24. Januar 2017 gibt es wieder interessante Fachvorträge. Außerdem starten wir mit unserem Gruppenprojekt! Wer beim Treffen dabei sein möchte, meldet sich hier kostenlos an.

 

Vereinfachte Verwaltung von Beziehungen im Datenmodell von Excel 2016

In der Office-Insider Version gibt’s seit gestern einige Neuerungen. Zwei gefallen mir dabei besonders gut:

 

1. Die Diagrammsicht des Datenmodells lässt sich nun bequem als Bild speichern.
2. Das Dialogfeld zum Bearbeiten von Beziehungen wurde deutlich verbessert.

 

Das sind die Vorteile des neuen Dialogfelds

Bisher musste ich im Dialogfeld die Spalten aus einer Dropdownliste auswählen. Ohne Kenntnis der jeweiligen Spalteninhalte war das oft schwierig und führte zu falschen Beziehungen.

 

Jetzt werden die Spalten mit Daten angezeigt. Das macht das Finden passender Spalten zum Kinderspiel. Einfach die jeweiligen Spalten anklicken. Fertig.

 

Beziehungen_verwalten_Excel_2016

Das Finden passender Spalten wird dank des neuen Dialogfelds deutlich einfacher, weil auch die Spalteninhalte angezeigt werden

 

Alle Neuerungen zu Power Pivot in Excel 2016 können Sie hier nachlesen.