Durchschnittsumsatz über alle Jahre in Power BI berechnen

Ich berechne in diesem Beitrag den Durchschnittsumsatz für alle Jahre. Ich werde Ihnen zunächst verschiedene Versuche präsentieren, die anfangs scheitern und schlussendlich immer näher zu Lösung hinführen. Am Ende dieses Artikels werde ich Ihnen die meiner Ansicht nach beste Lösung aufzeigen.

Das Datenmodell

In unserem Datenmodell gibt es eine Tabelle Bestellungen und eine Datumstabelle namens Kalender. Beide Tabellen sind über eine 1-zu-N-Beziehung miteinander verbunden.

Der Umsatz

Wir berechnen den Umsatz, indem wir die Spalte Gesamtpreis aus den Bestellungen aufaddieren.

Umsatz = SUM(Bestellungen[Gesamtpreis])

In der folgenden Matrix sehen Sie die Umsätze pro Jahr:

Durchschnittsumsatz im Säulendiagramm ausgeben

Wir können in Power BI den Durchschnittsumsatz auch ganz ohne Formeln berechnen. In einem gruppierten Säulendiagramm platzieren wir dazu das Jahr auf der X-Achse und den Umsatz auf der Y-Achse. Dann fügen wir über den Analysebereich (Lupensymbol) einfach eine Durchschnittslinie ein und aktivieren die Datenbeschriftung.

Der Durchschnittsumsatz liegt im aktuellen Beispiel bei 8.837,57 €. Die Frage ist nun, wie wir dieses Ergebnis über ein Measure berechnen können.

Berechnung des Durchschnittsumsatzes

Im Folgenden werde ich verschiedene Vorgehensweisen testen, um den Durchschnittsumsatz für die Jahre aus den Bestellungen zu berechnen. Dabei werde ich zuerst Verfahren anwenden, die nicht zum Ziel führen, um schlussendlich zu verschiedenen Lösungen zu kommen. Beachten Sie bitte, dass ich in den folgenden Visualisierungen das Feld Jahr immer aus der Datumstabelle Kalender nehme!

1. Versuch: Durchschnittsumsatz mit AVERAGE

Ich fange erst einmal ganz einfach an und verwende die Standardfunktion für Durchschnitt: AVERAGE. Hier mein Measure:

Durchschnittsumsatz = AVERAGE(Bestellungen[Gesamtpreis])

Wie Sie sehen, sind die Ergebnisse in der Spalte Durchschnittsumsatz komplett falsch:

Doch warum tut diese Kennzahl nicht das, was wir wollen? Um dies zu verstehen, müssen wir uns mit dem Filterkontext beschäftigen. Jede Zeile der Visualisierung erzeugt eine Filterbedingung, welche Power BI auf das Datenmodell anwendet. Erst nach der Filterung führt Power BI die eigentlichen Rechenoperationen durch. Schauen wir uns das einmal für das Jahr 2015 an:

Dieser Filterkontext bewirkt, dass Power BI die Tabelle Bestellungen auf alle Zeilen filtert, bei denen das Bestelldatum aus dem Jahr 2015 ist. Dies sind insgesamt 81 Zeilen. Der Umsatz für diese Bestellungen liegt bei 7.050,30 €. Was Power BI nun rechnet, ist also 7.050,30 : 81 = 87,0407…

Das Problem ist also nicht, dass Power BI falsch rechnet. Es tut tatsächlich genau das, was wir ihm in der Formel vorgegeben haben. Power BI sieht jedoch nicht die richtigen Zeilen im Modell. Und daher ist das Ergebnis falsch.

Damit das wirklich klar wird, schauen wir uns nun noch die Gesamtzeile an:

Hier sieht Power BI also alle 500 Zeilen aus den Bestellungen. Der Gesamtumsatz liegt bei 53.025,40 €. Das Programm rechnet also 53.025,40 : 500 = 106,0508.

2. Versuch: Durchschnittsumsatz mit sehr simpler Berechnung

Um den Durchschnittsumsatz pro Jahr korrekt zu ermitteln, müssen wir uns Folgendes überlegen:

