DAX-Patterns zur Neukundenermittlung: Measure vs. Column

--- Coronazeit ist Bloggingzeit ... wir hoffen, daß es Ihnen allen gut geht und daß dieser Blogpost ein wertvoller Beitrag für alle Power BI User zur Nutzung der Zeit im Homeoffice ist ---

In Power BI kann mittels DAX Statement ermittelt werden, welche Kunden in einer Periode Neukunden sind oder welche auch schon in früheren Perioden Umsätze hatten und damit Bestandskunden sind. Im Wesentlichen steht eine sehr dynamische Methode auf Basis eines Measures sowie eine statische Methode auf Basis einer Calculated Column zur Verfügung. Beide DAX Statements sind anspruchsvoll aber mit dem Pattern als Vorlage gut handhabbar.

Die dynamische Methode hat trotz der beeindruckenden Berechnungsleistung zahlreiche praktische Nachteile. Dazu kommen noch Einschränkungen aus dem Umstand heraus, daß ein Set von Measures (Neu- und Bestandskunden) geringere Visualisierungsmöglichkeiten hat als eine Column (mit den Einträgen Neu- und Bestandskunden). Insofern gelten für diesen Blogbeitrag auch die in Columns vs. Measures: Grundlagen zur Visualisierung mit Power BI festgestellten Sachverhalte.

1. Ausgangssituation

Ziel ist es, die Umsatzentwicklung eines Unternehmens nach Neukunden und Bestandskunden aufzugliedern:

Das zugrundliegende Datenmodell ist ein klassisches Star Schema, das DAX-Pattern für die Neukundenermittlung spielt sich zwischen den beiden Tabellen Fact Sales und Dim Customers ab:

Folgendes Measure ist bereits angelegt:

Sales = 
    SUMX(
        'Fact Sales';
        'Fact Sales'[Order Quantity] * Related('Dim Products'[List Price])
    )

Weiters ist die Tabelle Dim Dates als Datumstabelle deklariert, um in weiterer Folge die Quick Measures Funktion nutzen zu können.

2. Pattern zur dynamischen Neukundenermittlung (Measure)

Für die dynamische Neukundenentwicklung steht in Power BI Desktop das Quick Measure Sales from new customers zur Verfügung. Dazu werden das Measure Sales, das Feld Customer Key und das Feld Date als Parameter hinzugefügt:

Das folgende DAX Statement wurde vom Quick Measures Wizard erzeugt und anschließend nachbearbeitet. Es wurde lediglich die Funktion KEEPFILTERS() entfernt, da diese in Verbindung mit VALUES() keine Funktion hat, die Variablen wurden umbenannt und die Zeilenumbrüche wurden etwas verändert. Die Kommentarzeilen (beginnend mit "//") erläutern die Funktion des jeweiligen Bereichs:

Sales - New Customers = 
VAR var_ListOfCustomers_ThisPeriod =
// Ermittlung der Customers Liste, die in der aktuell selektierten Periode Umsätze hat
	FILTER(
		VALUES('Fact Sales'[Customer Key]);
		NOT ISBLANK([Sales])
	)

VAR var_ListOfCustomers_PriorPeriods =
// Filterung der obigen  Liste auf jene Customers, die auch vor der selektierten Periode bereits Umsätze hatte
	FILTER(
		var_ListOfCustomers_ThisPeriod;
		NOT ISBLANK(
            CALCULATE(
                [Sales];
                FILTER(
                    ALL('Dim Dates'[Date]);
                    'Dim Dates'[Date] < MIN('Dim Dates'[Date])
                )
            )
		)
	)

RETURN
	CALCULATE(
        [Sales];
        EXCEPT(
        // Filterung der aktuellen Customers Liste auf diejenigen Einträge, die in der zweiten Liste nicht vorkommen
            var_ListOfCustomers_ThisPeriod;
            var_ListOfCustomers_PriorPeriods
        )
    )
Sales - Existing Customers = [Sales] - [Sales - New Customers]

Mit den beiden Measures werden die folgende Visualisierungen erstellt. Dabei fällt auf, daß die Neukundenermittlung im Column Chart offenbar anders nach anderen Regeln funktioniert als im darunterliegenden Matrix Visual, dort werden sämtliche Umsätze als Sales - New Customers gewertet:

Bei aktivem Jahresfilter ergibt sich zwar im Matrix Visual ein plausibles Bild ...

... aber die Summe der Monatsbeträge im Chart entsprechen noch immer nicht dem Jahresbetrag, was die folgende Aufstellung zweifelsfrei zeigt:

Die Ursache für dieses nicht additive Verhalten liegt in der sehr flexiblen Berechnung des oben gezeigten DAX-Statements: auf Jahresebene wird der gesamte Jahresumsatz eines neuen Kunden als Sales - New Customers gewertet, während auf Monatsebene nur der erste Monatsumsatz als Sales - New Customers gilt und die Folgemonate dann nicht mehr. Der gleiche Effekt tritt auch bei der Ermittlung auf Tagesebene und bei der Ermittlung ganz ohne Zeitbezug auf.

