Power Pivot – Wichtige FunktionenUmsatzanteile in Power Pivot mit ALL und ALLSELECTED berechnen

Daten filtern in Power Pivot mit ALL und ALLSELECTED

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

In diesem Beitrag lernen Sie zwei 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 die aktive Auswahl in der Pivot-Tabelle

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 einzelnen Kunden?

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 ProduktName) dafür, dass das Measure [Umsatz] immer nur für ein bestimmtes Produkt berechnet wird.

Hinweis: Hier ist erklärt, wie Sie das Measure [Umsatz] aus Absatzmenge und Preis berechnen.

Um den Gesamtumsatz zu ermitteln, müssen Sie den Filter ProduktName aber „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 Umsatz_Gesamt, indem Sie die folgende Formel hinterlegen:

Umsatz_Gesamt:= CALCULATE([Umsatz]; ALL(t_Bestellungen))

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

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

Ergebnis: Egal, welches Produkt in der Zeile Ihrer Pivot-Tabelle 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 ein weiteres Measure mit der folgenden Formel:

Umsatzanteil:= DIVIDE([Umsatz]; [Umsatz_Gesamt]; "")

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.

Wann ALL nicht das gewünschte Ergebnis liefert

Wenn Sie in der Pivot-Tabelle einen Datenschnitt einfügen und beispielsweise nach Produkt 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 Bremsbeläge MTB auswählen, zeigt das Measure den Anteil der einzelnen Kunden mit dem Produkt Bremsbeläge MTB am Gesamtumsatz, nicht am Umsatz mit Bremsbelägen MTB. 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 ein neues Measure für die gefilterten Umsätze:

Umsatz_Gesamt_Gefiltert:= CALCULATE([Umsatz]; ALLSELECTED(t_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 „Bremsbeläge MTB“, dann bezieht sich der Umsatzanteil nur auf den Umsatz mit diesem Produkt. Wählen Sie „Bremsbeläge MTB“ und „Bremsscheibe 180 mm“, dann bezieht sich der Umsatzanteil nur auf den Umsatz dieser beiden Produkte.

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:

Umsatzanteil_Gefiltert:= DIVIDE([Umsatz]; [Umsatz_Gesamt_Gefiltert]; "")

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

Vergleich: ALL und 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 ein bestimmtes Produkt gesetzt wurde.

Beide Varianten haben ihre Berechtigung – je nach Analysefrage!

Fazit

Mit CALCULATE in Kombination mit ALL und ALLSELECTED haben Sie ein mächtiges Werkzeug:

• 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.