Einsatz von berechneten Spalten und berechneten Feldern

Wenn sie sich mit der Verwendung von PowerPivot vertraut machen, stellen die meisten Benutzer fest, dass die wahre Stärke darin besteht, ein Ergebnis auf irgendeine Weise zu aggregieren oder zu berechnen. Wenn Ihre Daten eine Spalte mit numerischen Werten aufweisen, können Sie sie einfach aggregieren, indem Sie sie in einer PivotTable- oder Power View-Feldliste auswählen. Da es sich um eine numerische Matrix handelt, wird sie naturgemäß automatisch addiert, gemittelt, gezählt oder unabhängig von der von Ihnen ausgewählten Art der Aggregation. Dies wird als implizites Maß bezeichnet. Implizite Measures eignen sich hervorragend für eine schnelle und einfache Aggregation, doch haben sie auch Grenzen, die mit expliziten Measures und berechneten Spalten fast immer überwunden werden können.

Betrachten wir zunächst ein Beispiel, in dem eine berechnete Spalte zum Hinzufügen eines neuen Textwerts zu jeder Zeile in einer Tabelle namens "Product" (Produkt) verwendet wird. Jede Zeile in der Tabelle "Product" enthält alle Arten von Informationen zu den jeweiligen Produkten, die wir verkaufen. Es gibt Spalten für den Produktnamen, die Farbe, die Größe, den Händlerpreis usw. Es gibt eine weitere verwandte Tabelle namens "Product Category" (Produktkategorie), die die Spalte "ProductCategoryName" (Produktkategoriename) enthält. Wir möchten erreichen, dass jedes Produkt in der Tabelle "Product" den Produktkategorienamen aus der Tabelle "Product Category" einbezieht. In unserer Produkttabelle können wir wie folgt eine berechnete Spalte namens "Product Category" erstellen:

Unsere neue Formel für "Product Category" verwendet die DAX-Funktion RELATED, um Werte aus der Spalte "ProductCategoryName" in die verknüpfte Tabelle "Product Category" abzurufen und diese Werte dann für jedes Produkt (jede Zeile) in die Produkttabelle einzugeben.

Dies ist ein gutes Beispiel, wie mithilfe einer berechneten Spalte ein fester Wert für die einzelnen Zeilen hinzugefügt werden kann, die später im Bereich ZEILEN, SPALTEN oder FILTER einer PivotTable oder in einem Power View-Bericht verwendet werden kann.

Lassen Sie uns ein weiteres Beispiel erstellen, bei dem wir eine Gewinnspanne für unsere Produktkategorien berechnen möchten. Dies ist ein häufiges Szenario, sogar in vielen Tutorials. In unserem Datenmodell gibt es eine Tabelle "Umsätze", die Transaktionsdaten enthält, und es besteht eine Beziehung zwischen der Tabelle "Umsätze" und der Tabelle "Produktkategorie". In der Tabelle "Umsätze" gibt es eine Spalte mit den Verkaufsbeträgen und eine weitere Spalte mit den Kosten.

Wir können eine berechnete Spalte erstellen, die einen Gewinnbetrag für jede Zeile berechnet, indem die Werte in der Spalte "COGS" (Wareneinsatz) wie folgt von Werten in der Spalte "SalesAmount" (Umsatzbetrag) subtrahiert werden:

Jetzt können wir eine PivotTable erstellen und das Feld "Produktkategorie" auf "SPALTEN" und das neue Feld "Gewinn" in den Bereich "WERTE" ziehen (eine Spalte in einer Tabelle in PowerPivot ist ein Feld in der PivotTable-Feldliste). Das Ergebnis ist eine implizite Kennzahl mit dem Namen "Gewinnsumme". Dies ist ein aggregierter Betrag von Werten aus der Spalte "Gewinn" für jede der verschiedenen Produktkategorien. Unser Ergebnis sieht wie folgt aus:

