Freigeben über

Erstellen eines Measures in einer PivotTable oder in einem PivotChart

  • Artikel

Ein Measure ist eine Berechnung, die Sie zum Messen eines Ergebnisses in Bezug auf andere Faktoren erstellen, die für die Analyse relevant sind, z. B. Zeit, Geografie, Organisation oder Produkteigenschaften. Es gibt mehrere Möglichkeiten, ein Measure zu erstellen, aber je nach geplanter Verwendung des Measures sind nicht alle Ansätze gleichermaßen effektiv. Stellen Sie speziell beim Erstellen einer Arbeitsmappe zur Verwendung als Datenmodell in einer Berichtsanwendung sicher, dass Sie nur explizite Measures erstellen, wie in den unten aufgeführten Abschnitten beschrieben.

Erstellen von Measures zur Verwendung in Datenmodellen

In einer PowerPivot-Arbeitsmappe erstellte Measures können in Berichten verwendet werden, die Sie in Power View und andere Berichtsanwendungen erstellen, parallel zu anderen direkt im Bericht erstellten Measures oder Berechnungen. Damit ein Measure im Bericht als vordefinierte Berechnung angezeigt wird, müssen Sie es in einer PowerPivot-Arbeitsmappe als explizites Measure erstellen. Ein explizites Measure ist ein Measure, das Sie manuell erstellen. Es steht im Gegensatz zu impliziten Measures, die in Excel generiert werden, wenn Sie dem Werte-Bereich einer PivotTable Felder hinzufügen.

Erstellen von Measures zur Verwendung in Excel

Beim Erstellen eines Measures in Excel müssen Sie der PowerPivot-Arbeitsmappe zuerst eine PivotTable oder ein PivotChart hinzufügen. Sie können dann Measures mit einem der folgenden Ansätze erstellen:

  • Erstellen Sie ein implizites Measure, indem Sie ein Feld aus der PowerPivot-Feldliste in den Werte-Bereich ziehen. Wenn Sie ein numerisches Feld ziehen, wird das implizite Measure mit einer SUM-Aggregation berechnet. Wenn Sie ein Textfeld ziehen, wird das Measure mit einer COUNT-Aggregation berechnet. Sie können für ein implizites Measure die Berechnung in eine andere Aggregation ändern, z. B. MIN, MAX oder DISTINCTCOUNT.

    HinweisHinweis

    Implizite Measures sind einfach zu erstellen, sie haben jedoch mehr Einschränkungen als explizite Measures. Implizite Measures können nicht umbenannt, verschoben oder in anderen PivotTables oder Diagrammen in der Arbeitsmappe verwendet werden. Außerdem wird, da implizite Measures auf einem vorhandenen Feld basieren, beim Löschen dieses Felds auch das verknüpfte implizite Measure gelöscht. Schließlich können implizite Measures nur das in die Aggregation integrierte Datenformat verwenden. Sie unterstützen nicht die verschiedenen Datenformate, die expliziten Measures zur Verfügung stehen.

  • Erstellen Sie manuell ein explizites Measure mithilfe der Schaltfläche Neues Measure auf dem PowerPivot-Menüband.

  • Erstellen Sie manuell ein explizites Measure im PowerPivot-Fenster im Berechnungsbereich, indem Sie einen Measurenamen und eine Formel im Formelbereich einer Zelle eingeben.

Wenn Sie das Measure hinzufügen, wird die Formel für jede Zelle im Bereich Werte der PivotTable ausgewertet. Da für jede Kombination von Zeilen- und Spaltenüberschriften ein Ergebnis erstellt wird, kann das Ergebnis für das Measure in jeder Zelle anders sein.

Beispiel: Erstellen eines expliziten Measures, das eine einfache Aggregation verwendet

Dieses Beispiel enthält fahrradbezogene Daten aus der Datenbank AdventureWorks. Informationen zum Herunterladen der Beispielarbeitsmappe finden Sie unter Abrufen von Beispieldaten für PowerPivot. Weitere Informationen zu Formeln finden Sie unter Erstellen von Formeln für Berechnungen.

