DAX-Szenarien in Power Pivot
Dieser Abschnitt enthält Links zu Beispielen, die die Verwendung von DAX-Formeln in den folgenden Szenarien veranschaulichen.
Komplexe Berechnungen durchführen
Arbeiten mit Text und Daten
Bedingte Werte und Testen auf Fehler
Zeitintelligenz nutzen
Werte einordnen und vergleichen
In diesem Artikel
Einstieg
Besuchen Sie das Wiki des DAX-Ressourcenzentrums , wo Sie alle Arten von Informationen zu DAX finden, darunter Blogs, Beispiele, Whitepaper und Videos, die von branchenführenden Fachleuten und Microsoft bereitgestellt werden.
Szenarien: Durchführen komplexer Berechnungen
DAX-Formeln können komplexe Berechnungen ausführen, die benutzerdefinierte Aggregationen, Filterung und die Verwendung von bedingten Werten umfassen. Dieser Abschnitt enthält Beispiele für die ersten Schritte mit benutzerdefinierten Berechnungen.
Erstellen Sie benutzerdefinierte Berechnungen für eine PivotTable
CALCULATE und CALCULATETABLE sind leistungsstarke, flexible Funktionen, die zum Definieren berechneter Felder nützlich sind. Mit diesen Funktionen können Sie den Kontext ändern, in dem die Berechnung durchgeführt wird. Sie können auch die Art der durchzuführenden Aggregation oder mathematischen Operation anpassen. Beispiele finden Sie in den folgenden Themen.
Wenden Sie einen Filter auf eine Formel an
An den meisten Stellen, an denen eine DAX-Funktion eine Tabelle als Argument verwendet, können Sie normalerweise stattdessen eine gefilterte Tabelle übergeben, indem Sie entweder die FILTER-Funktion anstelle des Tabellennamens verwenden oder einen Filterausdruck als eines der Funktionsargumente angeben. Die folgenden Themen enthalten Beispiele dafür, wie Filter erstellt werden und wie sich Filter auf die Ergebnisse von Formeln auswirken. Weitere Informationen finden Sie unter Daten in DAX-Formeln filtern .
Mit der FILTER-Funktion können Sie Filterkriterien mithilfe eines Ausdrucks angeben, während die anderen Funktionen speziell zum Herausfiltern leerer Werte entwickelt wurden.
Entfernen Sie Filter selektiv, um ein dynamisches Verhältnis zu erstellen
Indem Sie dynamische Filter in Formeln erstellen, können Sie auf einfache Weise Fragen wie die folgenden beantworten:
Wie hoch war der Beitrag des aktuellen Produktumsatzes zum Gesamtumsatz des Jahres?
Wie viel hat diese Sparte im Vergleich zu anderen Sparten zum Gesamtgewinn aller Betriebsjahre beigetragen?
Formeln, die Sie in einer PivotTable verwenden, können vom PivotTable-Kontext beeinflusst werden, aber Sie können den Kontext selektiv ändern, indem Sie Filter hinzufügen oder entfernen. Das Beispiel im Thema ALL zeigt Ihnen, wie Sie dies tun. Um das Verhältnis der Verkäufe für einen bestimmten Wiederverkäufer zu den Verkäufen aller Wiederverkäufer zu ermitteln, erstellen Sie eine Kennzahl, die den Wert für den aktuellen Kontext dividiert durch den Wert für den Kontext ALLE berechnet.
Das Thema ALLEXCEPT enthält ein Beispiel für das selektive Löschen von Filtern für eine Formel. Beide Beispiele führen Sie durch, wie sich die Ergebnisse je nach Design der PivotTable ändern.
Weitere Beispiele zur Berechnung von Verhältnissen und Prozentsätzen finden Sie in den folgenden Themen:
Verwenden eines Werts aus einer äußeren Schleife
Zusätzlich zur Verwendung von Werten aus dem aktuellen Kontext in Berechnungen kann DAX einen Wert aus einer vorherigen Schleife verwenden, um einen Satz verwandter Berechnungen zu erstellen. Das folgende Thema bietet eine exemplarische Vorgehensweise zum Erstellen einer Formel, die auf einen Wert aus einer äußeren Schleife verweist. Die EARLIER-Funktion unterstützt bis zu zwei Ebenen verschachtelter Schleifen.
Weitere Informationen zum Zeilenkontext und verwandten Tabellen sowie zur Verwendung dieses Konzepts in Formeln finden Sie unter Kontext in DAX-Formeln .
Szenarien: Arbeiten mit Text und Datumsangaben
Dieser Abschnitt enthält Links zu DAX-Referenzthemen, die Beispiele für allgemeine Szenarien enthalten, die das Arbeiten mit Text, das Extrahieren und Zusammenstellen von Datums- und Uhrzeitwerten oder das Erstellen von Werten basierend auf einer Bedingung umfassen.
Erstellen Sie eine Schlüsselspalte durch Verkettung
Power Pivot lässt keine zusammengesetzten Schlüssel zu; Wenn Sie also zusammengesetzte Schlüssel in Ihrer Datenquelle haben, müssen Sie diese möglicherweise in einer einzigen Schlüsselspalte kombinieren. Das folgende Thema enthält ein Beispiel dafür, wie eine berechnete Spalte basierend auf einem zusammengesetzten Schlüssel erstellt wird.
Erstellen Sie ein Datum basierend auf Datumsteilen, die aus einem Textdatum extrahiert wurden
Power Pivot verwendet einen Datums-/Uhrzeitdatentyp von SQL Server, um mit Datumsangaben zu arbeiten; Wenn Ihre externen Daten Daten enthalten, die anders formatiert sind – beispielsweise wenn Ihre Daten in einem regionalen Datumsformat geschrieben sind, das von der Power Pivot-Daten-Engine nicht erkannt wird, oder wenn Ihre Daten ganzzahlige Ersatzschlüssel verwenden – können Sie dies tun Sie müssen eine DAX-Formel verwenden, um die Datumsteile zu extrahieren und die Teile dann zu einer gültigen Datums-/Uhrzeitdarstellung zusammenzusetzen.
Wenn Sie beispielsweise eine Spalte mit Datumsangaben haben, die als Ganzzahl dargestellt und dann als Textzeichenfolge importiert wurden, können Sie die Zeichenfolge mithilfe der folgenden Formel in einen Datums-/Uhrzeitwert konvertieren:
=DATUM(RECHTS([Wert1],4),LINKS([Wert1],2),MITTE([Wert1],2))
Wert1 | Ergebnis |
|---|---|
03.01.2009 | 3.1.2009 |
12132008 | 13.12.2008 |
25.06.2007 | 25.6.2007 |
Die folgenden Themen enthalten weitere Informationen zu den Funktionen, die zum Extrahieren und Zusammenstellen von Datumsangaben verwendet werden.
Definieren Sie ein benutzerdefiniertes Datums- oder Zahlenformat
Wenn Ihre Daten Datumsangaben oder Zahlen enthalten, die nicht in einem der standardmäßigen Windows-Textformate dargestellt werden, können Sie ein benutzerdefiniertes Format definieren, um sicherzustellen, dass die Werte korrekt verarbeitet werden. Diese Formate werden beim Konvertieren von Werten in Zeichenfolgen oder von Zeichenfolgen verwendet. Die folgenden Themen enthalten auch eine detaillierte Liste der vordefinierten Formate, die für die Arbeit mit Datumsangaben und Zahlen verfügbar sind.
Ändern Sie Datentypen mithilfe einer Formel
In Power Pivot wird der Datentyp der Ausgabe durch die Quellspalten bestimmt, und Sie können den Datentyp des Ergebnisses nicht explizit angeben, da der optimale Datentyp von Power Pivot bestimmt wird. Sie können jedoch die von Power Pivot durchgeführten impliziten Datentypkonvertierungen verwenden, um den Ausgabedatentyp zu bearbeiten.
Um ein Datum oder eine Zahlenfolge in eine Zahl umzuwandeln, multiplizieren Sie mit 1,0. Die folgende Formel berechnet beispielsweise das aktuelle Datum minus 3 Tage und gibt dann den entsprechenden ganzzahligen Wert aus.
=(HEUTE()-3)*1,0
Um einen Datums-, Zahlen- oder Währungswert in eine Zeichenfolge umzuwandeln, verketten Sie den Wert mit einer leeren Zeichenfolge. Die folgende Formel gibt beispielsweise das heutige Datum als Zeichenfolge zurück.
=""& HEUTE()
Die folgenden Funktionen können auch verwendet werden, um sicherzustellen, dass ein bestimmter Datentyp zurückgegeben wird:
Wandeln Sie reelle Zahlen in ganze Zahlen um
Konvertieren Sie reelle Zahlen, Ganzzahlen oder Datumsangaben in Zeichenfolgen
Konvertieren Sie Zeichenfolgen in reelle Zahlen oder Daten
Szenario: Bedingte Werte und Testen auf Fehler
Wie Excel verfügt DAX über Funktionen, mit denen Sie Werte in den Daten testen und basierend auf einer Bedingung einen anderen Wert zurückgeben können. Beispielsweise könnten Sie eine berechnete Spalte erstellen, die Reseller je nach Jahresumsatz entweder als bevorzugt oder als Wert bezeichnet. Funktionen, die Werte testen, sind auch nützlich, um den Bereich oder Typ von Werten zu überprüfen, um zu verhindern, dass Berechnungen durch unerwartete Datenfehler beschädigt werden.
Erstellen Sie einen Wert basierend auf einer Bedingung
Sie können verschachtelte IF-Bedingungen verwenden, um Werte zu testen und neue Werte bedingt zu generieren. Die folgenden Themen enthalten einige einfache Beispiele für bedingte Verarbeitung und bedingte Werte:
Auf Fehler innerhalb einer Formel testen
Im Gegensatz zu Excel können Sie in einer Zeile einer berechneten Spalte keine gültigen Werte und in einer anderen Zeile ungültige Werte haben. Das heißt, wenn in irgendeinem Teil einer Power Pivot-Spalte ein Fehler auftritt, wird die gesamte Spalte mit einem Fehler gekennzeichnet, sodass Sie immer Formelfehler korrigieren müssen, die zu ungültigen Werten führen.
Wenn Sie beispielsweise eine Formel erstellen, die durch Null dividiert, erhalten Sie möglicherweise das Ergebnis unendlich oder einen Fehler. Einige Formeln schlagen auch fehl, wenn die Funktion auf einen leeren Wert stößt, obwohl sie einen numerischen Wert erwartet. Während Sie Ihr Datenmodell entwickeln, lassen Sie die Fehler am besten erscheinen, damit Sie auf die Meldung klicken und das Problem beheben können. Wenn Sie Arbeitsmappen veröffentlichen, sollten Sie jedoch eine Fehlerbehandlung integrieren, um zu verhindern, dass unerwartete Werte dazu führen, dass Berechnungen fehlschlagen.
Um die Rückgabe von Fehlern in einer berechneten Spalte zu vermeiden, verwenden Sie eine Kombination aus logischen und Informationsfunktionen, um auf Fehler zu testen und immer gültige Werte zurückzugeben. Die folgenden Themen bieten einige einfache Beispiele dafür, wie Sie dies in DAX tun können:
Szenarien: Zeitintelligenz verwenden
Die DAX-Zeitintelligenzfunktionen umfassen Funktionen, mit denen Sie Datumsangaben oder Datumsbereiche aus Ihren Daten abrufen können. Sie können diese Daten oder Datumsbereiche dann verwenden, um Werte für ähnliche Zeiträume zu berechnen. Die Zeitintelligenzfunktionen umfassen auch Funktionen, die mit Standarddatumsintervallen arbeiten, damit Sie Werte über Monate, Jahre oder Quartale hinweg vergleichen können. Sie könnten auch eine Formel erstellen, die Werte für das erste und letzte Datum eines bestimmten Zeitraums vergleicht.
Eine Liste aller Zeitintelligenzfunktionen finden Sie unter Zeitintelligenzfunktionen (DAX) . Tipps zur effektiven Verwendung von Datums- und Uhrzeitangaben in einer Power Pivot-Analyse finden Sie unter Datumsangaben in Power Pivot .
Berechnen Sie den kumulierten Umsatz
Die folgenden Themen enthalten Beispiele zur Berechnung von Schluss- und Eröffnungssalden. Mit den Beispielen können Sie laufende Bilanzen über verschiedene Intervalle wie Tage, Monate, Quartale oder Jahre hinweg erstellen.
Vergleichen Sie die Werte im Laufe der Zeit
Die folgenden Themen enthalten Beispiele zum Vergleichen von Summen über verschiedene Zeiträume hinweg. Die von DAX unterstützten Standardzeiträume sind Monate, Quartale und Jahre.
Berechnen Sie einen Wert über einen benutzerdefinierten Datumsbereich
In den folgenden Themen finden Sie Beispiele zum Abrufen benutzerdefinierter Datumsbereiche, z. B. die ersten 15 Tage nach dem Start einer Verkaufsaktion.
Wenn Sie Zeitintelligenzfunktionen verwenden, um einen benutzerdefinierten Satz von Daten abzurufen, können Sie diesen Satz von Daten als Eingabe für eine Funktion verwenden, die Berechnungen durchführt, um benutzerdefinierte Aggregate über Zeiträume hinweg zu erstellen. Ein Beispiel dafür finden Sie im folgenden Thema:
Hinweis: Wenn Sie keinen benutzerdefinierten Datumsbereich angeben müssen, aber mit Standard-Buchungseinheiten wie Monaten, Quartalen oder Jahren arbeiten, empfehlen wir Ihnen, Berechnungen mit den dafür vorgesehenen Zeitintelligenzfunktionen wie TOTALQTD durchzuführen. TOTALMTD, TOTALQTD usw.
Szenarien: Ranking und Vergleich von Werten
Um nur die obersten n Elemente in einer Spalte oder PivotTable anzuzeigen, haben Sie mehrere Möglichkeiten:
Sie können die Features in Excel 2010 verwenden, um einen Top-Filter zu erstellen. Sie können auch eine Reihe von oberen oder unteren Werten in einer PivotTable auswählen. Im ersten Teil dieses Abschnitts wird beschrieben, wie Sie nach den Top-10-Elementen in einer PivotTable filtern. Weitere Informationen finden Sie in der Excel-Dokumentation.
Sie können eine Formel erstellen, die Werte dynamisch einordnet, und dann nach den Rangfolgewerten filtern, oder den Rangfolgewert als Slicer verwenden. Der zweite Teil dieses Abschnitts beschreibt, wie Sie diese Formel erstellen und diese Rangfolge dann in einem Slicer verwenden.
Jede Methode hat Vor- und Nachteile.
Der Excel-Top-Filter ist einfach zu verwenden, aber der Filter dient nur zu Anzeigezwecken. Wenn sich die der PivotTable zugrunde liegenden Daten ändern, müssen Sie die PivotTable manuell aktualisieren, um die Änderungen anzuzeigen. Wenn Sie dynamisch mit Rankings arbeiten müssen, können Sie DAX verwenden, um eine Formel zu erstellen, die Werte mit anderen Werten innerhalb einer Spalte vergleicht.
Die DAX-Formel ist leistungsfähiger; Darüber hinaus können Sie durch Hinzufügen des Ranking-Werts zu einem Slicer einfach auf den Slicer klicken, um die Anzahl der angezeigten Top-Werte zu ändern. Die Berechnungen sind jedoch rechenintensiv und diese Methode eignet sich möglicherweise nicht für Tabellen mit vielen Zeilen.
Zeigen Sie nur die Top-Ten-Elemente in einer PivotTable an
Zum Anzeigen der oberen oder unteren Werte in einer PivotTable
|
Bestellen Sie Artikel dynamisch mithilfe einer Formel
Das folgende Thema enthält ein Beispiel für die Verwendung von DAX zum Erstellen einer Rangfolge, die in einer berechneten Spalte gespeichert wird. Da DAX-Formeln dynamisch berechnet werden, können Sie immer sicher sein, dass das Ranking korrekt ist, auch wenn sich die zugrunde liegenden Daten geändert haben. Da die Formel in einer berechneten Spalte verwendet wird, können Sie außerdem die Rangfolge in einem Slicer verwenden und dann die Top-5-, Top-10- oder sogar Top-100-Werte auswählen.
No comments:
Post a Comment