In diesem Fall ist "Profit" nur als Feld in WERTE sinnvoll. Wenn wir "Profit" im Bereich SPALTEN ablegen, würde unsere PivotTable wie folgt aussehen:

Das Feld "Profit" bietet keine sinnvollen Informationen, wenn es in den Bereichen SPALTEN, ZEILEN oder FILTER abgelegt wird. Es ist nur als aggregierter Wert im Bereich WERTE sinnvoll.

Wir haben eine Spalte mit dem Namen "Gewinn" erstellt, die eine Gewinnspanne für jede Zeile in der Tabelle "Umsätze" berechnet. Anschließend haben wir Gewinn dem VALUES-Bereich unserer PivotTable hinzugefügt, wodurch automatisch ein implizites Measure erstellt wurde, in dem ein Ergebnis für jede der Produktkategorien berechnet wird. Wenn Sie denken, dass wir den Gewinn für unsere Produktkategorien wirklich zweimal berechnet haben, haben Sie Recht. Zuerst wurde ein Gewinn für jede Zeile in der Tabelle "Umsätze" berechnet und dann dem Bereich "WERTE" hinzugefügt, in dem er für jede der Produktkategorien aggregiert wurde. Wenn Sie auch denken, dass wir die berechnete Spalte "Gewinn" nicht wirklich erstellen mussten, haben Sie ebenfalls Recht. Aber wie berechnen wir dann unseren Gewinn, ohne eine Spalte für den berechneten Gewinn zu erstellen?

Der Gewinn könnte wirklich besser als explizit berechnetes Feld ermittelt werden.

Momentan belassen wir die berechnete Spalte "Profit" in der Tabelle "Sales" und "Product Category" in SPALTEN sowie "Profit" in WERTE der PivotTable, um unsere Ergebnisse vergleichen zu können.

Im Berechnungsbereich unserer Umsatztabelle erstellen wir eine Kennzahl namens Gesamtgewinn (um Namenskonflikte zu vermeiden). Am Ende ergeben sich dieselben Ergebnisse wie zuvor, jedoch ohne die berechnete Spalte "Profit".

Zuerst wählen wir in der Tabelle "Umsatz" die Spalte "Umsatzbetrag" aus und klicken dann auf "AutoSumme", um ein explizites Measure "Summe von Umsatzbetrag " zu erstellen. Denken Sie daran, dass wir ein explizites Measure im Berechnungsbereich einer Tabelle in Power Pivot erstellen. Dasselbe tun wir für die COGS-Spalte. Wir benennen diese Total SalesAmount und Total COGS um, damit sie leichter zu identifizieren sind.

Dann wird ein weiteres Measure mit dieser Formel erstellt:

Total Profit:=[Total SalesAmount] - [Total COGS]

Nachdem das Format des neuen Measures "Total Profit" in "Währung" geändert wurde, können wir es zu unserer PivotTable hinzufügen.

Wie Sie sehen können, gibt das neue Measure "Total Profit" dieselben Ergebnisse wie beim Erstellen der berechneten Spalte "Profit" und dem anschließenden Ablegen im Bereich WERTE zurück. Der Unterschied besteht darin, dass unser Measure "Total Profit" viel effizienter ist und unser Datenmodell übersichtlicher und kompakter gestaltet, da die Berechnung bei der Auswahl der Felder für die PivotTable und auch nur für die ausgewählten Felder erfolgt. Die berechnete Spalte "Profit" ist nicht wirklich erforderlich.

Warum ist dieser letzte Teil wichtig? Berechnete Spalten fügen Daten zum Datenmodell hinzu, und Daten belegen Arbeitsspeicher. Wenn wir das Datenmodell aktualisieren, werden auch Verarbeitungsressourcen benötigt, um alle Werte in der Spalte "Profit" neu zu berechnen. Wir müssen keine Ressourcen wie diese aufnehmen, da wir tatsächlich den Gewinn berechnen möchten, wenn wir die Felder auswählen, für die in der PivotTable der Gewinn ermittelt werden soll, z. B. für Produktkategorien, Regionen oder nach Datum.

