Neben den Standardberechnungen wie Summe, Mittelwert und Anzahl werden im Datenmodell von Power Pivot Measures bzw. berechnete Felder gebraucht, die Anteile berechnen, wie beispielsweise den prozentualen Anteil des Umsatzes eines Kunden am Gesamtumsatz aller Kunden. In dem folgenden Tipp zeigen wir Ihnen, wie Sie Measures für die Anteilsberechnungen erstellen und welche Ergebnisse in der PivotTable erscheinen.

Datenmodell in Power Pivot

Als Basis für die Berechnungen dient das folgende Datenmodell:

Es wird auf Basis dieses Datenmodells eine PivotTable erstellt, in der alle Kunde mit Ihren Umsätzen und ihren Anteilen am Gesamtumsatz erscheinen. Mit Power Pivot können Sie Daten auswerten, die aus verschiedenen Datenquellen stammen können. Eine einzige Tabelle kann weit mehr als 1 Million Datensätze umfassen.

Measure für den Gesamtumsatz

Zunächst wird ein Measure erstellt, mit dem Sie den Gesamtumsatz berechnen.

1. Klicken Sie auf der Registerkarte Power Pivot in der Gruppe Berechnungen auf die Schaltfläche Measures und dann auf Neues Measure. Das folgende Dialogbild erscheint:

2. Wählen Sie den Namen der Tabelle aus, in der Sie das Measure speichern möchten.
3. Geben Sie den Namen des Measures an, z.B. Gesamtumsatz.
4. Geben Sie die folgende Formel ein, um die Werte in der Spalte Umsatz der Tabelle selVerkauf zu summieren:
   =SUM(selVerkauf[Umsatz])
5. Wählen Sie als Kategorie Currency bzw. Währung.

6. Klicken Sie abschließend auf die Schaltfläche Formel überprüfen. Wenn die Formel syntaktisch in Ordnung ist, erscheint der Hinweis Keine Fehler in Formel. Das Dialogbild sieht folgendermaßen aus:

Die PivotTable mit den Kundennamen und den zugehörigen Umsätzen sieht folgendermaßen aus:

Measure für Zwischenberechnungen

Im nächsten Schritt wird ein Zwischenmeasure für weitere Berechnungen erstellt. Nennen Sie es Gesamtumsatz_Kunden_Alle. Die Formel für dies Measure lautet:

=CALCULATE([Gesamtumsatz];ALL(TKunde))

Die Funktion CALCULATE ändert, erweitert oder hebt den Filterkontext in der PivotTable auf. Die Aufhebung des Filterkontexts ist notwendig, damit pro Kunde der Wert des Gesamtumsatzes für die Anteilsberechnung zur Verfügung steht. In diesem Beispiel hebt die Funktion den aktuellen Filterkontext, sprich der einzelne Kunde mit seinen Verkaufsdaten, auf und ersetzt ihn durch die Verkaufsdaten aller Kunden, d.h. der Filter auf der Tabelle der Verkaufsdaten wird entfernt. Danach werden die Umsätze aller Kunden addiert. Die Aufhebung des Filters wird erreicht durch den Ausdruck ALL(TKunde). Wenn Sie dieses Measure zur Kontrolle in die PivotTable ziehen, wird für alle Kunden der gleiche Gesamtumsatz angezeigt.

Measure für die Berechnung von Anteilen

Erstellen Sie ein weiteres Measure mit dem Namen Anteil_Kunde_Gesamtumsatz und der folgenden Formel, um die prozentualen Anteile zu berechnen:

=DIVIDE([Gesamtumsatz];[Gesamtumsatz_Kunden_Alle];BLANK())

Die Funktion DIVIDE dividiert zwei Measures. Als dritter Parameter dieser Funktion können Sie einen Wert angeben, der in der PivotTable erscheinen soll, wenn der Nenner den Wert 0 hat. In unserem Beispiel soll ein leerer Wert angezeigt werden. Der Ausdruck dafür lautet BLANK(). Wählen Sie im Listenfeld Kategorie den Eintrag Number bzw. Zahl und wählen Sie als Format Prozentsatz aus.

