Projektauswertungen mit Power Query, Power Pivot, Datenmodell und DAX Wenn Pivot nicht ausreicht: Die BI-Tools von Excel im Praxiseinsatz

Teil 3: Mit DAX-Measures Kennzahlen ermitteln, die über den Standard hinausgehen
Projekte mit Power Query und Power Pivot auswerten - Teil 3

Wer Projektdaten mit Pivot auswerten möchte, stößt oft an Grenzen. Erfahren Sie in Teil 3 der Artikelserie, wie Sie mit expliziten Measures Auswertungen erstellen, die mit Standardberechnungen nicht möglich wären.

Management Summary

Projektauswertungen mit Power Query, Power Pivot, Datenmodell und DAX Wenn Pivot nicht ausreicht: Die BI-Tools von Excel im Praxiseinsatz

Teil 3: Mit DAX-Measures Kennzahlen ermitteln, die über den Standard hinausgehen
Projekte mit Power Query und Power Pivot auswerten - Teil 3

Wer Projektdaten mit Pivot auswerten möchte, stößt oft an Grenzen. Erfahren Sie in Teil 3 der Artikelserie, wie Sie mit expliziten Measures Auswertungen erstellen, die mit Standardberechnungen nicht möglich wären.

Management Summary

Wir empfehlen zum Thema Berichten
Methoden des modernen Portfoliomanagements

Die richtigen Dinge tun – für mehr Fokus, Agilität und Produktivität im Unternehmen! In unserem E-Learning-Seminar lernen Sie in nur 4 Workshops, wie Sie Ihr Portfolio mit modernen Methoden organisieren und ausbauen.  Mehr Infos

In Teil 2 konnten Sie anhand von zwei Beispielen sehen, wie Sie mit Hilfe der Formelsprache DAX vordefinierte Berechnungen – sog. explizite Measures – in Power Pivot erstellen. Damit lassen sich zusätzliche Kennzahlen berechnen, die in jeder auf dem Datenmodell basierenden Pivot-Tabelle wiederverwendet werden können.

Erfahren Sie in diesem dritten und abschließenden Teil, wie Sie explizite Measures zur Berechnung von Kennzahlen einsetzen, die über Standard-Auswertungen in Pivot hinausgehen. Ermitteln Sie z.B. die monatsweise Kumulation von Kosten über Jahresgrenzen hinweg oder stellen Sie Teilmengen wie externe und sonstige Kosten für die verschiedenen Projekte gegenüber.

Auswertung 1: Kumulierte Plan- und Ist-Kosten nach Monat ermitteln

Sie möchten wissen, wie sich die Ist- und Plan-Kosten bis zu einem bestimmten Monat entwickelt haben – und zwar kumuliert und über den Jahreswechsel hinaus? In einer herkömmlichen Pivot-Tabelle ließen sich die kumulierten Werte zwar mit der Einstellung Anzeigen alsLaufende Summe berechnen, eine jahresübergreifende Berechnung, wie in Bild 1 gezeigt, wäre allerdings nicht möglich

Bild 1: Vorschau auf die fertige Pivot-Tabelle zu den monatsweise kumulierten Kosten über Jahresgrenzen hinweg
Bild 1: Vorschau auf die fertige Pivot-Tabelle zu den monatsweise kumulierten Kosten über Jahresgrenzen hinweg

Für die in Bild 1 gezeigte Lösung kumulieren Sie zunächst die Plan- und Ist-Kosten über die Monate, indem Sie auf die bereits erstellten Measures Plan-Kosten und Ist-Kosten zurückgreifen. Allerdings sollen nicht nur die Kosten des aktuellen Monats dargestellt werden, sondern auch die Kosten bis zum aktuellen Monat bzw. bis zum höchsten Tagesdatum in der Spalte Kalender[Datum] im aktuellen Filterkontext. Erfahren Sie, wie Sie dazu vorgehen. 

Bild 2: Die kumulierten Ist-Kosten werden mit Hilfe des bereits erstellten Measures Ist-Kosten ermittelt, indem der aktuelle Filterkontext – nur Januar 2022 – verändert wird zu bis Januar 2022
Bild 2: Die kumulierten Ist-Kosten werden mit Hilfe des bereits erstellten Measures Ist-Kosten ermittelt, indem der aktuelle Filterkontext – nur Januar 2022 – verändert wird zu bis Januar 2022

