Projekte auswerten mit Power Query, Power Pivot, Datenmodell und DAX Wenn Pivot nicht ausreicht - die BI-Tools von Excel im Praxiseinsatz

Teil 2:
Pivot-Auswertungen mit DAX-Measures erweitern
Projekte mit Power Query und Power Pivot auswerten - Teil 2

Wer Projektdaten mit Pivot auswerten möchte, stößt oft an Grenzen. Erfahren Sie in Teil 2 der Artikelserie, wie Sie mit DAX-Measures die Pivot-Feldliste erweitern und bedarfsgerechte Kennzahlen über mehrere Tabellen hinweg berechnen.

Management Summary

Projekte auswerten mit Power Query, Power Pivot, Datenmodell und DAX Wenn Pivot nicht ausreicht - die BI-Tools von Excel im Praxiseinsatz

Teil 2:
Pivot-Auswertungen mit DAX-Measures erweitern
Projekte mit Power Query und Power Pivot auswerten - Teil 2

Wer Projektdaten mit Pivot auswerten möchte, stößt oft an Grenzen. Erfahren Sie in Teil 2 der Artikelserie, wie Sie mit DAX-Measures die Pivot-Feldliste erweitern und bedarfsgerechte Kennzahlen über mehrere Tabellen hinweg berechnen.

Management Summary

In diesem Beitrag erfahren Sie anhand eines Praxisbeispiels, wie Sie verteilte Projektdaten mit Hilfe der Excel-Tools Power Query und Power Pivot auswerten und mit wichtigen Kennzahlen ergänzen. Dies ermöglicht eine differenzierte Analyse auch dann, wenn die Standardfunktionen in Pivot an Grenzen stoßen – z.B. bei unstrukturierten Daten aus unterschiedlichen Quellen. 

Während der erste Teil dieser dreiteiligen Artikelfolge beschreibt, wie Sie die Daten zusammenführen und zur gemeinsamen Auswertung in das Excel-Datenmodell laden, geht es in diesem zweiten Teil darum, wie Sie bedarfsgerechte Kennzahlen über mehrere Tabellen hinweg berechnen. Dabei kommen die Formelsprache DAX sowie sogenannte "Measures" zum Einsatz – das sind vordefinierte Berechnungen auf Basis von DAX (siehe Kasten).

Bild 1: Nachdem zunächst die Daten in drei Schritten aufbereitet wurden (siehe erster Teil der Artikelfolge), geht es im vierten Schritt darum, mit Hilfe von DAX-Measures wichtige Kennzahlen zu gewinnen
Bild 1: Nachdem zunächst die Daten in drei Schritten aufbereitet wurden (siehe erster Teil der Artikelfolge), geht es im vierten Schritt darum, mit Hilfe von DAX-Measures wichtige Kennzahlen zu gewinnen

Die Formelsprache DAX und vordefinierte Berechnungen (DAX-Measures)

Die Formelsprache DAX enthält eine Bibliothek mit über 250 Funktionen und zahlreichen Operatoren. DAX steht dabei für Data Analysis eXpressions. DAX-Formeln sind Excel-Formeln sehr ähnlich und weisen viele gleiche Funktionen auf wie z.B. "SUM", "AVERAGE" oder "LEFT". Eine Referenz der DAX-Funktionen finden Sie unter https://docs.microsoft.com/de-de/dax/.

DAX-Measures – also vordefinierte Berechnungen, die auf DAX beruhen – erstellt Excel automatisch, wenn Sie in einer Pivot-Tabelle auf Basis des Datenmodells Felder in den Wertebereich der Feldliste ziehen. Solche sogenannten "impliziten Measures" sind einfach zu verwenden, lassen sich nicht ändern, ihr Funktionsumfang ist begrenzt und das gewünschte Zahlenformat muss in der Pivot-Tabelle nachträglich festgelegt werden. Es lohnt sich daher, Measures selbst zu erstellen – sogenannte "explizite Measures". Sie sind wesentlich vielseitiger, stehen in jeder Pivot-Tabelle der Arbeitsmappe, die auf dem Datenmodell beruht, zur Verfügung und können verwendet werden, um aussagefähige Kennzahlen zu berechnen. 

Auswertung 1: Plan- und Ist-Kosten für jedes Projekt gegenüberstellen

Berechnung mit impliziten Measures

Teil 1 dieses Beitrags zeigt bereits, wie Sie die Plan- und Ist-Kosten aller Projekte einander gegenüberstellen (siehe dort "Beispiel 1: Plan- und Ist-Kosten für jedes Projekt gegenüberstellen"). Folgende Felder aus der Feldliste benötigen Sie zum Erstellen der Pivot-Tabelle:

  • Das Feld Projektbezeichnung aus der Tabelle Projektdaten, das Sie in den Bereich Zeilen ziehen.
  • Die beiden Spalten Kosten aus den Tabellen IstKosten und PlanKosten, die Sie in den Wertebereich ziehen. Die Kosten werden dadurch automatisch summiert. 
  • Abschließend passen Sie die Spaltenüberschrift der Kostenspalte an und legen das Zahlenformat für diese Wertebereiche fest.

