Fü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):

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):

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])

• Anzahl Positionen:=DISTINCTCOUNT([Auftragsposition])
• Anzahl Reisende (Durchschn.):=[Anzahl Reisende (Summe)]/[Anzahl Positionen]

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):

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)]

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):

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