Betrachten wir ein anderes Beispiel. Ein Beispiel, in dem eine berechnete Spalte Ergebnisse erzeugt, die auf den ersten Blick richtig erscheinen, aber...

In diesem Beispiel sollen Umsatzbeträge als Prozentsatz des Gesamtumsatzes berechnet werden. Wir erstellen eine berechnete Spalte namens % of Sales (Prozentsatz des Umsatzes) wie folgt in der Tabelle "Sales":

Unsere Formel besagt: Für jede Zeile in der Tabelle "Sales" wird der Betrag in der Spalte "SalesAmount" durch die Gesamtsumme aller Beträge in der Spalte "SalesAmount" dividiert.

Wenn wir eine PivotTable erstellen und "Product Category" zu SPALTEN hinzufügen sowie die neue Spalte % of Sales auswählen, um sie in WERTE abzulegen, erhalten wir eine Gesamtsumme für "% of Sales" für die einzelnen Produktkategorien.

OK. Das sieht bisher gut aus. Aber es soll ein Datenschnitt hinzugefügt werden. "Calendar Year" (Kalenderjahr) wird hinzugefügt und dann ein Jahr ausgewählt. In diesem Fall wählen wir das Jahr 2007 aus. Dadurch erhalten wir Folgendes.

Auf den ersten Blick erscheint dies möglicherweise weiterhin richtig. Aber unsere Prozentsätze sollten insgesamt 100 % ergeben, da wir den Prozentsatz des Gesamtumsatzes für jede der Produktkategorien für 2007 erhalten möchten. Wo liegt der Fehler?

Unsere Spalte "% of Sales" hat einen Prozentwert für jede Zeile berechnet, der dem Wert in der Spalte "SalesAmount" dividiert durch die Gesamtsumme aller Werte in der Spalte "SalesAmount" entspricht. Die Werte in einer berechneten Spalte sind fest. Sie stellen für jede Zeile in der Tabelle ein unveränderliches Ergebnis dar. Als % of Sales zu unserer PivotTable addiert wurde, wurde es als Summe aller Werte in der Spalte "SalesAmount" aggregiert. Die Summe aller Werte in der Spalte "% of Sales" ergibt immer 100 %.

Die berechnete Spalte "% of Sales" kann gelöscht werden, da sie keine Hilfe darstellt. Stattdessen wird ein Measure erstellt, das den Prozentsatz des Gesamtumsatzes richtig berechnet, unabhängig von möglicherweise angewendeten Filtern oder Datenschnitten.

Erinnern Sie sich an das zuvor erstellte Measure "TotalSalesAmount", das einfach die Spalte "SalesAmount" summiert? Wir haben es als Argument in unserem Measure "Total Profit" verwendet und werden es erneut als Argument in unserem neuen berechneten Feld verwenden.

Mithilfe der folgenden Formel wird ein neues Measure erstellt:

% of Total Sales:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())

Diese Formel besagt: Dividiert das Ergebnis aus Total SalesAmount durch die Gesamtsumme von SalesAmount ohne andere Spalten- oder Zeilenfilter als die in der PivotTable definierten.

Wenn jetzt das neue % of Total Sales (Prozentsatz des Gesamtumsatzes) zur PivotTable hinzugefügt wird, erhalten wir:

Das sieht besser aus! Jetzt wird % of Total Sales für jede Produktkategorie als Prozentsatz der Gesamtumsätze für das Jahr 2007 berechnet. Wenn wir ein anderes Jahr oder mehr als ein Jahr im Datenschnitt "CalendarYear" auswählen, erhalten wir neue Prozentsätze für die Produktkategorien, aber unsere Gesamtsumme beträgt immer noch 100 %. Es können auch weitere Datenschnitte und Filter hinzugefügt werden. Das Measure "% of Total Sales" führt unabhängig von den angewendeten Datenschnitten und Filtern immer zu einem Prozentsatz des Gesamtumsatzes. Mit Measures wird das Ergebnis immer gemäß dem Kontext berechnet, der durch die Felder in SPALTEN und ZEILEN und durch alle Filter oder Datenschnitte bestimmt wird, die angewendet werden. Dies ist die Leistungsfähigkeit von Measures.

