Grohrock.consulting

Excel, Datenbanken und mehr

Schlagwort: PowerPivot

IBCS-Diagramme mit Excel und PowerPivot

Einer meiner persönlichen Favoriten unter den YouTube-Videos ist bis heute Clip 6: Simplify von Rolf Hichert. Lustig und informativ ist es allemal, aber am Ende bleibt natürlich die Frage, wie man solche Diagramme in der Praxis effizient erstellen kann. Meine Prämisse war wie immer, auf die Verwendung von VB zu verzichten.

Das Ziel war die Erstellung eines Diagramms im folgenden Stil:

Diagramm im IBCS-Stil

Dargestellt werden sollten die letzten 5 KW, dazu die aktuelle und die folgende KW, jeweils klar erkennbar am Stil. Ins Rennen gegangen bin ich mit zwei verschiedenen Pivot-Diagramm; das eine erstellt über PowerPivot und das andere über den Standardweg.

Die dazugehörige Datei kann und sollte hier heruntergeladen werden: verkäufe.xlsx

Der Reiter „Daten“ enthält die aktuelle Datengrundlage beider Diagramme, der Reiter „Daten (neu)“ enthält die Daten, mit denen wir unsere Diagramme aktualisieren wollen.

Ich lade nun jeden dazu ein, die neuen Daten zu kopieren, diese in die alte Tabelle einzufügen und dann einen beherzten Klick auf  „Daten > alle aktualisieren“ zu wagen. Daraufhin sollten sich die Diagramme wie folgt verändern.

Der Unterschied ist ein kleiner Trick, der nur bei einem Blick unter die Haube auffällt. Das Standarddiagramm besteht aus einer Berechnung, weshalb die Formatierung bei allen Balken gleich ist. Das PowerPivot-Diagramm besteht aus drei Berechnungen, weshalb die Formatierung sich auch individuell anpassen lässt. Ein Klick auf „PowerPivot > Verwalten“ zeigt, wie es funktioniert. Vergangenheit, Gegenwart und Zukunft haben jeweils eine eigene Berechnung und weil diese per se keine überschneidenden Zeiträume haben können, wirken sie im Diagramm wie aus einem Guss. Einziger Nachteil: die Legende sollte man tunlichst ausblenden.

Verkäufe (vergangen) :=CALCULATE(SUM('verkäufe'[Verkäufe]);'verkäufe'[KWdiff]<0)
Verkäufe (aktuell) :=CALCULATE(SUM('verkäufe'[Verkäufe]);'verkäufe'[KWdiff]=0)
Verkäufe (Prognose) :=CALCULATE(SUM('verkäufe'[Verkäufe]);'verkäufe'[KWdiff]>0)

Im Prinzip also ganz einfach und noch viel praktischer, wenn die Daten automatisiert von einer anderen Datenquelle gezogen werden. Fairerweise muss man natürlich dazusagen, dass sich die Standardlösung optimieren lässt, indem man zum Beispiel das Design als Vorlage speichert und dann nach jeder Aktualisierung wieder überstülpt, aber ganze so bequem wie die PowerPivot-Variante wird es dadurch trotzdem nicht.

Ich will auch nicht ausschließen, dass ich andere Methoden außen vor gelassen habe, von daher gerne kommentieren, falls es noch andere Möglichkeiten gibt, die das Leben erleichtern.

Power Pivot: Gruppieren nach Monat / Quartal / Halbjahr

Wer die Funktion „Gruppieren“ aus normalen Pivottabellen in Excel kennt, wird sie bei Power Pivot zunächst vermissen. Auch bei korrekter Formatierung als Datum lassen sich Werte nicht nach Monat oder Quartal gruppieren. Abhilfe schafft hier die Einführung von Beziehungen zwischen einem Datum und der dazugehörigen Gruppe.

Kurz zu den Ausgangsdaten. Das Beispiel enthält Verkäufe mit Summen und Datumsangaben. Denkbare Analysen wären nun die Preisentwicklung pro Monat oder die Einnahmen pro Quartal.

Die Beispieldatei kann hier heruntergeladen werden: Verkäufe Gruppieren

Schritt 1: Zuordnungstabelle erstellen

Das kleinste Gemeinsam, welches wir zuordnen wollen, ist der Monat. Dementsprechend müssen wir eine Tabelle erstellen, in welcher jeder Monat einem Monatsnamen, Quartal und Halbjahr zugeordnet wird. In meinem Fall sind das folgende Spalten:

  • Monat (Nummer)
  • MonatKurz
  • MonatLang
  • Quartal
  • Halbjahr

