Tutorials

Pivot-Tabellen: Prozentanteil der Gesamtsumme trotz Filtern beibehalten

Melden

Hier ist ein detailliertes Tutorial, wie du in Excel Pivot-Tabellen filterst, ohne dass sich die Gesamtsumme im Nenner verändert. So bleibt dein Prozentanteil immer auf die ursprüngliche Gesamtsumme bezogen.

Das Problem

Standardmäßig berechnet Excel den "Prozentwert des Gesamtergebnisses" basierend auf den aktuell sichtbaren Daten. Wenn du also eine Kategorie herausfilterst, wird die neue (kleinere) Summe zu 100 % – was deine Prozentanteile verfälscht, wenn du sie weiterhin auf das ursprüngliche Ganze beziehen möchtest.

Es gibt zwei Wege, dies zu lösen: Der "Profi-Weg" über das Datenmodell (DAX) und der "Quick-Fix" über ein verstecktes Feld.

Methode 1: Der Profi-Weg (Über das Datenmodell/Power Pivot)

Dies ist die sauberste Methode, da sie dynamisch funktioniert.

Schritt 1: Daten zum Datenmodell hinzufügen

  1. Klicke in deine Datentabelle.
  2. Gehe auf Einfügen > PivotTable.
  3. Setze im Dialogfenster unbedingt den Haken bei: "Dem Datenmodell diese Daten hinzufügen".
  4. Klicke auf OK.

Schritt 2: Ein Measure für die Gesamtsumme erstellen

Wir brauchen eine Formel, die Filter ignoriert.

  1. Klicke rechts in der Feldliste mit der rechten Maustaste auf den Tabellennamen und wähle Measure hinzufügen... (oder gehe oben im Menü auf den Reiter "Power Pivot").
  2. Measure-Name: Gesamtsumme Ohne Filter
  3. Formel:
    =CALCULATE(SUM([Umsatz]); ALL('DeineTabelle'))

    (Ersetze [Umsatz] durch deine Wertspalte und 'DeineTabelle' durch deinen Tabellennamen.) Die Funktion ALL sorgt dafür, dass alle Filter ignoriert werden.

Schritt 3: Den Prozentanteil berechnen

  1. Erstelle ein zweites Measure.
  2. Measure-Name: % von Gesamtsumme
  3. Formel:
    =DIVIDE(SUM([Umsatz]); [Gesamtsumme Ohne Filter])
  4. Wähle als Formatierung Prozentsatz.

Das Ergebnis

Wenn du nun einen Datenschnitt (Slicer) oder Filter nutzt, ändert sich die SUM([Umsatz]), aber der Nenner (Gesamtsumme Ohne Filter) bleibt gleich. Dein Prozentsatz bezieht sich immer auf das große Ganze.

Methode 2: Der "Trick" ohne Formeln (Top-N Filter)

Wenn du kein Power Pivot nutzen möchtest, kannst du die Filterung über einen Filter-Trick "vorgaukeln".

  1. Erstelle deine Pivot-Table ganz normal.
  2. Ziehe deinen Wert (z. B. Umsatz) zweimal in den Bereich Werte.
  3. Klicke den zweiten Wert rechts an -> Werte anzeigen als... -> % des Gesamtergebnisses.
  4. Wichtig: Benutze nicht die normalen Checkboxen im Zeilenfilter, um Daten auszublenden.
  5. Klicke stattdessen auf den kleinen Pfeil bei den Zeilenbeschriftungen -> Wertefilter -> Größer als... (oder eine andere Bedingung).

Hinweis: Diese Methode ist weniger flexibel als Methode 1, funktioniert aber für einfache Ansichten, bei denen man z. B. nur die Top-Werte sehen will, ohne die 100%-Basis zu verlieren.

Methode 3: Visuelles Filtern (Slicer-Verbindung trennen)

Ein weiterer Workaround ist die Nutzung von zwei Pivot-Tabellen.

  1. Erstelle zwei identische Pivot-Tabellen.
  2. Die erste Pivot-Tabelle dient als "Master" für die Gesamtsumme (sie wird nicht gefiltert).
  3. Die zweite Pivot-Tabelle ist deine Arbeitsansicht.
  4. Verknüpfe die Felder der zweiten Pivot-Tabelle per Zellbezug (=A1/Master!$B$10) mit der Summe der ersten Tabelle.
  5. Da dies jedoch bei Größenänderungen der Pivot-Tabelle instabil ist, wird Methode 1 (DAX) ausdrücklich empfohlen.

Zusammenfassung: Welche Formel ist wichtig?

Wenn du mit dem Datenmodell arbeitest (Methode 1), ist dies die magische Formel: =CALCULATE(SUM(Spalte); ALL(Tabelle))

  • SUM: Berechnet den gefilterten Wert.
  • CALCULATE + ALL: Berechnet den Wert, als gäbe es keine Filter.
  • DIVIDE: Teilt beides sicher durcheinander.

Tipp: Du kannst den Datenschnitt (Slicer) nun verwenden, wie du willst – die Prozentwerte bleiben im Verhältnis zum ursprünglichen Gesamtwert korrekt!

0