Power BI: Durchschnittspreise aus kumulierten Daten berechnen

Startseite » Allgemein » Power BI: Durchschnittspreise von kumulierten Umsätzen und Mengen berechnen

Ein Leser hat mir über meinen Frage-Service folgendes Anliegen zukommen lassen:

Hallo Herr Trummel,

ich bin bei der Suche nach einer Lösung für mein Power BI Problem auf ihre Seite und Hilfsangebot gestoßen – tolle Sache!

Ich hoffe sehr, dass sie mir hier helfen können 😊

Ausgangslage:

Faktentabelle mit Menge und Umsatz pro Kunde, pro Artikel und pro Datum

Ziel:

Pro Monat soll jeweils der Durchschnittspreis (kumulierter Umsatz : kumulierte Menge) des aktuellen Jahres mit dem des Vorjahres verglichen werden.

Es sollen in der Kalkulation nur Artikel berücksichtig werden, die sowohl im aktuellen wie auch im Vorjahr (kumulierte) Durchschnittspreise haben (Ausschluss Produkt-Mix-Effekt).

Zudem soll gleichzeitig ein Kunden-Mix-Effekt ausgeschlossen werden, indem die Berechnung pro Kunde durchgeführt wird und die Ergebnisse für jede verfügbare Kombination aus Kunde + Artikel danach summiert werden.

Mein Problem:

Die Berechnung pro Zeile (Kunde/Artikel) zeigt ein korrektes Ergebnis an, in den Zeilenzwischensummen sind dann aber immer auch die Umsätze, die auf Kunden/Artikel-Ebene eigentlich ausgeschlossen sein sollten mit enthalten.

Für ihre Mühe und Unterstützung bedanke ich mich schon jetzt!

Die Beispieldaten

Schauen wir uns folgende Beispieldaten einmal an:

Excel-Tabelle mit den Beispieldaten, in welchen bestimmte Zeilen gelb hervorgehoben sind. — In den Beispieldaten sehen Sie bereits, welche Zeilen unsere Berechnung berücksichtigen muss.

Sie können die Beispieldaten als Excel-Datei über den folgenden Link kostenfrei herunterladen. (Die fertige Power BI Desktop-Datei finden Sie zum Herunterladen am Ende dieses Beitrags.)

Beispieldaten für Durchschnittspreise.xlsx Herunterladen

Wir haben die Kunden 1,2 und 3 sowie die Artikel A, B und C. Uns liegen Umsätze aus den Jahren 2023 und 2024 vor. In der Tabelle habe ich bereits einmal händisch die Zeilen gelb hervorgehoben, für welche es im Vorjahr entsprechende Umsätze gab (wo also der gleiche Kunde schon mal den gleichen Artikel gekauft hatte).

Kennzeichnung der Datensätze mit einer Spalte

Nachdem ich die Daten in Power BI Desktop eingelesen habe, habe ich die relevanten Zeilen mithilfe einer berechneten Spalte gekennzeichnet.

In der Tabelle in Power BI Desktop ist die Spalte "Kennzeichnung" grün umrandet. — Wir erstellen eine Spalte, um alle relevanten Datensätze zu kennzeichnen.

Hier die Formel:

Kennzeichnung = 
//Variablen
VAR vArtikel = 'Umsätze'[Artikel]
VAR vKunde = 'Umsätze'[Kunde]
VAR vJahr = RELATED(Kalender[Jahr])
VAR vMonat = RELATED(Kalender[Monat])
RETURN
//Formelergebnis
COUNTROWS(
    FILTER(
        'Umsätze',
        'Umsätze'[Artikel] = vArtikel &&
        'Umsätze'[Kunde] = vKunde &&
        RELATED(Kalender[Jahr]) = vJahr - 1 &&
        RELATED(Kalender[Monat]) = vMonat
    )
)

Hier müssen wir beachten, dass Power BI die Formel im Zeilenkontext ausführt. Das bedeutet, dass Power BI die Tabelle schrittweise von oben nach unten durchläuft und in jeder Zeile die Berechnung durchführt. Nun einmal die Formel im Detail erklärt:

Wir speichern uns erst einmal den Artikel, den Kunden, das Jahr und den Monat der jeweiligen Zeile in Variablen ab. Anschließend filtern wir die gesamte Tabelle mithilfe der FILTER-Funktion auf diejenigen Zeilen, in denen der gleiche Kunde den gleichen Artikel im gleichen Monat ein Jahr vorher (vJahr - 1) schon mal gekauft hatte. Die nach der Filterung übrig gebliebenen Zeilen zählen wir dann mit der Funktion COUNTROWS.