Filterkontext

Der Filterkontext definiert die Filter, die vor dem Ausführen eines Measures angewandt werden. In den Bildern 1 und 2 wird z.B. auf das Projekt BCM390 aus der Tabelle Projektdaten sowie auf das Jahr 2022 und den Monat Januar aus der Tabelle Kalender gefiltert.

Erst filtern, dann berechnen. So können mit einer Measure-Formel mehrere Ergebnisse generiert werden.

Schritt 1: Plan-/Ist-Kosten pro Projekt über die Monate kumulieren

  • Öffnen Sie die Beispieldatei “Projektdaten_Teil2_LOE.xslx" mit dem in Teil 1 und 2 erstellten Datenmodell, das bereits erste Measures enthält. Klicken Sie im Register Einfügen auf PivotTable und wählen Sie als Datenquelle Aus dem Datenmodell. Rechts erscheint die Feldliste, in der Sie nun Zugriff auf die Spalten aller im Datenmodell enthaltenen Tabellen haben.
  • Ziehen Sie aus der Tabelle Projektdaten die Spalte Projekt, anschließend aus der Tabelle Kalender die Spalten Jahr und Monatsnamein den Bereich Zeilen.
  • Erstellen Sie ein neues Measure Ist-Kosten_kum für die kumulierten Ist-Kosten, indem Sie auf das entsprechende Symbol in der Symbolleiste für den Schnellzugriff klicken (siehe Teil 1) oder alternativ den Befehl Power Pivot / Berechnungen / Measures / Neues Measure wählen. Geben Sie im Dialogfeld als Tabellenname IstKosten ein, als Measurename Ist-Kosten_kum (Bild 3).
  • Geben Sie die Formel ein wie in Bild 4 oben gezeigt. 
  • Erstellen Sie auf die gleiche Weise ein Measure für die Plan-Kosten_kum mit der in Bild 4 unten gezeigten Berechnungsformel.
  • Aktivieren Sie für beide Measures das Häkchen rechts in der Feldliste.
Bild 3: Die kumulierten Ist-Kosten werden über die Ist-Kosten ermittelt, indem der aktuelle Filterkontext verändert wird
Bild 3: Die kumulierten Ist-Kosten werden über die Ist-Kosten ermittelt, indem der aktuelle Filterkontext verändert wird
Bild 4: Im Detail: Die Berechnungen in den beiden Measures Ist-Kosten_kum und Plan-Kosten_kum
Bild 4: Im Detail: Die Berechnungen in den beiden Measures Ist-Kosten_kum und Plan-Kosten_kum

Die Funktionsweise der Formeln

  • Mit der Funktion CALCULATE verändern Sie den in der Pivot-Tabelle vorhandenen Filterkontext.
  • Die Funktion FILTER verwenden Sie, um die Berechnung der Ist-Kosten für eine bestimmte Teilmenge auszuführen. Diese Teilmenge legen Sie in der Funktion FILTER mit folgenden zwei Argumenten fest: 
    ALL(Kalender[Datum]); Kalender[Datum] <= MAX(Kalender[Datum]).
  • Die Funktion ALL entfernt zunächst alle Filter auf der Kalender-Datumsspalte und begrenzt die Datumsangaben nach oben bis zum größten aktuellen Datum im Filterkontext. Das geschieht durch das Argument Kalender[Datum] <= MAX(Kalender[Datum])
  • Damit wird der Berechnungszeitraum bis einschließlich Ende Januar 2022 für die in Bild 2 dargestellte Zelle festgelegt.

Bild 5 zeigt die daraus resultierende Pivot-Tabelle mit den beiden zusätzlichen Measures. 

Eine kleine Korrektur

