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:

Genau das gleiche macht die Funktion SUMX():

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

Gleichzeitig können wir auch folgende Measure verwenden:

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:

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:

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:

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.