In diesem Beispiel werden zwei Methoden zum Erstellen eines expliziten Measures veranschaulicht. Zuerst erstellen Sie ein Measure im PowerPivot-Fenster in einem Berechnungsbereich, der alle im Modell definierten Measures anzeigt. Danach fügen Sie der PowerPivot-Arbeitsmappe eine PivotTable oder ein PivotChart hinzu und fügen dann mit dem Dialogfeld Measureeinstellungen ein Measure hinzu. Die Formel eines Measures definiert eine Summe, einen Mittelwert oder eine andere Berechnung mithilfe der Spalten und Tabellen in der PowerPivot-Arbeitsmappe.

  1. Klicken Sie im Fenster PowerPivot auf die Registerkarte Home und in der Gruppe Sichten auf Berechnungsbereich.

  2. Klicken Sie in der Tabelle "FactResellerSales" auf eine Zelle an einer beliebigen Stelle im Berechnungsbereich.

  3. Geben Sie in der Bearbeitungsleiste oben in der Arbeitsmappe eine Formel im Format <measurename>:<formel> ein:

    Projected Sales:=SUM('FactResellerSales'[SalesAmount])*1.06

  4. Klicken Sie auf OK, um die Formel zu übernehmen.

  5. Klicken Sie im Fenster PowerPivot auf die Registerkarte Home und in der Gruppe Berichte auf PivotTable.

    Überprüfen Sie im Dialogfeld PivotTable erstellen, ob Neues Arbeitsblatt ausgewählt ist, und klicken Sie auf OK.

    PowerPivot erstellt in einem neuen Excel-Arbeitsblatt eine leere PivotTable und zeigt die PowerPivot-Feldliste rechts in der Arbeitsmappe an.

  6. Erweitern Sie die Tabelle FactResellerSales, um das erstellte Measure anzuzeigen. Wenn bereits vor dem Erstellen des Measures eine PivotTable in der Arbeitsmappe vorhanden war, müssen Sie am oberen Rand der PowerPivot-Feldliste auf die Schaltfläche Aktualisieren klicken, um die Felder zu aktualisieren.

  7. Klicken Sie im Excel-Fenster auf der Registerkarte PowerPivot in der Gruppe Measures auf Neues Measure.

  8. Klicken Sie im Dialogfeld Measureeinstellungen unter Tabellenname auf den Pfeil nach unten, und wählen Sie in der Dropdownliste die Option FactResellerSales aus.

    Mit der Tabellenauswahl wird festgelegt, wo die Definition des Measures gespeichert werden soll. Das Measure muss nicht mit einer Tabelle gespeichert werden, auf die das Measure verweist.

  9. Geben Sie unter Measurename (alle PivotTables) den Namen Total Quantity ein.

  10. Der Name des Measures muss innerhalb einer Arbeitsmappe eindeutig sein. Der Name, der bereits für alle beliebigen Spalten in einer Arbeitsmappe verwendet wird, kann nicht verwendet werden.

  11. Platzieren Sie den Cursor im Textfeld Formel hinter dem Gleichheitszeichen (=), und geben Sie dann die folgende Formel ein:

    SUM(FactResellerSales[OrderQuantity])

  12. Klicken Sie auf OK.

Beide Measures, die Sie erstellt haben, werden in der Quelldatentabelle gespeichert, sie können jedoch von jeder PivotTable oder jedem PivotChart verwendet werden. Die Measures werden in der PowerPivot-Feldliste angezeigt und stehen allen Benutzern der Arbeitsmappe zur Verfügung.

Beispiel: Erstellen eines expliziten Measures, das eine benutzerdefinierte Aggregation verwendet

In diesem Beispiel erstellen Sie eine benutzerdefinierte Aggregation, für die eine der neuen DAX-Aggregationsfunktionen verwendet wird, SUMX, und die Funktion ALL, von der in diesem Fall alle Werte in einer Spalte unabhängig von deren Kontext zurückgegeben werden. Im Beispiel werden die folgenden Spalten aus der DAX-Beispielarbeitsmappe verwendet:

  • DateTime[CalendarYear]

  • ProductCategory[ProductCategoryName]

  • ResellerSales_USD [SalesAmount_USD]

