Kostenstellen, Aufbauorganisationen, Stücklisten – Hierarchien kommen überall vor. Im Artikel Was macht eigentlich PATH haben wir gezeigt, was man mit der Funktion PATH machen kann. Eine aktuelle Anfrage eines Lesers nehmen wir zum Anlass, Power Pivot PATH() in diesem Artikel etwas tiefer zu betrachten.

Die Ausgangslage

Wir haben eine Mitarbeiterliste, in der die Mitarbeiter und deren Hierarchie dargestellt ist:

  • Holger (Abteilungsleiter)

    • Marc (Teamleiter)

      • Rolf (Verkäufer)
      • Martina (Verkäuferin)

    • Sabine (Teamleiterin)

      • Linda (Verkäuferin)
    • Manfred (Teamleiter)

  • Andreas (Abteilungsleiter)

    • Dietrich (Teamleiter)

In einer relationalen Struktur stellt man dies wie folgt dar:

IDParentIDName
1Holger (Chef)
21Marc (Teamleiter)
32Rolf (Verkäufer)
42Martina (Verkäuferin)
51Sabine (Teamleiterin)
65Linda (Verkäuferin)
71Manfred (Teamleiter)
8Andreas (Abteilungsleiter)
98Dietrich (Teamleiter)

Alle Mitarbeiter (auch die Abteilungsleiter und die Teamleiter) arbeiten im Verkauf und haben Umsätze getätigt (der Einfachheit halber immer 100 EUR):

IDMitarbeiterIDUmsatz
11100
22100
33100
49100
55100
64100
73100
87100
98100

Das Ziel

In einer Pivot-Tabelle sollen die Umsätze pro Mitarbeiter sowie die Umsätze pro Team und pro Abteilung verdichtet dargestellt werden.

Schritt 1: Berechnete Spalten mit PATH() anlegen

Im Artikel Was genau macht eigentlich PATH? wurden die Grundlagen bereits vorgestellt. Mit der PATH-Funktion erstellen wir uns in der Mitarbeiter-Tabelle eine Hierarchie-Spalte:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Auf Basis dieser Spalte können wir dann drei Spalten mit den jeweiligen Leveln erstellen:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos
  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos
  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Die Mitarbeiter-Tabelle sieht dann wie folgt aus: image

Schritt 2: Eine Hierarchie bilden...

...und eine Verknüpfung zu den Transaktionen erstellen: image

Schritt 3: eine Pivot-Tabelle erzeugen

image

Erweiterter Ansatz

Nicht schön sind die leeren Unterzweige. Diese werden aus den Zeilen generiert, bei denen Level 2 oder Level 3 leer sind. Trotzdem liefern diese Werte zurück, da der Mitarbeiter auf höchster Stufe (z.B. Andreas) 100 EUR Umsatz getätigt hat. Um diese los zu werden, fügen wir zunächst eine neue Spalte in die Mitarbeiter-Tabelle ein – die Länge des aktuellen Pfades:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Dann brauchen wir zwei weitere Measures: die aktuelle Tiefe des Baumes BrowseDepth und die maximale Tiefe im aktuellen Filter MaxHierarchyDepth:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos
  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Bei BrowseDepth machen wir uns zu Nutze, dass ein Boole'scher Wert addiert werden kann – True wird wie 1, False wie 0 interpretiert.

Den Umsatz unterdrücken bei leeren Subknoten

Damit erstellen wir eine eigene Summe, die BLANK() zurückgibt, wenn wir uns in einem “leeren” Zweig befinden:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

image

Die Erweiterung des erweiterten Ansatzes

Smile

Das löst das Problem, dass es keine leeren Unterzweige mehr gibt. Allerdings muss man auch genau hinschauen, wie die Zahlen zu Stande kommen. Bei Andreas steht beispielsweise 200, obwohl die Summe seiner Untergebenen (hier nur Dietrich) eigentlich nur 100 ergeben. Klar, Andreas hat selbst auch 100 EUR Umsatz erwirtschaftet. Rein logisch tritt er damit sowohl als Verkäufer, als auch als Chef auf – und so könnte man das auch darstellen. Dazu füllen wir zunächst die Level mit dem jeweils höheren Level auf, wenn das aktuelle Level leer ist – d.h. die Spalten Level 2 und Level 3 ändern wir auf:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos
  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Außerdem fügen wir eine Information ein, ob es sich um das Blatt im Hierarchie-Baum handelt – dabei wird für jeden Satz ermittelt, wie viele Sätze es gibt bei denen der aktuelle Satz Vater ist:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Damit ändert sich die Mitarbeiter-Tabelle: image Und den Umsatz berechnen wir dann so:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Damit erhalten wir: image Wenn also der Hauptknoten selbst Umsatz generiert hat, erscheint dieser gleichzeitig als Unterknoten.