Übrigens verwende ich in meinem Modell eine Kalendertabelle. Dies ist generell zu empfehlen. Sie sollten in jedem Projekt eine Kalendertabelle verwenden. Mithilfe der Funktion RELATED greife ich auf die Spalten Jahr und Monat dieser Kalendertabelle zu. Das funktioniert, weil es eine Beziehung zwischen den beiden Tabellen gibt:

Datenmodell von Power BI, in welchem die Kalendertabelle mit der Tabelle „Umsätze“ verbunden ist. — Im Datenmodell von Power BI ist die Tabelle „Umsätze“ mit einer Kalendertabelle verbunden.

Berechnung der Durchschnittspreise mit einem Measure

Mit dem folgenden Measure berechne ich nun die Durchschnittspreise:

Durchschnittspreis = 
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            FILTER(
                'Umsätze',
                'Umsätze'[Kennzeichnung] > 0
            ),
            Kalender[Jahr],
            Kalender[Monat],
            'Umsätze'[Kunde],
            'Umsätze'[Artikel]
        ),
        "Durchschnittspreisberechnung",
        DIVIDE(
            CALCULATE(SUM('Umsätze'[Umsatz])),
            CALCULATE(SUM('Umsätze'[Menge]))
        )
    ),
    [Durchschnittspreisberechnung]
)

Schauen wir uns die einzelnen Bestandteile der Formel einmal an:

Filterung der Tabelle mit FILTER

Mithilfe der Funktion FILTER reduzieren wir die Tabelle Umsätze auf jene Zeilen, bei denen unsere Spalte Kennzeichnung einen Wert erzeugt hat, der größer ist als 0. Mit anderen Worten stellen wir also sicher, dass wir nur jene Zeilen betrachten, bei denen - wie gefordert - der gleiche Kunde den gleichen Artikel im gleichen Monat des Vorjahres gekauft hat.

Im DAX-Studio kann ich die gefilterte Tabelle sehr schön sichtbar machen:

EVALUATE
FILTER(
    'Umsätze',
    'Umsätze'[Kennzeichnung] > 0
)

Hier das Ergebnis:

Ausschnitt aus dem DAX Studio, in welchem die Tabelle „Umsätze“ mit der Funktion FILTER gefiltert wurde. — Im DAX-Studio können wir uns die gefilterte Tabelle sehr schön vor Augen führen.

Eine Zusammenfassungstabelle mit SUMMARIZE erstellen

Anschließend erzeugen wir mit der Funktion SUMMARIZE eine Zusammenfassungstabelle, welche nach Jahr, Monat, Kunde und Artikel gruppiert ist. Dies ist wichtig, um die Umsätze und Mengen kumulieren zu können. Das ist notwendig, da ein Kunde in einem Monat den gleichen Artikel ja auch mehrfach kaufen kann.

Anschaulich wird dies alles, wenn wir uns die Zusammenfassungstabelle einmal anschauen. Dazu führe ich die Anweisungen wieder im DAX Studio aus:

EVALUATE
SUMMARIZE(
	FILTER(
	    'Umsätze',
	    'Umsätze'[Kennzeichnung] > 0
	),
	Kalender[Jahr],
	Kalender[Monat],
	'Umsätze'[Kunde],
	'Umsätze'[Artikel]
)

Hier das Ergebnis im DAX Studio:

DAX-Abfrage im DAX Studio, welche die Zusammenfassungstabelle erzeugt. — Im DAX Studio können wir die Zusammenfassungstabellen sehen.

Durchschnittspreis mit ADDCOLUMNS an Zusammenfassungstabelle anfügen

Wie wir sehen, gruppiert Power BI die Daten nach Jahr, Monat, Kunde und Artikel.

Nun können wir mit der Funktion ADDCOLUMNS eine berechnete Spalte an diese Zusammenfassungstabelle anfügen. Wir addieren jeweils einmal die Umsätze und die Mengen auf und teilen beide Summen dann durcheinander.

Wichtig ist, dass wir für diese Berechnung einen Filterkontext erzeugen, damit wir auch wirklich nur diejenigen Zeilen der Umsatz-Tabelle aufaddieren, die tatsächlich zu den jeweiligen Zeilen unsere Zusammenfassungstabelle gehören.