Da die Plan-Kosten im Beispielprojekt zwar für alle Monate der Projektlaufzeit festgelegt sind, die Ist-Kosten im laufenden Projekt jedoch nur für einen Teil dieses Zeitraums zur Verfügung stehen, erscheinen beim Kumulieren auch Werte für Monate ohne Ist-Kosten (Bild 5, grau markiert). Diese sind für die Auswertung nicht relevant, deshalb soll die Pivot-Tabelle nur die Kosten bis zum letzten Monat mit vorhandenen Ist-Kosten anzeigen. Diese Optimierung führen Sie in Schritt 2 durch.

Bild 5: Links werden die kumulierten Plan- und Ist-Kosten auch für Monate dargestellt, in denen keine Ist-Kosten vorliegen – hier Juli bis Dezember 2022 – und rechts nur für die Monate bis zu den letzten vorhandenen Ist-Kosten
Bild 5: Links werden die kumulierten Plan- und Ist-Kosten auch für Monate dargestellt, in denen keine Ist-Kosten vorliegen – hier Juli bis Dezember 2022 – und rechts nur für die Monate bis zu den letzten vorhandenen Ist-Kosten

Schritt 2: Die kumulierten Kosten nur für Monate bis zu den letzten vorhandenen Ist-Kosten berechnen

Um die Tabelle so zu optimieren, dass kumulierte Kosten nur für Monate mit Ist-Kosten berechnet werden, ermitteln Sie zuerst das höchste Ist-Datum mit Hilfe des Measures Ist-Datum_MaxAuf Basis der vorhandenen Measures Ist-Kosten_kum und Plan-Kosten_kum legen Sie dann neue Measures mit den Namen Ist-Kosten_kum_lim und Plan-Kosten_kum_lian, in denen die kumulierten Berechnungen durch Ist-Datum_Max limitiert werden. Gehen Sie zum Anlegen der drei Measures wie in den Bildern 6 bis 8 gezeigt vor.

Bild 6: Berechnung des größten Datums mit Ist-Kosten ungleich 0
Bild 6: Berechnung des größten Datums mit Ist-Kosten ungleich 0
Bild 7: Berechnung der kumulierten Ist-Kosten mit Limitierung
Bild 7: Berechnung der kumulierten Ist-Kosten mit Limitierung
Bild 8: Berechnung der kumulierten Plan-Kosten mit Limitierung
Bild 8: Berechnung der kumulierten Plan-Kosten mit Limitierung
DownloadDownload
Download ZIPDownload ZIP

Jetzt Feedback geben und mitdiskutieren!

Wir würden uns über Ihre Bewertung und/oder einen Kommentar freuen ‒ nur so können wir Ihnen in Zukunft noch bessere Inhalte liefern.

Diese Funktion steht nur eingeloggten Nutzern zur Verfügung.
1 Kommentare anzeigen & selbst mitreden!
Gesamt
Bewertungen 1
Kommentare 1

Das könnte Sie auch interessieren

Mit Excel-Datenschnitten in nur 2 Schritten zum interaktiven Info-Cockpit

Um Projektdaten auf das Wesentliche zu reduzieren, kommt oft die Pivot-Funktion zum Einsatz. Eine praktische Alternative sind Datenschnitte, mit denen Sie Tabellen mit nur einem Klick visuell filtern. So setzen Sie Datenschnitte optimal ein.

Vorschaubild

Sie wollen Ihre Pivot-Tabellen in Excel möglichst unkompliziert erstellen und das Maximum aus dem Informationsgehalt Ihrer Auswertungen herausholen? Dieter Schiecke gibt Ihnen 12 Tipps an die Hand, mit denen Sie sich Zeit und Nerven sparen.

Vorschaubild

Pivot-Tabellen sind das perfekte Tool, wenn es darum geht, umfangreiche Informationen zu unterschiedlichen Fragestellungen auszuwerten, ohne sich mit Formeln oder Filtern beschäftigen zu müssen.

Alle Kommentare (1)

Eduard
Chernavin

Eine sehr gute Einleitung für Fortgeschrittene. Allerdings trägt die Verwendung von englischen Begriffen bspw. "Measures", für welche eine gängige deutschsprachige Entsprechung (im ausgewählten Beispiel wäre "Berechnungen") gibt, einem besseren Verständnis nicht sonderlich bei.
Nur ein gutgemeinter Verbesserungsvorschlag!