Unterschied SUM und SUMX

    In unseren Schulungen werden wir häufig gefragt, was denn der Unterschied zwischen SUM und SUMX ist und wann man welche Funktion verwendet. Häufig genug, um mal einen Artikel darüber zu schreiben.

    Syntax von SUM() und SUMX()

    Beide Funktionen zählen was zusammen. SUM addiert die Werte einer Spalte, beispielsweise die Spalte ‘SalesOrderDetail’[LineTotal. Also der Gesamtwert einer Auftragsposition in der Adventure Works Datenbank:

    PosTotal := SUM(SalesOrderDetail[LineTotal])

    Genau das gleiche macht die Funktion SUMX():

    PosTotal := SUMX(SalesOrderDetail; SalesOrderDetail[LineTotal])

    Das bedeutet, dass die beiden Funktionen bis hierhin gleichwertig sind.

    Beispiel: Mehrwertsteuer

    Für unser Beispiel stehen uns Umsätze aus Kundenaufträgen zur Verfügung (Tabelle ‘Umsätze’):

    Beispieldaten mit Umsätzen

    Der Betrag steht in Netto zur Verfügung. Um die Mehrwertsteuer zu berechnen und zu summieren, können wir folgendes Measure verwenden

    MwSt_SUM:=ROUND( SUM(‚Umsätze'[Netto]) * 0,19 ;2)

    Gleichzeitig können wir auch folgende Measure verwenden:

    MwSt_SUMX:=SUMX(‚Umsätze‘; ROUND(‚Umsätze'[Netto] * 0,19;2))

    Beides als Währung formatiert ergibt folgendes (unterschiedliches) Ergebnis:

    Unterschied zwischen SUM und SUMX in Excel Pivot Tabelle

    Bisschen gewollt?

    Ja, zugegeben, das Beispiel ist etwas gewollt. Der Unterschied sticht aber ins Auge: das Measure mit SUM aggregiert die Netto-Spalte und rundet dann auf zwei Stellen. Das Measure mit SUMX rechnet für jede Zeile die Mehrwertsteuer, rundet sofort und aggregiert am Ende.

    Das ist einer der Unterschiede zwischen SUM und SUMX: SUM aggregiert eine Spalte. Mehr kann die Funktion nicht. SUMX läuft über jede Zeile der im ersten Parameter angegebenen Tabelle und führt für jede Zeile die im zweiten Parameter angegebene Formel aus. Das bedeutet SUMX verhält sich im Grunde so, als ob man eine berechnete Spalte angelegt, dort die Mehrwertsteuer berechnet (und rundet) und die Spalte am Ende beispielsweise mit SUM aggregiert hätte. Damit ist SUMX eine Iterator-Funktion.

    Iterator-Funktionen

    Alle Funktionen, die mit einem X enden sind Iterator-Funktionen. Diese werden immer Zeilenweise ausgeführt. Das bedeutet, dass man in der Formel (der zweite Parameter) genau das gleiche tun kann, wie in einer berechneten Spalte. Nur das die Spalte eben nicht als berechnete Spalte im Datenmodell abgelegt wird. FILTER ist übrigens auch eine Iterator-Funktion. Auch wenn kein X am Ende steht!

    Wenn man in dem zweiten Parameter eines Iterators, also in der Expression, das gleich tun kann, wie in einer berechneten Spalte, dann kann man auch mit RELATED oder RELATEDTABLE arbeiten?

    Beispiel mit Iterator

    Wir möchten alle Produkte herausfinden, mit denen wir mehr als 100 EUR Umsatz gemacht gemacht haben. Vorliegen haben wir zwei Tabellen: Product und SalesOrderDetail:

    Datenmodell in Excel Power Pivot oder Power BI

    Erster Impuls: Wir legen eine berechnete Spalte in Product mit dem Namen Umsatz an, in der wir den erzielten Umsatz des Produkts errechnen. Das klappt prima mit SUMX und RELATEDTABLE:

    =SUMX(RELATEDTABLE(SalesOrderDetail); SalesOrderDetail[LineTotal])

    Kurz zur Erinnerung: RELATEDTABLE() filtert alle Zeilen der verbundenen Tabelle, die mit der aktuellen Zeile in Beziehung stehen. Würde man das RELATEDTABLE weglassen, wäre das Ergebnis in jeder Zeile von Product identisch. Es stände überall die Gesamtsumme aller Umsätze.

    Ein Measure für die Auswertung

    Jetzt legen wir ein Measure an, in dem wir alle Produkte zählen, die mehr als 100 EUR Umsatz hatten:

    [#Products > 100EUR] := COUNTROWS(FILTER(Product;Product[Umsatz] > 100))

    Auch hier wieder ein Iterator: FILTER(). Filter geht jede Zeile der Tabelle im ersten Parameter durch und gibt die Zeile nur dann zurück, wenn für die aktuelle Zeile die Bedingung im zweiten Parameter wahr ist.

    Und das geht auch in einem Schritt

    Oben habe ich geschrieben, dass ein Iterator Zeile für Zeile durchläuft und die Expression im zweiten Parameter ausführt. Die Expression arbeitet also auf einer Zeile. Und damit kann ich mich in dieser Expression verhalten, als wäre ich in einer berechneten Spalte. Damit kann ich das Measure auch ohne berechnete Spalte formulieren:

    [#Products>100EUR] := COUNTROWS(FILTER(Product; SUMX(RELATEDTABLE(SalesOrderDetail); SalesOrderDetail[LineTotal]) > 100))

    Same Same but different

    De Variante mit der berechneten Spalte plus Measure ist nicht identisch wie die Variante nur mit Measure. Die Formel einer berechneten Spalte wird im Datenmodell bei einer Aktualisierung ausgeführt. Das bedeutet, dass für ein Produkt berechnet wird, wieviel Umsatz es generiert hat. Führt man jetzt beispielsweise einen Datumsfilter ein, um herauszufinden, wie viele Produkte nur im aktuellen Jahr mehr 100 EUR generiert haben, ändert sich das Ergebnis nicht. Der Datumsfilter bleibt wirkungslos. Die berechnete Spalte wurde bereits im Datenmodell abgelegt. Und das unabhängig eines Filters in einer Pivot-Tabelle oder Slicer. Du kannst es auch so sehen: Die berechnete Spalte verhält sich so, als ob du die Daten bereits aus der Datenquelle importiert hättest.

    Verwendet man hingegen die Variante nur mit Measure, dann hat der Filter Einfluss. Dann filtert SUMX zwar auf RELATEDTABLE. Wenn der Datumsfilter SalesOrderDetail aber einschränkt, erhält SUMX weniger Zeilen zurück.

    Unterschied SUM und SUMX?

    Eigentlich kann man sagen, dass man SUM nicht benötigt, weil man alles mit SUMX machen kann. Es gibt auch Artikel, die versucht haben einen Performance-Unterschied festzustellen.

    Ich halte es so: Wenn mir SUM genügt, verwendet ich SUM. Das ist meistens der Fall, wenn ich SUM in Zusammenhang mit der CALCULATE-Funktion verwende. Filterungen auf Tabellen, die ich bei SUMX im ersten Parameter anwenden würde, benötige ich in Kombination mit CALCULATE nicht. Da verwende ich SUM. Und sonst verwende ich ich immer SUMX.

    Weitere Links

    Kategorien: Artikel, Funktionen, Grundlagen, Kontext, Power BI, 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

    Antwort schreiben

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