Wann Sie berechnete Spalten und berechnete Felder verwenden sollten
Beim ersten Erlernen der Verwendung von Power Pivot entdecken die meisten Benutzer, dass die wahre Stärke darin liegt, ein Ergebnis auf irgendeine Weise zu aggregieren oder zu berechnen. Wenn Ihre Daten eine Spalte mit numerischen Werten haben, können Sie sie einfach aggregieren, indem Sie sie in einer PivotTable- oder Power View-Feldliste auswählen. Da es sich um Zahlen handelt, wird es von Natur aus automatisch summiert, gemittelt, gezählt oder welche Art von Aggregation Sie auch immer auswählen. Dies wird als implizites Maß bezeichnet. Implizite Kennzahlen eignen sich hervorragend für eine schnelle und einfache Aggregation, aber sie haben Grenzen, und diese Grenzen können fast immer mit expliziten Kennzahlen und berechneten Spalten überwunden werden .
Sehen wir uns zunächst ein Beispiel an, in dem wir eine berechnete Spalte verwenden, um einen neuen Textwert für jede Zeile in einer Tabelle namens Product hinzuzufügen. Jede Zeile in der Produkttabelle enthält alle Arten von Informationen zu jedem Produkt, das wir verkaufen. Wir haben Spalten für Produktname, Farbe, Größe, Händlerpreis usw. Wir haben eine andere verwandte Tabelle namens Product Category, die eine Spalte ProductCategoryName enthält. Wir möchten, dass jedes Produkt in der Produkttabelle den Produktkategorienamen aus der Produktkategorietabelle enthält. In unserer Produkttabelle können wir eine berechnete Spalte namens Produktkategorie wie folgt erstellen:
Unsere neue Produktkategorieformel verwendet die RELATED DAX-Funktion, um Werte aus der ProductCategoryName-Spalte in der zugehörigen Produktkategorietabelle abzurufen, und gibt diese Werte dann für jedes Produkt (jede Zeile) in die Produkttabelle ein.
Dies ist ein großartiges Beispiel dafür, wie wir eine berechnete Spalte verwenden können, um einen festen Wert für jede Zeile hinzuzufügen, die wir später im Bereich ZEILEN, SPALTEN oder FILTER von PivotTable oder in einem Power View-Bericht verwenden können.
Lassen Sie uns ein weiteres Beispiel erstellen, in dem wir eine Gewinnspanne für unsere Produktkategorien berechnen möchten. Dies ist ein häufiges Szenario, sogar in vielen Tutorials. Wir haben eine Sales-Tabelle in unserem Datenmodell, die Transaktionsdaten enthält, und es besteht eine Beziehung zwischen der Sales-Tabelle und der Product Category-Tabelle. In der Tabelle Sales haben wir eine Spalte mit Verkaufsbeträgen und eine andere Spalte mit Kosten.
Wir können eine berechnete Spalte erstellen, die einen Gewinnbetrag für jede Zeile berechnet, indem wir Werte in der COGS-Spalte von Werten in der SalesAmount-Spalte wie folgt subtrahieren:
Jetzt können wir eine PivotTable erstellen und das Feld Product Category in COLUMNS und unser neues Profit-Feld in den Bereich VALUES ziehen (eine Spalte in einer Tabelle in PowerPivot ist ein Feld in der PivotTable-Feldliste). Das Ergebnis ist eine implizite Kennzahl namens Sum of Profit . Es ist eine aggregierte Menge von Werten aus der Gewinnspalte für jede der verschiedenen Produktkategorien. Unser Ergebnis sieht so aus:
Profit ist in diesem Fall nur als Feld in VALUES sinnvoll. Wenn wir Profit in den Bereich COLUMNS setzen würden, würde unsere PivotTable so aussehen:
Unser Feld "Gewinn" liefert keine nützlichen Informationen, wenn es in den Bereichen COLUMNS, ROWS oder FILTERS platziert wird. Er ist nur als aggregierter Wert im Bereich VALUES sinnvoll.
Wir haben eine Spalte namens Profit erstellt, die eine Gewinnspanne für jede Zeile in der Tabelle Sales berechnet. Anschließend haben wir Profit zum Bereich VALUES unserer PivotTable hinzugefügt und automatisch eine implizite Kennzahl erstellt, bei der ein Ergebnis für jede der Produktkategorien berechnet wird. Wenn Sie denken, dass wir den Gewinn für unsere Produktkategorien wirklich doppelt berechnet haben, liegen Sie richtig. Wir haben zuerst einen Gewinn für jede Zeile in der Tabelle „Sales" berechnet und dann den Gewinn zum Bereich WERTE hinzugefügt, wo er für jede der Produktkategorien aggregiert wurde. Wenn Sie auch denken, dass wir die Spalte „Gewinn berechnet" nicht wirklich erstellen mussten, haben Sie ebenfalls Recht. Aber wie berechnen wir dann unseren Gewinn, ohne eine Gewinn berechnete Spalte zu erstellen?
Profit, wäre wirklich besser als explizites Maß zu berechnen.
Für den Moment belassen wir unsere berechnete Spalte Profit in der Sales-Tabelle und Product Category in COLUMNS und Profit in VALUES unserer PivotTable, um unsere Ergebnisse zu vergleichen.
Im Berechnungsbereich unserer Tabelle „Sales" erstellen wir eine Kennzahl namens „Total Profit" (um Namenskonflikte zu vermeiden). Am Ende werden die gleichen Ergebnisse wie zuvor erzielt, jedoch ohne eine Spalte mit berechnetem Gewinn.
Zuerst wählen wir in der Sales-Tabelle die SalesAmount-Spalte aus und klicken dann auf AutoSum, um eine explizite Sum of SalesAmount- Kennzahl zu erstellen. Denken Sie daran, dass eine explizite Kennzahl eine ist, die wir im Berechnungsbereich einer Tabelle in Power Pivot erstellen. Dasselbe machen wir für die COGS-Spalte. Wir benennen diese in „Total SalesAmount" und „Total COGS" um, um sie leichter identifizieren zu können.
Dann erstellen wir eine weitere Kennzahl mit dieser Formel:
Gesamtgewinn:=[ Total SalesAmount] - [Total COGS]
Hinweis: Wir könnten unsere Formel auch als Total Profit:=SUM([SalesAmount]) - SUM([COGS]) schreiben, aber indem wir separate Kennzahlen Total SalesAmount und Total COGS erstellen, können wir sie auch in unserer PivotTable verwenden, und das können wir Verwenden Sie sie als Argumente in allen möglichen anderen Maßformeln.
Nachdem wir das Format unserer neuen Kennzahl „Gesamtgewinn" in „Währung" geändert haben, können wir sie unserer PivotTable hinzufügen.
Sie können sehen, dass unsere neue Kennzahl „Gesamtgewinn" die gleichen Ergebnisse zurückgibt wie das Erstellen einer berechneten Spalte „Gewinn" und das anschließende Platzieren in VALUES. Der Unterschied besteht darin, dass unsere Gesamtgewinnmessung weitaus effizienter ist und unser Datenmodell sauberer und schlanker macht, da wir zu diesem Zeitpunkt und nur für die Felder berechnen, die wir für unsere PivotTable auswählen. Wir brauchen diese Profit-berechnete Spalte schließlich nicht wirklich.
Warum ist dieser letzte Teil wichtig? Berechnete Spalten fügen dem Datenmodell Daten hinzu, und Daten nehmen Speicherplatz in Anspruch. Wenn wir das Datenmodell aktualisieren, werden auch Verarbeitungsressourcen benötigt, um alle Werte in der Spalte Profit neu zu berechnen. Wir müssen solche Ressourcen nicht wirklich in Anspruch nehmen, da wir unseren Gewinn wirklich berechnen möchten, wenn wir die Felder auswählen, für die wir in der PivotTable Gewinn erzielen möchten, wie z. B. Produktkategorien, Region oder Datum.
Schauen wir uns ein anderes Beispiel an. Eine, bei der eine berechnete Spalte Ergebnisse erzeugt, die auf den ersten Blick korrekt aussehen, aber….
In diesem Beispiel möchten wir die Verkaufsbeträge als Prozentsatz des Gesamtumsatzes berechnen. Wir erstellen eine berechnete Spalte mit dem Namen % of Sales in unserer Sales-Tabelle wie folgt:
Unsere Formel lautet: Dividieren Sie für jede Zeile in der Tabelle „Sales" den Betrag in der Spalte „SalesAmount" durch die SUM-Summe aller Beträge in der Spalte „SalesAmount".
Wenn wir eine PivotTable erstellen und Produktkategorie zu COLUMNS hinzufügen und unsere neue Spalte % of Sales auswählen, um sie in VALUES einzufügen, erhalten wir eine Gesamtsumme von % of Sales für jede unserer Produktkategorien.
OK. Das sieht soweit gut aus. Aber lassen Sie uns einen Slicer hinzufügen. Wir fügen das Kalenderjahr hinzu und wählen dann ein Jahr aus. In diesem Fall wählen wir 2007. Das bekommen wir.
Auf den ersten Blick mag das noch richtig erscheinen. Aber unsere Prozentsätze sollten wirklich 100 % betragen, weil wir den Prozentsatz des Gesamtumsatzes für jede unserer Produktkategorien für 2007 wissen möchten. Was ist also schief gelaufen?
Unsere Spalte „% of Sales" hat für jede Zeile einen Prozentsatz berechnet, der dem Wert in der Spalte „SalesAmount" dividiert durch die Gesamtsumme aller Werte in der Spalte „SalesAmount" entspricht. Werte in einer berechneten Spalte sind fest. Sie sind ein unveränderliches Ergebnis für jede Zeile in der Tabelle. Als wir % of Sales zu unserer PivotTable hinzufügten, wurde es als Summe aller Werte in der SalesAmount-Spalte aggregiert. Diese Summe aller Werte in der Spalte „% of Sales" ist immer 100 %.
Tipp: Lesen Sie unbedingt Kontext in DAX-Formeln . Es bietet ein gutes Verständnis des Kontexts auf Zeilenebene und des Filterkontexts, den wir hier beschreiben.
Wir können unsere berechnete Spalte % des Umsatzes löschen, da sie uns nicht weiterhilft. Stattdessen erstellen wir eine Kennzahl, die unseren Prozentsatz des Gesamtumsatzes korrekt berechnet, unabhängig von angewendeten Filtern oder Datenschnitten.
Erinnern Sie sich an die zuvor erstellte Kennzahl „TotalSalesAmount", die einfach die Spalte „SalesAmount" summiert? Wir haben es als Argument in unserer Kennzahl Gesamtgewinn verwendet und werden es erneut als Argument in unserem neu berechneten Feld verwenden.
Tipp: Das Erstellen expliziter Kennzahlen wie Total SalesAmount und Total COGS ist nicht nur selbst in einer PivotTable oder einem Bericht nützlich, sondern auch als Argumente in anderen Kennzahlen, wenn Sie das Ergebnis als Argument benötigen. Dadurch werden Ihre Formeln effizienter und besser lesbar. Dies ist eine gute Datenmodellierungspraxis.
Wir erstellen eine neue Kennzahl mit der folgenden Formel:
% des Gesamtumsatzes:=([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 in der PivotTable definierten.
Tipp: Lesen Sie unbedingt die Funktionen CALCULATE und ALLSELECTED in der DAX-Referenz.
Wenn wir nun unseren neuen Prozentsatz des Gesamtumsatzes zur PivotTable hinzufügen, erhalten wir:
Das sieht besser aus. Jetzt wird unser Prozentsatz des Gesamtumsatzes für jede Produktkategorie als Prozentsatz des Gesamtumsatzes für das Jahr 2007 berechnet. Wenn wir im Slicer „CalendarYear" ein anderes Jahr oder mehr als ein Jahr auswählen, erhalten wir neue Prozentsätze für unsere Produktkategorien, aber unsere Gesamtsumme beträgt immer noch 100 %. Wir können auch andere Slicer und Filter hinzufügen. Unsere Kennzahl % des Gesamtumsatzes ergibt immer einen Prozentsatz des Gesamtumsatzes, unabhängig davon, ob Datenschnitte oder Filter angewendet werden. Bei Kennzahlen wird das Ergebnis immer gemäß dem Kontext berechnet, der durch die Felder in COLUMNS und ROWS sowie durch alle angewendeten Filter oder Slicer bestimmt wird. Das ist die Macht der Maßnahmen.
Hier sind einige Richtlinien, die Ihnen bei der Entscheidung helfen sollen, ob eine berechnete Spalte oder eine Kennzahl für eine bestimmte Berechnungsanforderung geeignet ist:
Verwenden Sie berechnete Spalten
Wenn Sie möchten, dass Ihre neuen Daten in ZEILEN, SPALTEN oder in FILTERN in einer PivotTable oder auf einer ACHSE, LEGENDE oder TILE BY 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 jedem Bereich verwendet werden, und wenn sie numerisch sind, können sie auch in VALUES aggregiert werden.
Wenn Sie möchten, dass Ihre neuen Daten ein fester Wert für die Zeile sind. Sie haben beispielsweise eine Datumstabelle mit einer Datumsspalte und möchten eine weitere Spalte, die nur die Monatszahl enthält. Sie können eine berechnete Spalte erstellen, die nur die Monatszahl aus den Daten in der Datumsspalte berechnet. Beispiel: =MONAT('Datum'[Datum]).
Wenn Sie einer Tabelle für jede Zeile einen Textwert hinzufügen möchten, verwenden Sie eine berechnete Spalte. Felder mit Textwerten können niemals in VALUES aggregiert werden. Beispiel: =FORMAT('Datum'[Datum],"mmmm") gibt uns den Monatsnamen für jedes Datum in der Datumsspalte in der Datumstabelle.
Maßnahmen anwenden
Wenn das Ergebnis Ihrer Berechnung immer von den anderen Feldern abhängt, die Sie in einer PivotTable auswählen.
Wenn Sie komplexere Berechnungen durchführen müssen, wie z. B. die Berechnung einer Anzahl basierend auf einer Art Filter oder die Berechnung eines Jahresvergleichs oder einer Varianz, verwenden Sie ein berechnetes Feld.
Wenn Sie die Größe Ihrer Arbeitsmappe auf ein Minimum beschränken und ihre Leistung maximieren möchten, erstellen Sie so viele Ihrer Berechnungen wie möglich als Kennzahlen. In vielen Fällen können alle Ihre Berechnungen Messwerte sein, wodurch die Größe der Arbeitsmappe erheblich reduziert und die Aktualisierungszeit verkürzt wird.
Denken Sie daran, dass es nichts Falsches daran gibt, berechnete Spalten zu erstellen, wie wir es mit unserer Profit-Spalte getan haben, und sie dann in einer PivotTable oder einem Bericht zu aggregieren. Es ist tatsächlich eine wirklich gute und einfache Möglichkeit, eigene Berechnungen kennenzulernen und zu erstellen. Mit zunehmendem Verständnis dieser beiden äußerst leistungsstarken Funktionen von Power Pivot werden Sie das effizienteste und genaueste Datenmodell erstellen wollen, das Sie können. Hoffentlich hilft das, was Sie hier gelernt haben. Es gibt einige andere wirklich großartige Ressourcen da draußen, die Ihnen auch helfen können. Hier sind nur einige: Kontext in DAX-Formeln , Aggregationen in Power Pivot und DAX Resource Center . Und obwohl es ein bisschen fortgeschrittener ist und sich an Buchhaltungs- und Finanzexperten richtet, ist das Beispiel „Gewinn- und Verlustdatenmodellierung und -analyse mit Microsoft Power Pivot in Excel" vollgepackt mit großartigen Datenmodellierungs- und Formelbeispielen.
No comments:
Post a Comment