Office: Einsatz von berechneten Spalten und berechneten Feldern

Einsatz von berechneten Spalten und berechneten Feldern
Excel für Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Excel 2013 Developer Power BI Mehr... Weniger Beim ersten Kennenlernen der Verwendung von Power Pivot entdecken die meisten Benutzer die wirkliche Macht in der Aggregation oder Berechnung eines Ergebnisses in irgendeiner Weise. 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 ein numerisches Element handelt, wird es von Natur aus automatisch summiert, gemittelt, gezählt oder von der ausgewählten Aggregations ausgewählt. Dies wird als implizites Measure bezeichnet. Implizite Measures eignen sich hervorragend für die schnelle und einfache Aggregation, aber Sie haben Grenzwerte, 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 Formel für Produktkategorien verwendet die Verwandte DAX-Funktion, um Werte aus der Spalte ProductCategoryName in der Tabelle zugehörige Produktkategorie abzurufen, und gibt dann diese Werte 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 Lernprogrammen. Wir verfügen über eine Tabelle "Sales" in unserem Datenmodell, die Transaktionsdaten enthält, und es besteht eine Beziehung zwischen der Tabelle "Sales" und der Tabelle "Produktkategorie". In der Tabelle "Sales" gibt es eine Spalte mit Verkaufsbeträgen und eine andere Spalte, die Kosten aufweist.

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 "Gewinn" in den Bereich "Werte" (eine Spalte in einer Tabelle in PowerPivot ist ein Feld in der PivotTable-Feldliste) ziehen. Das Ergebnis ist eine implizite Kennzahl mit dem Namen Summe der Gewinne. Es handelt sich um eine aggregierte Menge von Werten aus der Spalte Profit 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.

Was wir getan haben, ist die Erstellung einer Spalte mit dem Namen Profit, die eine Gewinnspanne für jede Zeile in der Tabelle "Sales" berechnet. Anschließend haben wir dem Wertebereich unserer PivotTable Profit hinzugefügt, wobei automatisch ein implizites Measure erstellt wird, bei dem ein Ergebnis für jede der Produktkategorien berechnet wird. Wenn Sie der Meinung sind, dass wir den Profit für unsere Produktkategorien zwei Mal richtig berechnet haben, sind Sie richtig. Wir haben zuerst einen Gewinn für jede Zeile in der Tabelle "Sales" berechnet, und dann haben wir den Wertbereich addiert, in dem er für jede der Produktkategorien aggregiert wurde. Wenn Sie auch denken, dass wir die berechnete Profit Spalte nicht wirklich erstellen müssen, sind Sie ebenfalls richtig. Wie berechnen wir dann aber unseren Profit, ohne eine berechnete Profit Spalte 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".

Zunächst wählen wir in der Tabelle "Sales" die Spalte SalesAmount aus, und klicken Sie dann auf AutoSumme, um eine explizite Summe des SalesAmount- Measures zu erstellen. Beachten Sie, dass eine explizite Kennzahl im Berechnungsbereich einer Tabelle in Power Pivot erstellt wird. Das gleiche gilt für die Spalte "Rädchen". Wir werden diese Gesamt SalesAmount und die Gesamtzahl der Zahnräder umbenennen, damit Sie leichter zu erkennen sind.





Dann wird ein weiteres Measure mit dieser Formel erstellt:

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

Hinweis: Die Formel könnte auch als "Total Profit:=SUM([SalesAmount]) – SUM([COGS])" eingegeben werden, aber durch das Erstellen der separaten Measures "Total SalesAmount" und "Total COGS" können diese auch in der PivotTable verwendet werden. Zudem können sie in allen Arten von anderen Formeln mit Measures als Argumente verwendet werden.

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 %.

Tipp: Achten Sie darauf, den Kontext in DAX-Formelnzu lesen. Dieses Thema bietet eine gute Erläuterung zum Zeilenebenenkontext und Filterkontext, die hier beschrieben werden.

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.

Tipp: Das Erstellen expliziter Measures wie "Total SalesAmount" und "Total COGS" ist in einer PivotTable oder einem Bericht nicht nur selbst sinnvoll, sondern es stellt auch hilfreiche Argumente in anderen berechneten Feldern bereit, wenn das Ergebnis als Argument erforderlich ist. Dadurch sind Ihre Formeln effizienter und leichter lesbar. Dies ist eine geeignete Vorgehensweise bei der Datenmodellierung.

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.

Tipp: Lesen Sie unbedingt Informationen zu Calculate -und allselected -Funktionen im DAX-Bezug.

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 Sie möchten, dass die neuen Daten in Zeilen, Spalten oder in Filtern in einer PivotTable oder auf einer Achse, einer Legende oder, Kacheln in einer Power View-Visualisierung, angezeigt werden, müssen Sie eine berechnete Spalte verwenden. Genau wie normale Datenspalten können berechnete Spalten als Feld in einem beliebigen Bereich verwendet werden, und wenn Sie numerisch sind, können Sie auch in Werten 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 fortschrittlicher ist und sich an Buchhalter und Finanzfachleute richtet, sind die Ergebnisse der Datenmodellierung und-Analyse mit Microsoft Power Pivot in Excel -Beispiel mit tollen Daten Modellierungs-und Formel Beispielen geladen.

:)