Um zu ermitteln, aus welchen Daten eine Summe, ein Durchschnitt oder eine andere Berechnung entstanden ist, benötigt man alle Datensätze, die zu einem Ergebnis geführt haben - ein Drill-down. Das geht auch mit Excel Power Pivot.

Die Aufgabe

Du arbeitest in einer Firma, die Mehl herstellt und vertreibt. Aus dem Bestellsystem erhälst du Daten in zwei Tabellen: DimArtikel (der Artikelstamm) und FaktAuftrag (die Auftragspositionen).

Beispieldaten für Drill-down in Excel Power Pivot

Beide Tabellen impotierst du für die Auswertung in das Excel Datenmodell und erstellst eine Beziehung über die Felder DimArtikel[ID] und FaktAuftrag[ArtikelID].

Datenmodell für Drill-down in Excel Power Pivot

Eine einfache Datenanalyse

Aus diesen Daten kannst du leicht eine Pivot-Tabelle generieren, um beispielsweise die Absatzmengen kumuliert pro Artikel darzustellen. Man kann einfach mit impliziten Measures arbeiten (du ziehst das Feld FaktArtikel[Menge]) in die Werte der Pivot-Tabelle oder du legst ein explizites Measure in der Tabelle FaktAuftrag an:

Für dieses Beispiel habe ich die Variante Measure verwendet. Die Pivot-Tabelle sieht wie folgt aus:

Pivot-Tabelle mit Aggregation über Menge

Der Drill down

Bei der Analyse merkst du, dass der Wert für Weizenmehl 1050 seltsam ist. Um herauszufinden, wie der Wert zu Stande kam, benötigst du alle Zeilen, die zu dem Wert geführt haben. Dazu kann man in das Datenmodell wechseln und über die Schnellfilter die Auftragszeilen ermitteln. Oder man klickt doppelt mit der Maus auf den Wert 12.

Der Doppelklick legt in Excel ein neues Tabellenblatt an. In diesem Tabellenblatt legt Excel eine Tabelle mit allen Spalten der Tabelle FaktAuftrag sowie allen Zeilen mit Artikel Weizenmehl 1050 an.

Drill down Ergebnis

Zeilenanzahl

Wenn du einen Drill down durchführst wird dir immer in der ersten Zeile mitgeteilt, dass die Anzahl der Zeilen auf 1.000 begrenzt wurde. Das ist auch gut so. Stell dir vor du hast 5 Millionen Auftragszeilen und klickst versehentlich in der Pivot-Tabelle doppelt auf ein Feld. Dann versucht Excel alle Zeilen des gewählten Artikels in ein neues Arbeitsblatt zu exportieren. Klar: bei mehr als einer Million Zeilen streikt Excel natürlich ohnehin.

Manchmal sind 1.000 Zeilen aber zu wenig. Die Anzahl läßt sich aber leicht erhöhen. Diese Information ist in der Verbindung zwischen Excel und dem Datenmodell hinterlegt. In Excel im Menü Daten findest du den Eintrag Abfragen und Verbindungen.

Abfragen und Verbindungen in Excel

Es öffnet sich ein sogenanntes FlyOut auf der rechten Seite. Dort findest du alle Power Query Abfragen - aber auch alle anderen Verbindungen, wenn du oben den Eintrag Verbindungen auswählst.

FlyOut Abfragen und Verbindungen in Excel

Dort befindet sich auch die interne Datenverbindung zwischen Excel und dem Datenmodell mit dem Namen ThisWorkbookDataModel. Über das Kontextmenü (rechte Maustaste) gelangt man in den Eigenschaftsdialog der Verbindung.

Eigenschaftsdialog der Datenmodell-Datenverbindung in Excel

Im unteren Bereich findest du die Angabe Maximale Anzahl abzurufender Datensätze bei OLAP-Drillthrough. Diese kannst du hier verändern. Die Anzahl gilt nur für die Verbindung in deiner aktuellen Arbeitsmappe.

Welche Daten erscheinen beim für Drill down

Legen wir noch eine Measure an. Und zwar genau eine inhaltlich Gleiche. Nur legen wir die Measure in die Tabelle DimArtikel:

In meinen Trainings erzähle ich, dass es unerheblich ist in welcher Tabelle eine Measure abgelegt wird. Die Zuordnung sollte hautpsächlich sinnvoll erfolgen. Das stimmt nicht ganz. Wenn wir die neue Measure DimArtikelSumMenge ebenfalls in die Pivot-Tabelle aufnehmen, ist das Ergebnis in der Pivot-Tabelle identisch mit dem bisherigen Measure:

Pivot Tabelle mit Measure auf Dimensionstabelle

Aber wenn du jetzt auf das Ergebnis 12 des neuen Measures einen Doppelklick ausführst, ist das Ergebnis des Drill downs ein anders:

Drill down Ergebnis mit Measure auf Dimensionstabelle

Es werden jetzt die Felder und Zeilen von DimArtikel dargestellt.

Das bedeutet, dass bei einem Drill down immer die Daten der Tabelle angezeigt werden, in der sich das Measure befindet.

Drill down Tables

Für solche Fälle oder um generell die Spalten zu bestimmen, die bei einem Drill down angezeigt werden, kann man mit Drill down Tabellen arbeiten. Unser FaktAuftrag-Tabelle hat beispielsweise noch eine weitere Spalte, einen sogenannten Global Unique Identifier, also einen weltweit eindeutigen Schlüssel:

Beispieldaten für Drill down mit GUID

Die GUID dürfen wir nicht generell löschen, weil die noch für andere Measures relevant ist. Bei einem Drill down würde diese Spalte allerdings immer auftauchen - und stören. Du kannst aber einfach eine zweite Tabelle FaktAuftragDrillDown anlegen, die alle Felder außer der GUID enthält. Und dort legst du dein Measure an.

Datenmodell mit Drill down Table

Das ist ein wenig ein Taschenspielertrick. Er verdoppelt insbesondere das Datenvolumen der Faktentabellen. Aber es führt zum Ziel.

Measure-Tabelle - und der Nachteil beim Drill down

Häufig verwende ich eine Measure-Tabelle. Dabei handelt es sich um eine Tabelle, die keine Daten enthält und beispielsweise MyMeasures heißt. Dort lege ich alle Measures an. Damit behalte ich die Übersicht. Außerdem hat eine solche Tabelle eine weiteren Vorteil: Wenn in Power Query eine Tabelle umbenannt wird, wird die Tabelle im Datenmodell gelöscht und erneut angelegt. Damit werden alle Measures gelöscht, die zu dieser Tabelle gehören. Über den Weg Measure-Tabelle umgehe ich das Problem, da die Measures dort immer bestehen beiben.

Für einen Drill down ist das aber ein Nachteil - weil das klappt dann nicht mehr.

Du brauchst Unterstützung?

Für einen schnellen Start bieten wir Trainings und Workshops an. In unseren Trainings schulen wir deine Kollegen und dich, in unseren Workshops bauen wir Reports direkt gemeinsam mit dir auf. Du entscheidest, was besser für dich passt. Und auch danach lassen wir euch nicht allein und bieten und als Sparringspartner an.

Für einen generellen Einstieg in Excel Power Pivot schau bei unserem Online Kurs vorbei.

Jederzeit kannst du uns direkt per E-Mail kontaktieren oder ruf einfach an!