Einsatz von berechneten Spalten und berechneten Feldern

Wenn Sie zum ersten Mal lernen, wie Power Pivot verwendet wird, stellen die meisten Benutzer fest, dass die eigentliche Leistung beim Aggregieren oder Berechnen von Ergebnisse auf eine bestimmte Weise liegt. Wenn Ihre Daten eine Spalte mit numerischen Werten haben, können Sie sie einfach aggregieren, indem Sie sie in einer PivotTable auswählen oder Power View auswählen. Da es sich um numerische Werte gibt, wird diese automatisch addiert, ihr Mittelwert berechnet, gezählt oder der von Ihnen ausgewählte Aggregationstyp verwendet. Dies wird als implizites Measure bezeichnet. Implizite Measures sind ideal für schnelle und einfache Aggregationen, aber sie verfügen über Grenzwerte, die fast immer mit expliziten Measures und berechneten Spalten überwinden 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 "Product Category" verwendet die RELATED DAX-Funktion, um Werte aus der Spalte "ProductCategoryName" in der verknüpften Tabelle "Product Category" zu erhalten, und gibt dann diese Werte für jedes Produkt (jede Zeile) in der Tabelle "Product" 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 Lernprogrammen. Unser Datenmodell enthält eine Tabelle "Sales", die Transaktionsdaten enthält, und es gibt eine Beziehung zwischen der Tabelle "Sales" und der Tabelle "Product Category". In der Tabelle "Sales" gibt es 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 Sie eine PivotTable erstellen und das Feld "Produktkategorie" nach SPALTEN ziehen, und unser neues 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 Sum of Profit. Es ist eine aggregierte Anzahl von Werten aus der Spalte "Profit" für jede der verschiedenen Produktkategorien. Das Ergebnis sieht wie hier 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 nun eine Spalte namens "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, in dem für jede der Produktkategorien ein Ergebnis berechnet wird. Wenn Sie denken, dass wir den Gewinn für unsere Produktkategorien zweimal berechnet haben, sind Sie richtig. Zuerst haben wir für jede Zeile in der Tabelle "Sales" einen Gewinn berechnet und dann "Profit" zum Bereich WERTE hinzugefügt, in dem er für jede der Produktkategorien aggregiert wurde. Wenn Sie auch denken, dass die berechnete Spalte Profit nicht wirklich erstellt werden muss, sind Sie auch richtig. Wie aber berechnen wir den 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 zuerst in der Tabelle Sales die Spalte "SalesAmount" aus, und klicken Sie dann auf "AutoSum", um ein explizites Measure "Sum of SalesAmount" zu erstellen. Denken Sie daran: Ein explizites Measure wird im Berechnungsbereich einer Tabelle in Power Pivot erstellt. Gleiches gilt für die Spalte "COGS". Wir benennen diese "Total SalesAmount" und "Total COGS" um, damit sie einfacher 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: 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 die neuen Daten in ZEILEN, SPALTEN oder FILTER in einer PivotTable oder auf einer ACHSE, LEGENDE oder KACHELN NACH in einer Power View-Visualisierung angezeigt werden sollen, müssen Sie eine berechnete Spalte verwenden. Wie normale Datenspalten können berechnete Spalten in jedem Bereich als Feld verwendet werden, und wenn es sich um numerische Spalten handelt, 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 folgt nur eine Auswahl: Kontext in DAX-Formeln, Aggregationen in Power Pivot und DAX-Ressourcencenter. Und obwohl es etwas anspruchsvoller und auf Buchhaltungs- und Finanzprofis ausgerichtet ist, enthält das Beispiel Gewinn- und Verlustdatenmodellierung und -analyse mit Microsoft Power Pivot im Excel-Beispiel großartige Beispiele für Datenmodellierung und Formeln.