Power Pivot: Duplikate in Measures mit SUMX neutralisieren

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

Power BI Camp - Präsenztrainings in Wien und Nürnberg!

DAX & Datenmodellierung, Power Query und Dashboarding mit Power BI - als einzelnes Modul oder als ganze Trainingswoche!

Nürnberg 20. bis 24. April 2020
Wien 04. bis 08. Mai 2020
Early Bird!
Share on linkedin
Share on twitter
Share on facebook

Leave a Replay

Über den Autor

Blog auf Feedly abonnieren

Kategorien

Verwandte Beiträge

Kostenlos zum Newsletter anmelden

Ihre Anfrage

Schicken Sie uns Ihre Fragen und Anregungen!