Im Beispiel wird eine PivotTable verwendet, die als Zeilenbezeichnung CalendarYear und als Spaltenbezeichnung ProductCategoryName enthält; SalesAmount_USD wird in der Measureformel verwendet. Im Beispiel wird die folgende Frage beantwortet: Welcher Anteil des Gesamtumsatzes der Jahre 2005-2008 wurde in den einzelnen Jahren und den Produktkategorien erzielt? Dadurch wird z. B. ersichtlich, welcher Anteil am Gesamtumsatz durch Fahrradverkäufe im Jahr 2007 erzielt wurde. Diese Frage wird mithilfe der folgenden Measureformel beantwortet:

=SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])

Die Formel wird wie folgt zusammengesetzt:

  1. Der Zähler (SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])) ist die Summe der Werte in ResellerSales_USD[SalesAmount_USD] für die aktive Zelle in der PivotTable. Durch den Kontext von CalendarYear und ProductCategoryName unterscheidet sich dieser Wert für jede Kombination aus Jahr und Produktkategorie. Die gesamte Anzahl der im Jahr 2003 verkauften Fahrräder unterscheidet sich z. B. von der gesamten Anzahl des im Jahr 2008 verkauften Zubehörs.

  2. Für den Nenner wird zuerst eine Tabelle angegeben (ResellerSales_USD), und mithilfe der ALL -Funktion wird der gesamte Kontext in der Tabelle entfernt. Dadurch wird sichergestellt, dass der Wert für jede Kombination aus Jahr und Produktkategorie übereinstimmt: Der Nenner bildet immer den Gesamtumsatz der Jahre 2005-2008.

  3. Anschließend werden mit der SUMX-Funktion die Summe der Werte in der ResellerSales_USD[SalesAmount_USD]-Spalte gebildet. Anders ausgedrückt erhalten Sie die Summe von ResellerSales_USD[SalesAmount_USD] für alle Verkäufe des Wiederverkäufers.

HinweisHinweis

In Windows Vista und Windows 7 sind die Funktionen im PowerPivot-Fenster in einem Menüband verfügbar, das in diesem Thema erläutert wird. In Windows XP stehen Funktionen in einer Gruppe von Menüs zur Verfügung. Wenn Sie Windows XP verwenden und anzeigen möchten, wie die Menübefehle mit den Befehlen im Menüband in Beziehung stehen, lesen Sie die Informationen unter Die PowerPivot-Benutzeroberfläche in Windows XP.

