Power Pivot: Duplikate in Measures mit SUMX neutralisieren

Publiziert von

Duplikate TitelFür die Ermittlung von intelligenten Durchschnittswerten bietet Power Pivot die mächtigen Iterator-Formeln, wie etwa die sogenannte SUMX-Formel aus der DAX Formelsprache. Diese können unter anderem genutzt werden, um unerwünschte Duplikate in den Measures, wie sie bei der Zusammenführung von Kopf- und Positionstabellen häufig entstehen, zu neutralisieren.

Ausgangssituation

Aus der Zusammenführung der Auftragskopf- und Auftragspositionstabelle aus dem Buchungssystems eines Reisebüros ist folgende – stark vereinfachte – Faktentabelle entstanden (Dimensionstabellen benötigen wir nicht zur Behandlung dieses Sachverhalts in diesem Datenmodell):

1 Faktentabelle

Es kommt also zu einer Vervielfachung des Measures „Anzahl Reisende“ aus der ursprünglichen Auftragskopftabelle entsprechend der Anzahl der Auftragspositionen des jeweiligen Auftrags. Die Auswertung mit einem PivotTable liefert korrekte Ergebnisse für die Umsätze, jedoch völlig falsche Ergebnisse für die „Anzahl Reisende“ (da es insgesamt nur 5 Reisende gibt):

2 Pivotauswertung Summierung

Lösungsansatz: Durchschnittsberechnung

Als ersten Schritt werden anstelle der adhoc-Aggregation im PivotTable die „Nettoerlöse“ und „Anzahl Reisende (Summe)“ explizit als Measures definiert:

    • Nettoerlöse:=Sum([Umsatz])
    • Anzahl Reisende (Summe):=Sum([Anzahl Reisende])
Danach wird als Lösungsversuch eine arithmetische Durchschnittsberechnung auf die Anzahl der Positionen definiert:
    • Anzahl Positionen:=DISTINCTCOUNT([Auftragsposition])
    • Anzahl Reisende (Durchschn.):=[Anzahl Reisende (Summe)]/[Anzahl Positionen]

3 Measures Erster Versuch

Diese Berechnung führt bereits zu korrekten Ergebnissen auf Ebene der einzelnen Aufträge, jedoch ist das arithmetische Mittel auf Gesamtergebnis-Ebene nicht zutreffend (da es ja insgesamt 5 Reisende sind):

4 Pivotauswertung erste Measures

3. Lösungsansatz: Durchschnittsbildung mit Iterator-Formel SUMX

Die Lösung liegt in der Kombination der Durchschnittsberechnung mit einer intelligenten Summierung, dies wird durch die Iterator-Formel SUMX geschafft:

  • Anzahl Reisende (Iterator):=SUMX(
    DISTINCT(Auftragstabelle[Auftragsnummer]);
    [Anzahl Reisende (Summe)]/[Anzahl Positionen]

Die SUMX-Formel ermöglicht eine iterative Durchschnittsberechnung auf Ebene des Auftrages (die DISTINCT-Formel erzeugt eine virtuelle Tabelle mit allen Auftragsnummern), danach werden alle diese Durchschnitte summiert. Wichtig: die DISTINCT-Formel funktioniert nur, wenn auch der Tabellenname vor die Spaltenbezeichnung gestellt wird (dieser wird nicht automatisch beim Klicken im DAX-Formeleditor übernommen, muß also manuell hinzugetippt werden).

Als Draufgabe können wir jetzt noch eine aufbauende betriebswirtschaftliche Kennzahl definieren:

  • Netterlöse pro Reisendem:=[Nettoerlöse]/[Anzahl Reisende (Iterator)]

5 Measure Iterator

Mit dem Iterator-Measure sind jetzt die Aggregationen über alle Stufen bis zur Gesamtsumme korrekt abgebildet (bei entsprechender Filterung etwa auf bestimmte Artikel werden ebenfalls korrekte Ergebnisse ermittelt):

6 Pivotauswertung Measure Iterator

Kritische Würdigung

Neben dem SUMX-Iterator stehen auch noch AVERAGEX, MINX, MAXX, and COUNTAX zur Verfügung. Kritische zu sehen ist die – abhängig vom konkreten Datenmodell und der Datengranularität – rasch abnehmende Performance dieser Berechnungen.

Quellen

http://www.powerpivotpro.com/2010/02/sumx-the-5-point-palm-exploding-fxn-technique/

https://support.office.com/de-de/article/SUMX-Funktion-DAX

Robert Lochner

Robert Lochner ist seit 2001 als Unternehmer tätig und Gründer der Linearis. Seit seinem Betriebswirtschaftsstudium an der WU Wien ist er als Unternehmer in der BI Branche tätig, ist Trainer für Excel BI und Power BI und unterstützt Unternehmen bei der digitalen Transformation ihrer Business Intelligence Prozesse. Er ist Autor des Linearis Blogs und mehrerer fachlicher Publikationen.

Weitere Beiträge

Kategorien: Analyse, Excel als BI Frontend, Power Pivot nutzen