Umfassende Minieinführung in Power Pivot

Power Pivot ist für die Analyse großer Datenbestände gedacht. In diesem Tutorial gebe ich eine kleine Einführung in Power Pivot.

Aktivierung von Power Pivot

Power Pivot ist seit Excel 2013 in jedem Excel dabei, konnte aber seit Excel 2010 zusätzlich installiert werden. Jedoch muss Power Pivot zuerst aktiviert werden. Dazu in den Backstagebereich gehen (Registerkarte (Menü) Datei anklicken). Dann ganz unten die Optionen aufrufen und dort wiederrum im linken Menü Add-Ins auswählen.

Ganz unten nun bei Verwalten die COM-Add-Ins auswählen und beherzt auf Los… klicken.

Im dann erscheinenden Fenster Microsoft Power Pivot für Excel auswählen und OK klicken.

Sie erhalten nun ein neues Menü/Registerkarte Namens Power Pivot in Ihrem Excel.

Beispielübung Power Pivot

Drei Tabellen:

Tabelle: tabFilialen

Tabelle: tabEinzelpreis

Die einzelnen Tabellen können Sie in der Registerkarte Tabellenentwurf ganz links bei den Eigenschaften mit einem Namen versehen. Ich empfehle Ihnen das zu tun, da dieser Name in PowerPivot übernommen wird und somit erheblich zum besseren Überblick beiträgt!

Je Tabelle: Power Pivot/Zu Datenmodell hinzufügen

Unter Power Pivot/Verwalten können Verbindungen zwischen den Tabellen hergestellt werden.

Berechnung in der Haupttabelle "tabFilialen":

Neue Spalte: =tabFilialen[Umsatz 2021]+tabFilialen[Umsatz 2022]

Neue Spalte: =tabFilialen[Gesamtmenge Obst]*RELATED(tabEP[EP])

Related ist eine DAX^[1]-Funktion mit der ich die Spalte einer anderen Tabelle ansprechen kann.

In Excel ein neues Tabellenblatt und dort Einfügen/PivotTable/Aus dem Datenmodell

Alternativ: Einfügen/PivotTable/Aus externer Quelle Dann den Button "Verbindung auswählen", den zweiten Tab anklicken "Tabellen" und dort oben "Tabellen im Datenmodell der Arbeitsmappe" à "Öffnen"

Tabelle Filialen ändern:

Measures

Measures sind berechnete Felder und stehen immer unter der Tabelle.

Wird in Excel eine Pivot Table erstellt, gibt es die Möglichkeit per Menü: PivotTable-Analyse/Berechnungen/Felder, Elemente und Gruppen à Berechnete Felder zu erstellen. Das geht nicht in der PivotTable die über Power Pivot erstellt wurde. Dort muss dieses mit einem Measure erzeugt werden.

Ein Measure erstellen

Unten drunter klicken und einen Measures erstellen. Die Formel ist fast identisch, wird aber nicht in der Spalte berechnet, sondern unter der Tabelle.

Umsatz Obst:=SUMX(tabFilialen;tabFilialen[Verkaufsmenge]*RELATED(tabEP[EP]))

• "Measures:" kann umbenannt werden und wird dann so in der Pivot Table angezeigt.
• Der Doppelpunkt am Ende des Namens ist notwendig!

In der PivotTable kann nun Ein KPI eingetragen werden – eine grafische Sichtbarkeitmachung der Ziele:

Absoluter KPI

In der PivotTable gibt es nun ein Ampel-Symbol. Das Aufklappen und "Status" de-/aktivieren – dann werden die Grafik-Symbole angezeigt:

Zielwert: Measure

Statt eines absoluten Wertes kann auch ein Measure ausgewählt werden. Dazu sind zwei Measures notwendig, die ähnliche Inhalte haben. Beispielsweise kann ein Measure mit „Umsatz 2021“ und ein anderer mit „Umsatz 2022“ erstellt werden. Erstelle ich nun ein Measure zu „Umsatz 2021“ kann als Measure „Umsatz 2022“ ausgewählt werden und somit ein Vergleich der beiden Jahre grafisch hergestellt werden.

Wobei die Statusschwellenwerte dann eher in Prozent angegeben werden.

Implizierter Measure

Wenn in der PivotTable ein Feld in den Wertebereich gezogen wird und die Standardfunktion beibehalten wird (SUM, Mittelwert, Count, Min, Max, DISTINCTCOUNT), dann wird in der Power Pivot-Verwaltung am Ende der zugehörigen Spalte ein schreibgeschützter implizierter Measure angezeigt

Hirarchien erstellen

…bieten sich beispielsweise für Datum oder Vertriebsgruppen oder ähnlichem an.

Beispiel mit Zeit:

Alternative 1:
Diagramm – Tabelle3 anklicken – oben rechts: Hierarchie erstellen (Icon). Anschließend die Felder in die Hierarchie ziehen. Hierarchie – Kontextmenü: Umbenennen

Alternative 2:
Die Felder markieren (Beispiel: Tag, Jahr, Monat) – dann Kontextmenü: Hierarchie erstellen.

In der Pivot Table kann nun das Hierarchiefeld mit den vorgegebenen Feldern Gesamt eingegeben werden.

DAX-Funktionen

Wenn die Berechnungen innerhalb der gleichen Tabelle mit Zellen der gleichen Tabelle stattfinden, kann der Tabellenname weggelassen werden.