Schritt 2: Spalte Monat hinzufügen

Ich setze voraus, dass die beiden Tabellen spätestens jetzt über „PowerPivot > Zu Datenmodell hinzufügen“ ins Datenmodell aufgenommen wurden. Zunächst brauchen wir also für die Rohdaten eine neue Spalte namens „Monat“, welche sich aus folgender Formel errechnet:

=MONTH([Datum])

Wer es gerne ganz sauber mag, der macht nun einen Rechtsklick auf den Spaltennamen und wählt die Option „Aus Clienttools ausblenden“. Dieser Schritt bewirkt, dass die Spalte nachher nicht als verfügbare Spalte für die Pivottabelle angezeigt wird.

Schritt 3: Zuordnungstabelle vorbereiten

Die Zuordnungstabelle ist an sich schon fertig, aber eine Gemeinheit versteckt sich noch darin. Alphabetisch kommt Februar vor Januar, aber selbstverständlich wollen wir die Monate korrekt sortieren. Passend dazu gibt es die Funktion „Nach Spalte sortieren“, die für jede Spalte ausgewählt werden muss, die nicht automatisch richtig sortiert werden. In unserem Fall sind das MonatKurz und MonatLang.

Nach Spalte sortieren

Schritt 4: Verknüpfung herstellen

Das Erstellen der Verknüpfung lässt sich am einfachsten in der Diagrammsicht bewerkstelligen. Der Button zum Wechseln befindet sich ganz rechts unten im Power Pivot-Fenster. In dieser Ansicht werden die verfügbaren Tabellen mit allen Spalten, berechneten Werten und KPI angezeigt. Zur Verknüpfung genügt es, die Spalte „Verkäufe.Monat“ per Drag & Drop auf die Spalte „Zuordnung_Monate.Monat“ zu ziehen.

Verknüpfung erstellen

An dieser Stelle direkt ein Hinweis zu Verknüpfungen. Power Pivot kennt von sich aus nur 1:n-Verknüpfungen. Das heißt ein Wert in einer Spalte darf auf der einen Seite genau ein Mal vorkommen, in der anderen Spalte aber n Mal.

In unserem Beispiel existiert der Monat 1 in „Zuordnung_Monate.Monat“ genau ein Mal, in der Spalte „Verkäufe.Monat“ hingegen kann er beliebig oft vorkommen.

Nutzung in einer Pivottabelle

Zum Einfügen der Pivottabelle haben wir zwei verschiedene Möglichkeiten. Die direkteste ist über das Power Pivot-Fenster über „Home > PivotTable“. Ich persönlich möchte aber nicht jedes Mal dieses Fenster öffnen, wenn ich eine neue Tabelle in mein Datenblatt einfüge. Meiner Meinung nach wesentlich einfacher geht es direkt im Excel-Fenster über „Einfügen > PivotTable“. Dort je nach Wunsch die Position wählen und „Externe Datenquelle verwenden“ wählen. Als Verbindung wählen wir dann „Tabellen im Datenmodell der Arbeitsmappe“.

Vorhandene Verbindungen

Nun wird wie gewohnt eine Pivottabelle eingefügt, in der man Zeilen und Spalten je nach Wunsch einfügen kann. Als kleines Beispiel habe ich die Produkte in die Spalten und die Quartale und Monate in die Zeilen gepackt. Als Wert habe ich einfach die Summe der Gesamtpreise genommen.

PivotTable

Also alles im Kern ganz einfach und das Ergebnis besticht durch sauber aufteilbare Daten, ohne dass die Originaltabelle um die gesamte Palette an möglichen Datumswerten erweitert werden muss. Wie man sehen kann, taucht die Spalte „Verkäufe.Monat“ nicht auf, da ich sie vorher für Clienttools ausgeblendet habe. Für mich ist das vor allem eine Frage der Nutzbarkeit. Alle Spalten und Werte, die der Endnutzer der Tabelle nicht braucht, sollten zur Vermeidung von Unklarheiten einfach von vornherein ausgeblendet werden.

Ach ja, netter Nebeneffekt: Selbstverständlich wird die Tabelle mit den Zuordnungen von mir regelmäßig wiederverwendet und das spart in der Tat eine Menge Zeit.

Fragen? Anregungen? Einfach eine Nachricht hinterlassen und ich schaue, was ich machen kann.

Läuft mit WordPress & Theme erstellt von Anders Norén