Hier finden Sie einige Richtlinien, die Ihnen bei der Entscheidung helfen können, ob eine berechnete Spalte oder ein Measure für eine bestimmte Anforderung bei der Berechnung geeignet ist:

Verwenden Sie in folgenden Situationen berechnete Spalten

• Wenn die neuen Daten in ZEILEN, SPALTEN oder FILTERN in einer PivotTable oder auf einer ACHSE, LEGENDE oder TILE BY in einer Power View-Visualisierung angezeigt werden sollen, müssen Sie eine berechnete Spalte verwenden. Genau wie normale Datenspalten können berechnete Spalten als Feld in jedem beliebigen Bereich verwendet werden, und wenn sie numerisch sind, können sie auch in WERTE aggregiert werden.
• Wenn Ihre neuen Daten einen festen Wert für die Zeile darstellen sollen. Beispielsweise, wenn eine Datentabelle mit einer Spalte von Datumsangaben vorliegt und Sie eine weitere Spalte verwenden möchten, die nur die Monatszahl enthält. Sie können eine berechnete Spalte erstellen, die nur die Monatsnummer aus den Datumsangaben in der Spalte "Date" (Datum) berechnet. Beispiel: =MONTH(‘Date’[Date]).
• Wenn Sie für jede Zeile einer Tabelle einen Textwert hinzufügen möchten, verwenden Sie eine berechnete Spalte. Felder mit Textwerten können niemals im Bereich WERTE aggregiert werden. Beispiel: =FORMAT('Date'[Date],"mmmm") ergibt den Monatsnamen für die einzelnen Datumsangaben in der Spalte "Date" der Tabelle "Date".

Verwenden von Measures

• Wenn das Ergebnis der Berechnung immer von den anderen Feldern abhängig sein soll, die Sie in einer PivotTable auswählen.
• Wenn Sie komplexere Berechnungen durchführen müssen, wie bei der Berechnung eines Zählers auf Basis eines bestimmten Filters oder der Berechnung eines Werts im Verlauf der Jahre oder einer Abweichung, verwenden Sie ein berechnetes Feld.
• Wenn Sie die Größe einer Arbeitsmappe minimieren und ihre Leistung maximieren möchten, erstellen Sie so viele Ihrer Berechnungen wie möglich als Measures. In vielen Fällen können sämtliche Berechnungen als Measures vorliegen, wodurch die Größe der Arbeitsmappe und die Aktualisierungszeit erheblich verringert werden.

Bedenken Sie, dass das Erstellen von berechneten Spalten (wie bei der Spalte "Profit") und das anschließende Aggregieren in einer PivotTable oder einem Bericht nicht falsch ist. Es ist tatsächlich eine wirklich gute und einfache Möglichkeit zum Kennenlernen und zum Erstellen eigener Berechnungen. Mit zunehmenden Kenntnissen über diese beiden sehr leistungsfähigen Funktionen von Power Pivot möchten Sie das effizienteste und genaueste Datenmodell erstellen, das möglich ist. Hoffentlich kann Ihnen das hier angeeignete Wissen dabei helfen. Es gibt einige andere, wirklich großartige Ressourcen, die Ihnen ebenfalls helfen können. Hier sind nur ein paar: Kontext in DAX-Formeln, Aggregationen in Power Pivot und DAX-Ressourcencenter. Und obwohl es etwas fortgeschrittener ist und sich an Buchhaltungs- und Finanzexperten richtet, enthält das Beispiel "Modellierung und Analyse von Gewinn- und Verlustdaten mit Microsoft Power Pivot in Excel " eine Vielzahl großartiger Datenmodellierungs- und Formelbeispiele.