Einsatz von berechneten Spalten und berechneten Feldern

Beim ersten Erlernen der Verwendung von Power Pivot stellen die meisten Benutzer fest, dass die tatsächliche Leistung darin besteht, ein Ergebnis in irgendeiner Weise zu aggregieren oder zu berechnen. Wenn Ihre Daten über eine Spalte mit numerischen Werten verfügen, können Sie sie ganz einfach aggregieren, indem Sie sie in einer PivotTable oder Power View-Feldliste auswählen. Da sie von Natur aus numerisch ist, wird sie automatisch summiert, gemittelt, gezählt oder ein beliebiger Aggregationstyp, den Sie auswählen. Dies wird als implizites Measure bezeichnet. Implizite Measures eignen sich hervorragend für eine schnelle und einfache Aggregation, weisen jedoch Grenzwerte auf, und diese Grenzwerte können fast immer mit expliziten Measures und berechneten Spalten überwunden werden.

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 Produktkategorieformel verwendet die RELATED DAX-Funktion, um Werte aus der Spalte ProductCategoryName in der zugehörigen Product Category-Tabelle abzurufen, und gibt diese Werte dann für jedes Produkt (jede Zeile) in die Product-Tabelle ein.

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.

Erstellen wir ein weiteres Beispiel, in dem wir eine Gewinnspanne für unsere Produktkategorien berechnen möchten. Dies ist ein gängiges Szenario, auch in vielen Tutorials. Wir haben eine Sales-Tabelle in unserem Datenmodell, die Transaktionsdaten enthält, und es besteht eine Beziehung zwischen der Tabelle Sales und der Tabelle Product Category. In der Tabelle Sales haben wir eine Spalte mit Umsatzbeträgen und eine weitere Spalte mit 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 in SPALTEN und das neue Feld Profit in den Bereich WERTE ziehen (eine Spalte in einer Tabelle in PowerPivot ist ein Feld in der PivotTable-Feldliste). Das Ergebnis ist ein implizites Measure mit dem Namen Summe des Gewinns. Es handelt sich um eine aggregierte Menge von Werten aus der Gewinnspalte 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 Profit erstellt, die eine Gewinnspanne für jede Zeile in der Tabelle Sales berechnet. Anschließend haben wir Profit zum Bereich WERTE unserer PivotTable hinzugefügt und automatisch ein implizites Measure erstellt, bei dem für jede Produktkategorie ein Ergebnis berechnet wird. Wenn Sie der Meinung sind, dass wir den Gewinn für unsere Produktkategorien zweimal berechnet haben, haben Sie Recht. Wir haben zunächst einen Gewinn für jede Zeile in der Tabelle Sales berechnet und dann profit dem Bereich WERTE hinzugefügt, in dem er für jede Produktkategorie aggregiert wurde. Wenn Sie auch denken, dass wir die Spalte "Berechneter Gewinn" nicht wirklich erstellen mussten, haben Sie ebenfalls Recht. Aber wie berechnen wir dann unseren Gewinn, ohne eine berechnete Spalte "Profit" 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 der Tabelle "Sales" erstellen wir ein Measure namens Total Profit ("Gesamtgewinn", um Namenskonflikte zu vermeiden). Am Ende ergeben sich dieselben Ergebnisse wie zuvor, jedoch ohne die berechnete Spalte "Profit".

Wählen Sie zunächst in der Tabelle Sales die Spalte SalesAmount aus, und klicken Sie dann auf AutoSumme, um ein explizites SalesAmount-Measurezu erstellen. Denken Sie daran, dass wir ein explizites Measure im Berechnungsbereich einer Tabelle in Power Pivot erstellen. Gleiches gilt für die COGS-Spalte. Wir benennen diese Total SalesAmount und Total COGS um, damit sie leichter identifiziert werden können.

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: Dividieren Sie das Ergebnis von "Total SalesAmount" durch die Gesamtsumme von "SalesAmount" ohne andere Spalten- oder Zeilenfilter als die, die in der PivotTable definiert sind.

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 Ihre neuen Daten in ZEILEN, SPALTEN oder filtern in einer PivotTable oder auf einer AXIS, LEGEND oder TILE BY in einer Power View-Visualisierung angezeigt werden sollen, müssen Sie eine berechnete Spalte verwenden. Genau wie reguläre Datenspalten können berechnete Spalten als Feld in einem beliebigen Bereich verwendet werden, und wenn sie numerisch sind, können sie auch in VALUES 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 folgt nur eine Auswahl: Kontext in DAX-Formeln, Aggregationen in Power Pivot und DAX-Ressourcencenter. Und obwohl es etwas fortgeschrittener ist und sich an Buchhaltungs- und Finanzexperten richtet, ist das Beispiel für die Modellierung und Analyse von Gewinn- und Verlustdaten mit Microsoft Power Pivot in Excel mit hervorragenden Datenmodellierungs- und Formelbeispielen geladen.