Calculate() und Filter()

    Im letzten Artikel haben wir besprochen, wozu man diese Calculate() benötigt. Dort haben wir einen einfachen Periodenvergleich auf ein fest eingegebenes Jahr vorgenommen. Schöner wäre es, könnten wir dynamisch immer auf das vorherige Jahr gehen – das geht in Kombination mit Filter().

    Umsatzvergleich im Vertrieb

    In diesem Artikel greifen wir wieder auf die Daten der AdventureWorks-Datenbank zurück. Wir vergleichen, wie sich der Verkaufsumsatz bei den Verkäufern verglichen mit dem Vorjahr entwickelt hat. Unser Dashboard soll folgendermaßen aussehen:

    Dashboard

     

    Das Datenmodell

    Datenmodell

    Aus der Tabelle SalesOrderHeader erhalten wir alle Aufträge sowie den Nettobetrag (SubTotal). Wir verwenden als Datum das ShippingDate. Der jeweilige Verkäufer ist in der Tabelle SalesPerson hinterlegt, die via SalesPersonId verknüpft ist. Die Tabelle Person benötigen wir, da wir nicht die Nummer des Verkäufers anzeigen möchten, sondern den Namen. Allerdings verwenden wir Person nicht als Dimension, sondern legen auf SalesPerson eine berechnete Spalte mit dem Namen Lastname an. Die Formel lautet einfach:

    =RELATED(Person[LastName])

    Das nur deswegen, weil wir im Slicer später nicht alle Personen haben möchten, sondern nur die Verkäufer.

    Die Kennzahlen

    Die Berechnung des aktuellen Umsatzes ist einfach – allerdings haben wir uns trotzdem für ein berechnetes Feld entschieden, damit wir im Falle von “keinem Umsatz” eine “0” als Wert bekommen (an Stelle von “nichts”):

    Umsatz := IF( ISBLANK( SUM( [SubTotal]) ); 0; SUM([SubTotal]))

    Die Funktion für die Berechnung des Umsatzes aus dem letzten Artikel sah wie folgt aus:

    =CALCULATE(SUM(Umsatz[Umsatz]);YEAR(Calendar[DateKey]) = 2013)

    Dieser bringt uns hier nicht weiter – wir benötigen eher so etwas:

    =CALCULATE(SUM(SalesOrderHeader[SubTotal]);YEAR(Calendar[DateKey]) = YEAR(Calendar[DateKey] – 1))

    Das führt aber zu einem Fehler. Einfache Filter in der Calculate-Funktion müssen immer die Form Spaltenname = <fester Wert> haben. Für unseren Fall benötigen wir die Funktion Filter(), die wir im Artikel über Filter besprochen haben. Unser erster Versuch wäre dann:

    =CALCULATE(SUM(SalesOrderHeader[SubTotal]);Filter(Calendar; Calendar[Year] = Calendar[Year]-1))

    Das kann nicht funktionieren, da der Filter den Benutzerfilter einschränkt. Hat der Benutzer bspw. das Jahr 2007 gewählt, gibt dieser Filter alle Zeilen zurück, die im Jahr 2006 das Jahr 2007 haben – davon kann es keine Zeilen geben. Wir müssen den Benutzerkontext umgehen. Wie? Genau, mit dem All()-Filter:

    =CALCULATE(SUM(SalesOrderHeader[SubTotal]);Filter(All(Calendar); Calendar[Year] = Calendar[Year]-1))

    Dieser Ausdruck gibt immer noch keine Werte zurück. Calendar[Year] = Calendar[Year] – 1 vergleicht potentiell viele Tabellenzeilen. Hat der Benutzer 2007, 2008 und 2009 gewählt, weiß Power Pivot nicht, wie er hier agieren soll. Meines Erachtens müsste dieser Ausdruck einen Fehler werfen – tut er leider nicht. Dem Umstand mit mehreren gewählten Jahren ist leicht beizukommen – wir nehmen einfach das kleinste ausgewählte Jahr und ziehen davon 1 ab:

    =CALCULATE(SUM(SalesOrderHeader[SubTotal]);Filter(All(Calendar); Calendar[Year] = MIN(Calendar[Year])-1))

    Die Krux mit dem Kontext…

    Auch dieser Ausdruck gibt keinen Wert zurück. Zwar gibt der Filter alle Zeilen aus dem Vorjahr zurück – Calculate nimmt aber seine Filter und schränkt damit den aktuellen Benutzerkontext ein. Und der Benutzerkontext steht beispielsweise auf 2007 – der Filter gibt 2006 zurück. Die Schnittmenge ist natürlich leer… Daher müssen wir die Formel wie folgt verändern:

    =CALCULATE(SUM(SalesOrderHeader[SubTotal]); All(Calendar); Filter(All(Calendar); Calendar[Year] = MIN(Calendar[Year])-1))

    Mit dem einem All()-Filter auf Calendar heben wir den Filter auf Calendar auf – der Filter auf einen gewählten Verkäufer bleibt hingegen bestehen! Mit der Schnittmenge auf alle Aufträge des Verkäufers und der Einschränkung auf das Vorjahr haben wir jetzt tatsächlich den Umsatz des Vorjahres:

    Umsatz Vorperiode im Test

    Na ja, zumindest fast. Wir haben die obige Formel in die Kennzahl UmsatzVorjahrTest gepackt, um die obige Formel gegen die “richtige” zu testen. UmsatzVorjahrTest gibt leider immer die Summe des Vorjahres zurück. Der Monat wird nicht berücksichtigt.

    All() mit Spalten

    Die Begründung dafür ist ganz leicht: Wir haben in der Formel den Filter auf das Datum komplett zurückgesetzt, also auch die Monate. Eigentlich wollen wir nur das Jahr zurücksetzen. Daher bietet ALL() auch die Möglichkeit einzelne Spalten aus dem Filter herauszunehmen:

    =CALCULATE(SUM(SalesOrderHeader[SubTotal]); All(Calendar[Year]); Filter(All(Calendar[Year]); Calendar[Year] = MIN(Calendar[Year])-1))

    Dann klappt’s auch mit den Monaten:

    Korrekte Darstellung mit ALL()

    Time Intelligence-Funktionen

    Es geht auch einfacher:

    UmsatzVorjahrTest:=CALCULATE( SUM(SalesOrderHeader[SubTotal]);  SAMEPERIODLASTYEAR(Calendar[DateKey] ))

    Mit Funktionen wie SAMEPERIODLASTYEAR() stellt Power Pivot verschiedene Funktionen zur Verfügung, die intern das Gleiche machen, wie die oben beschriebene Formel. Von diesen Funktionen gibt es viele mehr, auf die wir in zukünftigen Artikeln näher eingehen werden. Wichtig zu verstehen ist aber, dass man sich diese Formeln selbst basteln kann – das ist gerade dann wichtig, wenn man sich nicht auf einen “normalen” Kalender beziehen möchte.

    Kategorien: Funktionen, Kontext, Power Pivot
    Holger Gubbels

    Holger Gubbels ist Spezialist für alles, was mit Business Intelligence zu tun hat. Der Diplom-Informatiker strukturiert und löst als Data Scientist und Berater der mogular GmbH tagtäglich komplexe Problemstellungen rund um die Datenanalyse. Als Dozent der Hochschule für Technik Stuttgart ist er ein ausgewiesener Experte seines Fachs und gewohnt, Inhalte einfach zu erläutern. Genau das macht er auch hier in seinen Beiträgen für das PowerPivotInsights Blog. Zusätzlich ist er Autor für die Computerwoche und den Springer-Verlag. Sie erreichen ihn unter hg@mogular.com

    4 Comments

    Antwort schreiben

    Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.