In unserem Beispiel gibt es 6 Jahre. Der Gesamtumsatz liegt bei 53.025,40 €. Die richtige Berechnung muss also lauten: 53.025,40 : 6 = 8.837,5666…

Das richtige Ergebnis würde ich also erhalten, wenn ich den Gesamtumsatz immer durch die 6 Jahre teile:

Durchschnittsumsatz = [Umsatz] / 6

In der Gesamtzeile erhalten wir nun das richtige Ergebnis. Allerdings ist der Durchschnittsumsatz für alle anderen Zeilen in der Matrix falsch. Außerdem steht die Zahl 6 statisch in der Formel. Wir müssten sie also händisch anpassen, wenn Bestellungen aus weiteren Jahren dazukommen. Dies ist daher keine gute Lösung.

3. Versuch: Durchschnittsumsatz mit CALCULATE und ALL

Wir können den Filterkontext so manipulieren, dass Power BI immer alle 500 Zeilen aus den Bestellungen sieht. Dies erreichen wir mit der Funktion ALL. Wenn wir mit ALL den Filterkontext für das Feld Kalender[Jahr] aufheben, dann erhalten wir in allen Zeilen den richtigen Durchschnittsumsatz:

Auch diese Lösung ist noch nicht gut, weil das Programm den Durchschnittsumsatz nun für alle Jahre der Datumstabelle anzeigt. Wir sehen jetzt viele Jahre, in denen es überhaupt keinen Umsatz gab. Diese Jahre sollen selbstverständlich nicht im Bericht auftauchen. Darüber hinaus ist die Zahl 6 in der Berechnung immer noch statisch.

4. Versuch: Überflüssige Zeilen mit IF und ISBLANK ausblenden

Um die überflüssigen Jahre aus der Matrix zu entfernen, können wir mit den Funktionen IF und ISBLANK einfach prüfen, ob es einen Umsatz gab. Wenn nein, dann unterdrücken wir die Ausgabe der Zeilen mit der Funktion BLANK.

Durchschnittsumsatz = 
IF(
    ISBLANK([Umsatz]),
    BLANK(),
    CALCULATE(
        [Umsatz] / 6,
        ALL(Kalender[Jahr])
    )
)

Dieser Ansatz blendet die überflüssigen Zeilen tatsächlich aus. Allerdings haben wir das Problem mit der statischen Zahl in der Division noch nicht gelöst. Daher ist dieses Measure natürlich auch noch nicht zufriedenstellend.

5. Versuch: Anzahl der relevanten Jahre dynamisch berechnen

Wir könnten nun auf die Idee kommen, mit DISTINCTCOUNT die Jahre aus der Datumstabelle zu zählen. Dies würde jedoch immer zum falschen Ergebnis führen, da der Kalender einen Zeitraum abdeckt, der viel größer ist als jener in den Bestellungen. Dieser Ansatz scheidet also aus.

In diesem Versuch fahren wir daher ganz schwere Geschütze auf und filtern die Datumstabelle auf den relevanten Zeitraum. Dazu ermitteln wir mit den Funktionen MIN und MAX zunächst einmal das kleinste und das größte Bestelldatum. Dann erzeugen wir mit der Funktion SUMMARIZE eine Zusammenfassungstabelle nach Kalender[Jahr], welche wir anschließend mit der Funktion FILTER auf den Zeitraum zwischen dem größten und dem kleinsten Bestelldatum filtern. Mit der Funktion COUNTX zählen wir dann die Zeilen dieser Zusammenfassungstabelle, wodurch wir auf unsere 6 Jahre kommen. Zuletzt führen wir noch die bereits bekannte Berechnung durch, diesmal jedoch mit der dynamisch errechneten Anzahl der Jahre.

So eine Zusammenfassungstabelle können wir uns sehr gut im DAX Studio anschauen. Dazu führen wir eine DAX-Abfrage aus. Eine solche Abfrage muss mit dem Schlüsselwort EVALUATE beginnen. Im Ergebnis sehen wir eine Spalte mit allen Jahren aus der Datumstabelle. SUMMARIZE entfernt dabei sämtliche Duplikate, sodass jedes Jahr nur einmal in der Tabelle steht.

Hier das komplette Measure:

Durchschnittsumsatz = 
VAR vMinDatum = CALCULATE(MIN(Bestellungen[Bestelldatum]), ALL(Kalender[Jahr]))
VAR vMaxDatum = CALCULATE(MAX(Bestellungen[Bestelldatum]), ALL(Kalender[Jahr]))

VAR vAnzJahre = 
COUNTX(
    FILTER(
        SUMMARIZE(
            ALL(Kalender),
            Kalender[Jahr]
        ),
        Kalender[Jahr] >= YEAR(vMinDatum) &&
        Kalender[Jahr] <= YEAR(vMaxDatum)
    ),
    Kalender[Jahr]
)

RETURN
IF(
    ISBLANK([Umsatz]),
    BLANK(),
    CALCULATE(
        [Umsatz] / vAnzJahre,
        ALL(Kalender)
    )
)

Das Ergebnis ist korrekt:

Wir haben alle überflüssigen Jahre aus der Matrix ausgeblendet und den Teiler in der Division dynamisch berechnet. Theoretisch könnten wir nun einen Haken hinter die Aufgabe setzen, doch wirklich zufrieden bin ich mit dieser Lösung immer noch nicht. Wir haben sehr viele Berechnungen, in denen Power BI die Tabelle Bestellungen durchlaufen muss, um die Zwischenergebnisse zu errechnen. Zudem ist die Formel sehr lang und für Einsteiger nur schwer zu verstehen. Also schauen wir weiter, ob es nicht doch noch eine bessere Lösung gibt.

Das fertige Measure: Durchschnittsumsatz mit AVERAGEX

Wir können die Formel erheblich vereinfachen, indem wir die erweiterte Standardfunktion zum Berechnen des Durchschnittswertes verwenden: AVERAGEX. Diese Funktion erlaubt es uns, eine Tabelle und einen Ausdruck anzugeben. Die Tabelle wird die Zusammenfassungstabelle der Kalenderjahre sein, welcher wir eine zusätzliche Spalte mit den Gesamtumsätzen hinzufügen. Im Ausdruck legen wir fest, dass die Funktion den Durchschnitt über diese Umsätze berechnen soll.

Durchschnittsumsatz = 
IF(
    ISBLANK([Umsatz]),
    BLANK(),
    AVERAGEX(
        ADDCOLUMNS(
            SUMMARIZE(
                ALL(Kalender),
                Kalender[Jahr]
            ),
            "Wert",
            [Umsatz]
        ),
        [Wert]
    )
)

Die Ergebnisse unterscheiden sich nicht von denen des vorherigen Measures. Allerdings ist die Berechnung kürzer und einfacher zu verstehen.

Beachten Sie bitte, dass wir die Umsatzspalte mithilfe der Funktion ADDCOLUMNS an die Zusammenfassungstabelle anfügen. SUMMARIZE kann zwar auch selbstständig berechnete Spalten anfügen, dies ist mittlerweile aber nicht mehr empfohlen. Einen sehr lesenswerten Artikel zu ADDCOLUMNS und SUMMARIZE finden Sie bei SQLBI: Best practices using SUMMARIZE and ADDCOLUMNS.

Die fertige Power BI Desktop-Datei können hier kostenfrei herunterladen:

Fazit

Den Durchschnittsumsatz über alle Jahre zu berechnen ist nicht ganz einfach. Sie müssen das Konzept des Filterkontextes verstehen, um zu erkennen, warum eine einfache Berechnung mit AVERAGE nicht zum richtigen Ergebnis führt. Die gute Nachricht ist jedoch, dass der Filterkontext ein relativ einfach zu verstehendes Prinzip ist. Es geht letztendlich darum, welche Zeilen Power BI im Datenmodell findet. Sie sollten darüber hinaus die Funktionen SUMMARIZE und ADDCOLUMNS kennen, mit denen Sie Zusammenfassungstabellen erstellen und Spalten an diese anfügen können. Beschäftigen Sie sich außerdem mit den erweiterten Aggregatfunktion wie AVERAGEX. Dann sollte eine Aufgabe wie der Durchschnittsumsatz über alle Jahre für Sie eine machbare Herausforderung darstellen.