Anhand folgender Grafik möchte ich einmal am Beispiel der ersten Zeile der Zusammenfassungstabelle verdeutlichen, was ich damit meine:

Zeigt für die 1. Zeile der Zusammenfassungstabelle den Filterkontext und daneben in der Umsatz-Tabelle diejenigen Zeilen, die davon betroffen sind. — Die 1. Zeile der Zusammenfassungstabelle soll nur die beiden markierten Zeilen in der Umsatz-Tabelle „sehen“

Zur besseren Veranschaulichung führe ich auch diese Berechnung wieder im DAX Studio durch und werde zusätzlich noch die kumulierten Umsätze und Mengen als einzelne Spalten ausgeben.

EVALUATE
ADDCOLUMNS(
    SUMMARIZE(
        FILTER(
            'Umsätze',
            'Umsätze'[Kennzeichnung] > 0
        ),
        Kalender[Jahr],
        Kalender[Monat],
        'Umsätze'[Kunde],
        'Umsätze'[Artikel]
    ),
    "Kumulierter Umsatz", 
    CALCULATE(SUM('Umsätze'[Umsatz])),
    "Kumulierte Menge",
    CALCULATE(SUM('Umsätze'[Menge])),
    "Durchschnittspreisberechnung",
    DIVIDE(
        CALCULATE(SUM('Umsätze'[Umsatz])),
        CALCULATE(SUM('Umsätze'[Menge]))
    )
)

Hier die Ergebnisse im DAX Studio:

DAX-Abfrage im DAX Studio, in welcher an die Zusammenfassungstabelle weitere Spalten angefügt werden. — Im DAX Studio sehen wir, dass wir mit ADDCOLUMNS weitere Spalten an die Zusammenfassungstabelle anfügen können.

(Hinweis: die kumulierten Umsätze und Mengen muss wir im Measure nicht separat ausrechnen. Im DAX Studio erleichtert es jedoch das Verständnis, wenn ich diese Spalten ausgebe.)

Den Filterkontext erzeugen wir, indem wir die Berechnungen in ein CALCULATE-Funktion einfügen.

In der folgenden Abbildung zeige ich, was passieren würde, wenn wir CALCULATE weglassen. Sie sehen, dass wir nun für jede Zeile der Zusammenfassungstabelle dasselbe Ergebnis erhalten (was natürlich falsch ist).

EVALUATE
ADDCOLUMNS(
    SUMMARIZE(
        FILTER(
            'Umsätze',
            'Umsätze'[Kennzeichnung] > 0
        ),
        Kalender[Jahr],
        Kalender[Monat],
        'Umsätze'[Kunde],
        'Umsätze'[Artikel]
    ),
    "Kumulierter Umsatz", 
    SUM('Umsätze'[Umsatz]),
    "Kumulierte Menge",
    SUM('Umsätze'[Menge]),
    "Durchschnittspreisberechnung",
    DIVIDE(
        SUM('Umsätze'[Umsatz]),
        SUM('Umsätze'[Menge])
    )
)

Hier das Ergebnis im DAX Studio:

Ausschnitt aus dem DAX-Studio, in welchem ich fälschlicherweise CALCULATE weggelassen habe und daher auf falsche Ergebnisse kommen. — Ohne CALCULATE erhalten wir die falschen Ergebnisse!

Da wir ein Measure erzeugen, brauchen wir eine Aggregatfunktion, mit welcher wir bestimmte Werte aus der Zusammenfassungstabelle ausgeben können. Hierfür nutzen wir die Funktion SUMX. Sie durchläuft die Zusammenfassungstabelle Zeile für Zeile und addiert die Werte in der Spalte Durchschnittspreisberechnung auf.

Der fertige Power BI-Bericht

Im Bericht müssen wir dann noch den richtigen Filterkontext erzeugen. Hierfür verwende ich einmal einen Datenschnitt, in welchem ich auf das Jahr 2024 filtere. In einer Matrix lege ich den Monat in die Zeile und den Kunden in die Spalten.

Hier sehen Sie das Ergebnis:

Bericht in Power BI, welcher über einen Datenschnitt auf das Jahr 2024 gefiltert ist. Eine Matrix zeigt die Durchschnittspreise für Monate und Kunden. — Im Bericht erzeugen wir mithilfe der Matrix und eines Datenschnittes den Filterkontext.

Die fertige Power BI Desktop-Datei können Sie über folgenden Link kostenfrei herunterladen:

Durchschnittspreise mit Power BI.pbix Herunterladen