Wenn das Measure nicht automatisch in der PivotTable erscheinen sollte, ziehen Sie das neu erstellte Measure in den Wertebereich der PivotTable. Die PivotTable sieht jetzt so aus:

Wenn Sie für die Kundennamen einen Filter aktivieren, werden nur die Gesamtumsätze und die Anteile der sichtbaren Kunden in Spalten B und C angezeigt. Sie benötigen ein weiteres Measure, wenn sich die Berechnung der Anteile nur auf den Gesamtumsatz der sichtbaren Kunden beziehen soll und nicht auf den Gesamtumsatz aller Kunden (siehe Spalte D im unteren Bild).

Die Formel des Measures Anteil_Kunde_Ausgewählt_Gesamtumsatz lautet:

=DIVIDE([Gesamtumsatz];[Gesamtumsatz_Kunden_Ausgewählt];BLANK())

Für die Berechnung ist vorher noch das Measure Gesamtumsatz_Kunden_Ausgewählt mit der folgenden Formel angelegt worden.

=CALCULATE([Gesamtumsatz];ALLSELECTED(TKunde))

DIE CALCULATE-Funktion ersetzt den aktuellen Filter, sprich alle Verkaufsdaten eines Kunden, durch einen neuen Filter, der die Verkaufsdaten aller in der PivotTable sichtbaren Kunden umfasst. Diese Filterung wird mit der Funktion ALLSELECTED(TKunde) erreicht.

Die Umsätze und Anteile der Kunden werden in der PivotTable auch dann richtig berechnet, wenn Sie den Ort aus der Tabelle TKunde als äußeres Zeilenfeld in der PivotTable verwenden:

Die Anteilswerte werden nicht richtig berechnet, wenn Sie anstelle des Namens oder des Ortes ein Feld aus einer anderen Tabelle als die des Kunden in die PivotTable ziehen. Im folgenden Beispiel wurde der Kundenname ersetzt durch eine Artikelbezeichnung. Die PivotTable sieht jetzt folgendermaßen aus:

Die Umsatzzahlen sind korrekt. Um die Anteile für die Artikel richtig zu berechnen, benötigen Sie vier neue Measures mit folgenden Formeln:

Gesamtumsatz_Artikel_Alle: =CALCULATE([Gesamtumsatz];ALL(TArtikel))

Gesamtumsatz_Artikel_Ausgewählt: =CALCULATE([Gesamtumsatz];ALLSELECTED(TArtikel))

Anteil_Artikel_Gesamtumsatz: =DIVIDE([Gesamtumsatz];[Gesamtumsatz_Artikel_Alle];BLANK())

Anteil_Artikel_Ausgewählt_Gesamtumsatz: =DIVIDE([Gesamtumsatz];[Gesamtumsatz_Artikel_Ausgewählt];BLANK())

Wenn Sie die neuen Measures für die Anteilsberechnungen der Artikel in die PivotTable übertragen und zwei Artikel auswählen, erhalten Sie folgende Ergebnisse:

Zum Abschluss soll noch das folgende Measure für die Anteilsberechung erstellt werden. Die Formel lautet:

Anteil_Ausgewählt_Gesamtumsatz: =DIVIDE([Gesamtumsatz];CALCULATE([Gesamtumsatz];ALLSELECTED());BLANK())

Mit diesem Measure können Sie alle Anteile in der PivotTable auf den Gesamtumsatz aller nur sichtbaren Elemente beziehen. Es entspricht der Funktion Werte anzeigen als % des Gesamtergebnisses, das Sie bei einer PivotTable, die nicht auf einem Datenmodell basiert, einstellen können. Wenn alle Measures für die Anteilsberechnung in einer PivotTable eingesetzt werden und jeweils nur 2 Artikel und 2 Kunden ausgewählt werden, sehen die Ergebnisse folgendermaßen aus:

Hinweis

• Die Anzahl der Measures im Datenmodell von Power Pivot können Sie zwar verringern, indem Sie auf Measures für Zwischenberechnungen verzichten und die Formeln stärker ineinander verschachteln. Mit den zusätzlichen Measures werden Ihre Berechnungen übersichtlicher. Sie können zudem die Zwischenergebnisse besser kontrollieren.