So erstellen Sie ein Measure, das eine benutzerdefinierte Aggregation verwendet

  1. Klicken Sie im Fenster PowerPivot auf die Registerkarte Home und in der Gruppe Berichte auf PivotTable.

  2. Überprüfen Sie im Dialogfeld PivotTable erstellen, ob Neues Arbeitsblatt ausgewählt ist, und klicken Sie auf OK.

    PowerPivot erstellt in einem neuen Excel-Arbeitsblatt eine leere PivotTable und zeigt die PowerPivot-Feldliste rechts in der Arbeitsmappe an.

  3. Verwenden Sie im Excel-Fenster die PowerPivot-Feldliste, um der PivotTable Spalten hinzuzufügen:

    1. Suchen Sie die Tabelle DateTime, und ziehen Sie die Spalte CalendarYear in den Bereich Zeilenbezeichnungen der PivotTable.

    2. Suchen Sie die Tabelle ProductCategory, und ziehen Sie die Spalte ProductCategoryName in den Bereich Zeilenbezeichnungen der PivotTable.

  4. Klicken Sie im Excel-Fenster auf der Registerkarte PowerPivot in der Gruppe Measures auf Neues Measure.

  5. Klicken Sie im Dialogfeld Measureeinstellungen unter Tabellenname auf den Pfeil nach unten, und wählen Sie in der Dropdownliste die Option ResellerSales_USD aus.

    Mit der Tabellenauswahl wird festgelegt, wo die Definition des Measures gespeichert werden soll. Das Measure muss nicht mit einer Tabelle gespeichert werden, auf die das Measure verweist.

  6. Geben Sie unter Measurename (alle PivotTables) den Namen AllResSalesRatio ein.

    Dieser Name wird als Bezeichner für das Measure verwendet. Daher muss er innerhalb der Arbeitsmappe eindeutig sein und kann nicht geändert werden.

  7. Geben Sie für Benutzerdefinierter Name (diese PivotTable) die Zeichenfolge All Reseller Sales Ratio ein.

    Dieser Name wird in der aktuellen PivotTable nur zu Anzeigezwecken verwendet. Sie können das Measure AllResSalesRatio z. B. in anderen PivotTables wieder verwenden, dort jedoch einen anderen Namen oder eine andere Sprache wählen.

  8. Platzieren Sie den Cursor im Textfeld Formel hinter dem Gleichheitszeichen (=).

  9. Geben Sie SUMX und dann eine Klammer ein.

    =SUMX(

    Während der Eingabe weist die QuickInfo unter dem Textfeld Formel darauf hin, dass die SUMX-Funktion zwei Argumente erfordert: eine Tabelle oder einen Ausdruck, der eine Tabelle zurückgibt, sowie einen Ausdruck, der die Zahlen bereitstellt, aus denen die Summe gebildet werden kann.

    Geben Sie Res ein, wählen Sie in der Liste ResellerSales_USD aus, und drücken Sie die TAB-TASTE.

    Der Spaltenname wird wie folgt in die Formel eingefügt:

    =SUMX(ResellerSales_USD

  10. Geben Sie ein Komma ein.

    Die QuickInfo wird aktualisiert und zeigt an, dass das nächste erforderliche Argument expression ist. Ein Ausdruck kann ein Wert, ein Verweis auf eine Spalte oder eine Kombination aus beiden sein. Sie könnten z. B. einen Ausdruck erstellen, der die Summe aus zwei anderen Spalten bildet. Für dieses Beispiel geben Sie den Namen einer Spalte an, die den Umsatz für jeden Wiederverkäufer enthält.

  11. Geben Sie die ersten Buchstaben des Namens der Tabelle ein, die die einzuschließende Spalte enthält. Geben Sie für dieses Beispiel Res ein, und wählen Sie in der Liste die Spalte ResellerSales_USD[SalesAmount_USD] aus.

  12. Drücken Sie die TAB-TASTE, um den Spaltennamen in die Formel einzufügen, und fügen Sie wie folgt eine schließende Klammer hinzu:

    =SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])

  13. Geben Sie einen Schrägstrich ein, und geben Sie anschließend den folgenden Code in das Dialogfeld Measureeinstellungen ein, bzw. verwenden Sie dazu die Kopier- und Einfügefunktion:

    SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])

    Die ALL-Funktion wird in der SUMX-Funktion geschachtelt. Die gesamte Formel wird nun wie folgt angezeigt:

    =SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])

  14. Klicken Sie auf Formel überprüfen.

    Die Formel wird auf Syntax- und Verweisfehler geprüft. Beheben Sie gegebenenfalls die Fehler, z. B. fehlende Klammern oder Kommas.

  15. Klicken Sie auf OK.

    Vom Measure werden nun Werte für jede Kombination aus Kalenderjahr und Produktkategorie in die PivotTable eingetragen.

  16. Formatieren Sie die Tabelle:

    1. Wählen Sie die Daten in der PivotTable aus, einschließlich der Zeile Gesamtsumme.

    2. Klicken Sie auf der Registerkarte Home in der Gruppe Zahl auf die Prozentschaltfläche (%), und klicken Sie anschließend zweimal auf die Schaltfläche für das Hinzufügen von Dezimalstellen (<- .0 .00).

    Die fertige Tabelle sieht nun folgendermaßen aus. Nun wird der Prozentsatz des Gesamtumsatzes für jede Kombination aus Produkt und Jahr angezeigt. Zum Beispiel betrug der Anteil der Fahrradverkäufe im Jahr 2007 31,71 % am Gesamtumsatz der Jahre 2005-2008.

All Reseller Sales

Spaltenbezeichnungen

 

 

 

 

Zeilenbezeichnungen

Accessories

Bikes

Clothing

Components

Gesamtergebnis

2005

0.02%

9.10%

0.04%

0.75%

9.91%

2006

0.11%

24.71%

0.60%

4.48%

29.90%

2007

0.36%

31.71%

1.07%

6.79%

39.93%

2008

0.20%

16.95%

0.48%

2.63%

20.26%

Gesamtergebnis

0.70%

82.47%

2.18%

14.65%

100.00%

Siehe auch

Verweis

Key Performance Indicator (Dialogfeld)

Konzepte

Measures in PowerPivot

PowerPivot-Fenster: Berechnungsbereich

Andere Ressourcen

Übersicht über Berichte, Diagramme und PivotTables