Power Pivot für die DatenanalyseUmsatzanteile in Power Pivot mit ALL und ALLSELECTED berechnen

Ein häufiger Praxisfall in Analysen ist die Frage: Wie hoch ist der Anteil eines Produkts, einer Region oder einer Kundengruppe am Gesamtumsatz?

In diesem Beitrag lernen Sie zwei sehr wichtige DAX-Funktionen kennen, mit denen Sie genau das berechnen können:

• ALL: entfernt sämtliche Filter, auch aus Datenschnitten
• ALLSELECTED: entfernt Filter, berücksichtigt aber aktive Auswahl im Bericht

Beide Funktionen werden in der Regel in Kombination mit CALCULATE eingesetzt.

Ausgangssituation: Prozentualer Anteil vom Gesamtumsatz

Angenommen, Sie haben eine Pivot-Tabelle mit den absoluten Umsätzen für die einzelnen Produkte. Nun möchten Sie zusätzlich wissen:

• Welchen Anteil am Gesamtumsatz hat ein bestimmtes Produkt?
• Wie groß ist der Marktanteil eines Produkts?
• Wie verteilen sich die Umsätze auf die Regionen?

Um diese Fragen zu beantworten, brauchen Sie in jeder Zeile zwei Werte:

• Den Umsatz des aktuellen Elements (zum Beispiel Produkt A).
• Den Gesamtumsatz aller Produkte als Vergleichs- oder Basiswert.

Erst durch die Division dieser beiden Werte erhalten Sie den Umsatzanteil in Prozent.

Schritt 1: Gesamtumsatz mit ALL berechnen

In der Pivot-Tabelle sorgt die Zeilenbeschriftung (zum Beispiel ProduktID) dafür, dass das Measure [Umsatz] immer nur für ein bestimmtes Produkt berechnet wird.

Hinweis: Wie Sie das Measure [Umsatz] aus Absatzmenge und Preis definieren, ist in diesem Beitrag erklärt.

Um den Gesamtumsatz zu ermitteln, müssen Sie diesen Filter „ignorieren“. Genau dafür können Sie die Funktion ALL einsetzen.

Um den Gesamtumsatz zu berechnen, erstellen Sie ein Measure. Aktivieren Sie im Menüband die Befehlsfolge Registerkarte Power Pivot > Befehlsgruppe Berechnungen > Befehl Measures > Befehl Neues Measure.

Es öffnet sich das Dialogfeld Measure. Definieren Sie hier das Measure Gesamtumsatz in der Tabelle tbl_Bestellungen, indem Sie die folgende Formel hinterlegen:

=CALCULATE([Umsatz];ALL(tbl_Bestellungen))

Mit der Funktion CALCULATE wird nun der Gesamtumsatz berechnet. Die einzelnen Argumente haben hierbei die folgende Bedeutung:

• [Umsatz] ist das bestehende Measure.
• ALL(tbl_Bestellungen) löscht alle Filter auf der Tabelle tbl_Bestellungen.

Ergebnis: Egal, welches Produkt in der Zeile steht – das Measure liefert immer den Gesamtumsatz aller Produkte.

Schritt 2: Umsatzanteil berechnen

Nun können Sie den Umsatz eines Produkts durch den Gesamtumsatz teilen. Definieren Sie hierzu in der Tabelle tbl_Bestellungen ein weiteres Measure mit der folgenden Formel:

=DIVIDE([Umsatz];[Gesamtumsatz aller Bestellungen])

Mit der Funktion DIVIDE können Sie eine Division in DAX durchführen. Durch die Funktion wird der Produktumsatz durch den Gesamtumsatz dividiert. Sie erhalten somit den Umsatzanteil pro Produkt am Gesamtumsatz.

Tipp: Stellen Sie unten bei Zahlenformat das Format Prozentsatz ein, damit Sie die Ergebnisse gleich als Prozentzahl dargestellt bekommen.

Wenn Sie in dieser Pivot-Tabelle einen Datenschnitt einfügen und beispielsweise nach Kunde filtern, wird mit CALCULATE und ALL ein Ergebnis angezeigt, das Sie in dieser Form vermutlich nicht erwarten.

Mit ALL haben Sie alle Filter entfernt – auch die aus Datenschnitten. Der Umsatzanteil bezieht sich also weiterhin auf den absoluten Gesamtumsatz aller Produkte und aller Kunden.

Wenn Sie im Datenschnitt Alpha OHG auswählen, zeigt das Measure den Anteil am Gesamtumsatz, nicht am Umsatz der Alpha OHG. Für diese Analysen brauchen Sie eine andere Steuerung.

ALLSELECTED – manchmal die bessere Wahl bei Datenschnitten

Um sicherzustellen, dass die Auswahl im Datenschnitt berücksichtigt wird, setzen Sie statt ALL die Funktion ALLSELECTED ein.

Erstellen Sie in der Tabelle tbl_Bestellungen ein neues Measure für die gefilterten Umsätze:

=CALCULATE([Umsatz];ALLSELECTED(tbl_Bestellungen))

ALLSELECTED entfernt nur die Filter, die durch Zeilen- oder Spaltenbeschriftungen gesetzt werden.

Filter aus Datenschnitten, Seitenfiltern oder Berichtseinstellungen bleiben erhalten!

Wählen Sie im Datenschnitt nur „Alpha OHG“, dann bezieht sich der Umsatzanteil nur auf den Umsatz der „Alpha OHG“. Wählen Sie „Alpha OHG“ und „Beispiel KG“, dann bezieht sich der Umsatzanteil nur auf den Umsatz dieser beiden Kunden.

Umsatzanteil im Kontext von ALLSELECTED

Nun können Sie mit dem gefilterten Gesamtumsatz den prozentualen Anteil berechnen. Definieren Sie ein neues Measure um den Umsatzanteil für gefilterte Umsätze zu berechnen:

=divide([Umsatz];[Gesamtumsatz (gefiltert)])

Dieses Measure berücksichtigt die Auswahl in Datenschnitten und liefert damit genau die Anteile, die Sie im aktuellen Berichtskontext benötigen.

Vergleich: ALL vs. ALLSELECTED

• ALL entfernt alle Filter auf einer Tabelle – auch aus Datenschnitten.
• ALLSELECTED entfernt nur Filter aus Zeilen oder Spalten, behält aber die Datenschnittauswahl.

Mit ALL können Sie den Umsatzanteil eines Kunden am absoluten Gesamtumsatz berechnen – unabhängig von Filtern.

Mit ALLSELECTED können Sie den Umsatzanteil eines Kunden am aktuellen Berichtskontext berechnen – zum Beispiel wenn der Datenschnitt auf einen bestimmten Kunden gesetzt wurde.

Beide Varianten haben ihre Berechtigung – je nach Analysefrage!

Fazit

Mit CALCULATE in Kombination mit ALL und ALLSELECTED haben Sie zwei mächtige Werkzeuge:

• ALL: Entfernt alle Filter – ideal für Anteile am absoluten Gesamtwert.
• ALLSELECTED: Berücksichtigt Datenschnitte – ideal für dynamische Vergleiche im Berichtskontext.
• Performance beachten: ALL und ALLSELECTED sind sehr leistungsfähig, können bei großen Tabellen aber spürbar Rechenzeit benötigen.