Dadurch haben Sie zwei implizite Measures erstellt – und zwar "Plan-Kosten" sowie "Ist-Kosten". Die zugehörigen DAX-Formeln zeigt Excel an, indem Sie: 

  • In das Power Pivot-Fenster wechseln (PowerPivot / Verwalten), dort die Registerkarte Erweitert wählen und dann auf die Schaltfläche Implizite Measures anzeigen klicken.
  • Anschließend wählen Sie eines der beiden Blätter "IstKosten" oder "PlanKosten" (am unteren Fensterrand) und klicken die Zelle "Summe …" an, die unterhalb der Kostenwerte erscheint.

Sie sehen jetzt oben in der Bearbeitungsleiste die zugrunde liegende DAX-Formel, die zwar angezeigt wird, die Sie jedoch nicht ändern können (Bild 2): Summe von Kosten 2:= SUM('Plankosten'[Kosten])

Wer in Excel die strukturierten Verweise in "intelligenten" Tabellen kennt, wird feststellen, dass die Art des Referenzierens nach dem Prinzip "Tabellenname[Spaltenname]" auch in DAX verwendet wird. 

Bild 2: Über die Registerkarte Erweitert im Power-Pivot-Fenster werden implizite Measures zwar angezeigt, können aber nicht verändert werden
Bild 2: Über die Registerkarte Erweitert im Power-Pivot-Fenster werden implizite Measures zwar angezeigt, können aber nicht verändert werden

Berechnung mit expliziten Measures

Um die Plan- und Ist-Kosten mit expliziten Measures zu berechnen, gehen Sie wie folgt vor:

  • Klicken Sie im Register Einfügen auf PivotTable. 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.
  • Klicken Sie in der Symbolleiste für den Schnellzugriff (vgl. Teil 1 des Beitrags) auf den Befehl Neues Measure (Bild 3). (Alternativ finden Sie den Befehl unter Power Pivot / Berechnungen / Measures / Neues Measure)
Bild 3: Sparen Sie Zeit und rufen Sie den Befehl Neues Measure einfach über die Symbolleiste für den Schnellzugriff auf, wie in Teil 1 beschrieben 
Bild 3: Sparen Sie Zeit und rufen Sie den Befehl Neues Measure einfach über die Symbolleiste für den Schnellzugriff auf, wie in Teil 1 beschrieben 
  • Wählen Sie zunächst die Tabelle, zu der das Measure gehören soll (Bild 4 [1]) 
  • Tragen Sie anschließend einen sprechenden Namen für das neue Measure ein (Bild 4 [2]). 
  • Geben Sie nun die Formel mit Hilfe der IntelliSense-Liste ein (Bild 4 [3]). In der Formel bilden Sie mit Hilfe der Funktion SUM die Summe über die Spalte IstKosten[Kosten]. Die fertige DAX-Formel sieht wie folgt aus: =SUM(IstKosten[Kosten]).
  • Legen Sie abschließend noch das Zahlenformat fest (Bild 4 [4]).
  • Erstellen Sie auf die gleiche Weise ein weiteres explizites Measure für die Plan-Kosten mit der Bezeichnung Plan-Kosten und der Formel: =SUM(PlanKosten[Kosten]).
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.
0 Kommentare anzeigen & selbst mitreden!
Gesamt
Bewertungen 0
Kommentare 0

Fortsetzungen des Fachartikels

Teil 1:
Verteilte Daten automatisiert importieren und aufbereiten

Verteilte Daten, verschieden Granularität, falsche Struktur: Wer Projektdaten in Excel per Pivot auswerten möchte, stößt oft an Grenzen. Doch mit Power Query und Power Pivot stellt Excel BI-Funktionen zur Verfügung, die Pivot flexibler und …

Teil 3:
Mit DAX-Measures Kennzahlen ermitteln, die über den Standard hinausgehen

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.

Das könnte Sie auch interessieren

Um Daten für eine Excel-Auswertung zusammenzuführen, ist oft zeitaufwendige Klick-Arbeit notwendig – vor allem, wenn Sie Daten manuell übertragen müssen. Schneller gelingt das in vielen Fällen, wenn Sie die Datenabfrage automatisieren.
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

Sie arbeiten gerne mit Pivot-Tabellen, ärgern sich aber über die starre Struktur der Tabellen? Dann verwenden Sie stattdessen doch Cube-Funktionen!

Unlocked Content