Namen in eckigen Klammern ([…]) beziehen sich immer auf Spaltennamen

Obere Bereich

Berechnung des Wochentages

=WEEKDAY([Datum];2)

1 à Sonntag=1, Samstag=7
2 à Montag=1, Sonntag=7
3 à Montag=0, Sonntag=6

Berechnung des Jahres

=YEAR([Datum])

Berechnung des Monats

=month(Tabelle3[Datum])

Berechnung des Tages

=DAY(Tabelle3[Datum])

Quartalsberechnung – nur Zahl

=ROUNDUP(MONTH(Tabelle1[Datum])/3;0)

Die Zahl am Ende gibt die Nachkommastellen an. Die „0“ am Ende sorgt für ein Aufrunden (auch wenn die Nachkommastelle unter 5 liegt). Damit ist gewährleistet, dass immer zum vollen Monat aufgerundet wird. Monat „0“ kann es somit nicht geben.

Der Unterschied zur IF-Anweisung ist, dass mit einer Zahl eventuell weitergerechnet werden kann.

Textverarbeitung

Beispielsweise sollen Kennungen zergliedert werden:

Da das Leerzeichen stets an anderer Stelle steht, kann nicht mit festen Werten gearbeitet werden.

Also muss LEFT oder RIGHT mit flexiblen Werten arbeiten. Das kann mit SEARCH erreicht werden. Wobei bei RIGHT die Länge des Gesamtstrings berechnet werden muss, damit die korrekte Anzahl Zeichen ermittelt werden kann.

=left(Tabelle1[Kennung];SEARCH(" ";Tabelle1[Kennung]))
=RIGHT(Tabelle1[Kennung];LEN(Tabelle1[Kennung])-SEARCH(" ";Tabelle1[Kennung]))

SEARCH gibt die Position zurück, an der der gesuchte Text – in diesem Beispiel das Leerzeichen – steht:
=SEARCH(" ";Tabelle1[Kennung])

LEN gibt die Länge der Zeichenkette zurück:
=LEN(Tabelle1[Kennung])

Somit ergibt sich:
RIGHT([ZEICHENKETTE] ; LÄNGE([ZEICHENKETTE] – Position des gesuchten Zeichens (im Beispiel das Leerzeichen).

Texte und oder Werte mit Text zusammenfügen

=“Quartal „ & Produkte[Quartal als Zahl]

=“Apfel“ & „kuchen“

Im unteren Bereich - Measures

Zählt die Anzahl unterschiedlicher Wochentage:

Wochentage:=COUNTROWS(DISTINCT(Tabelle3[Tag]))

Summe einer Spalte:

Einnahmensumme:=SUM(Tabelle3[Einnahme])

Filtern nach Umsätzen nach Jahren

Umsatz 2013:=sumx(FILTER(Tabelle1;YEAR(Tabelle1[Datum])=2013);Tabelle1[Einnahme])

Bei dieser Methode muss für jedes Jahr ein Measure erstellt werden. Eine bessere Alternative kann mit CALCULATE erreicht werden:

Umsatz:=CALCULATE(SUM(Tabelle1[Einnahme]);FILTER(Tabelle1;YEAR(Tabelle1[Datum]=2013)))

Perspektiven

Menü: Erweitert/Perspektiven/Erstellen und Verwalten

Neue Perspektive: Damit können bestimmte Tabellen oder Felder einer Tabelle ausgewählt werden, die dann entsprechend angezeigt werden. Damit können insbesondere in großen und umfangreichen Tabellen/Daten, die Sicht eingegrenzt werden oder auch bestimmten Gruppen, bestimmte Ansichten zur Verfügung gestellt werden.

Wird mit der Maus über den Spaltennamen gefahren, werden drei Icons darüber angezeigt:

1. Löschen
2. Umbenennen
3. Kopieren

Die Sichten werden auch in der Datensicht übernommen.

Datumstabelle

Die Datumstabelle orientiert sich von der Jahreszahl und den Gesamtmonaten (Von Bis) an den vorhandenen Daten und bietet einen vollständigen Kalender für den dargestellten Zeitraum. Damit können beispielsweise Wochentage zugeordnet werden.

Es muss eine Verknüpfung zwischen dem Feld Datum der Tabelle und Date im Kalender erstellt werden.

Im Kalender werden Monat und Jahr, sowie dann alle Tage des jeweiligen Monats als Hierarchie übernommen.

Die Datumshierarchie zeigt nur die Tage an, an denen eine Einnahme stattfand.

Alternative mit Wochentag, statt Datum:

Oder MMM-JJJJ:

Weiterführendes

https://www.durchblick-durch-daten.de/

https://support.microsoft.com/de-de/office/power-pivot-%C3%BCbersicht-und-schulung-f9001958-7901-4caa-ad80-028a6d2432ed

https://www.tabellenexperte.de/einfuehrung-in-das-datenmodell-und-power-pivot/

https://support.microsoft.com/de-de/office/data-analysis-expressions-dax-in-power-pivot-bab3fbe3-2385-485a-980b-5f64d3b0f730

Übersicht über DAX-Funktionen

https://learn.microsoft.com/de-de/dax/dax-overview

YouTube:

//www.youtube.com/@at-excel/search?query=power%20pivot">https://www.youtube.com/@at-excel/search?query=power%20pivot

^[1] DAX = Data Analysis Expressions