Die Berechnung ist also hoch-dynamisch und sehr leistungsfähig, in der Praxis macht die Nicht-Additivität aber tendenziell Probleme bei der Akzeptanz der Berechnung.

3. Pattern zur statischen Neukundenermittlung (Calculated Column)

Die statische Neukundenermittlung findet auf Ebene der Faktentabelle statt. Calculated Columns werden nur beim Import bzw. beim Refresh des Datenmodells aktualisiert, daher auch die allgemein verbreitete Bezeichnung statisch. Das folgende DAX-Statement für eine Calculated Column prüft pro Customer Key, ob bis zum Vorjahr bereits Umsätze vorhanden sind oder nicht. Dabei kann auch das erste Jahr gezielt behandelt werden, da hier die Neukundenermittlung noch nicht möglich ist (hier: Klassifizierung als "Existing Customers").

New Customers = 
// Definition: Neukunde ist, wer in den Jahren zuvor noch keinen Umsatz gehabt hat (jahresweise Ermittlung)
// Hinweis: für die Ermittlung wird das Konzept der Context Transition genutzt
VAR var_Customer = 'Fact Sales'[Customer Key]
VAR var_CurrentYear = YEAR('Fact Sales'[Order Date])
VAR var_FirstYear = YEAR(MIN('Fact Sales'[Order Date]))
VAR var_CurrentValue = CALCULATE(
        [Sales];
        FILTER(
            'Fact Sales';
            'Fact Sales'[Customer Key] = var_Customer
            && YEAR('Fact Sales'[Order Date]) < var_CurrentYear
            )       
        )

RETURN
    IF(var_CurrentYear = var_FirstYear;REPT(UNICHAR(8203);2) & "Existing Customers";
    IF(var_CurrentValue = BLANK();REPT(UNICHAR(8203);1) & "New Customers";
    REPT(UNICHAR(8203);2) & "Existing Customers"))
    // Das REPT(UNICHAR(8203);x) sorgt für die gewünschte Sortierreihenfolge der Texte

Das FILTER()-Statement nutzt das Prinzip der sogenannten Context Transition, bei der der aktuelle Row Context als Filterparameter für die Ermittlung der Umsätze bis zum Vorjahr verwendet wird. Die Variablen var_Customer und var_CurrentYear bewerkstelligen im Wesentlichen diese Parameterübergabe.

Das Statement erzeugt die Calculated Column New Customers in der Tabelle Fact Sales:

Die Visualisierung ist jetzt ohne weitere Erklärungen sofort aussagekräftig, da die Berechnung in der statischen Ermittlung additiv ist:

Die Summe der Monatsbalken entspricht auch bei aktivem Jahresfilter der ausgewiesenen Gesamtsumme ...

... was sich mit der Monatstabelle auch leicht überprüfen lässt:

4. Measure vs. Column

Wie bereits in unserem Blogbeitrag Columns vs. Measures: Grundlagen zur Visualisierung mit Power BI diskutiert, ist die Abbildung von Kategorien über ein Set von Measures tendenziell ein Nachteil in Power BI. Erstens stehen weniger Visualisierungsmöglichkeiten zur Verfügung und zweites kann über Measures kein Crossfilter gesetzt werden. Konkret bedeutet das im vorliegenden Fall, daß im Column Chart nicht ein Teilbalken selektiert werden kann sondern nur ein ganzes Monat:

Die Abbildung von Kategorien über eine Column eröffnet in der Visualisierung alle Optionen und auch der Crossfilter kann auf Teilbalken gesetzt werden (natürlich kann alternativ auch hier das ganze Monat mit beiden Balken selektiert werden):

5. Vergleich und Fazit

Nach unserer bisherigen Erfahrung ist die dynamische Neukundenermittlung zwar mathematisch beeindruckend und gut geeignet für die Präsentation der Leistungsfähigkeit von DAX und Power BI, für den Praxisbetrieb eines Dashboards ist die Nicht-Additivität aber mehr ein Fluch als ein Segen.

Die statische Neukundenermittlung hat den Vorteil der Additivität und damit der Analysefähigkeit vom obersten Aggregat bis auf den einzelnen Datensatz. Weiters stehen für eine Column mehr Visualisierungsmöglichkeiten als für ein Set an Measures zur Verfügung, die feingliedrige Crossfilter-Fähigkeit rundet das Set an Vorteilen noch ab. Der Nachteil von Calculated Columns besteht darin, daß die Berechnung nicht zur Laufzeit geändert bzw. parametrisiert werden kann, was in diesem Fall aber auch nicht relevant ist.

Ihre Erfahrungen?

Teilen Sie uns über den Kommentarbereich dieser Seite Ihre Erfahrungen zur Neukundenermittlung in Power BI mit uns und den anderen Lesern dieses Blogs.

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!

Die Termine für 2020 sind bereits abgeschlossen, nächste Termine voraussichtlich im Frühjahr 2021.

Gruppen ab 4 Teilnehmer können auch im Rahmen eines Inhouse Training ausgebildet werden.
Termine 2021
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!