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.

Über den Autor

Blog auf Feedly abonnieren

Kategorien

Verwandte Beiträge

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

Dashboarding mit Power BI, DAX & Datenmodellierung und Power Query. Drei Einzelmodule oder als ganze Trainingswoche - für Einsteiger und Fortgeschrittene!

Termine 2022

Wien: (7./8. Februar 2022)
und 25.-28 April 2022
Nürnberg: (14./15. Februar 2022)
und 9.-12. Mai 2022

Jetzt buchen und Rabatt sichern.

Jetzt buchen!

Leave a Replay

2 Gedanken zu „DAX-Patterns zur Neukundenermittlung: Measure vs. Column“

  1. Hallo Hr. Lochner,
    ein sehr schöner Beitrag! Ich konnte davon sehr profitieren in meiner PBI Lernkurve, denn genau diese Praxisaspekte gehen vielfach unter vor lauter DAX Begeisterung. Eine Erfahrung möchte ich allerdings hier teilen, denn sie hat mich tatsächlich ein paar Stunden gefuchst bis ich herausfand warum meine CALCULATE(…) Measures so gar nicht mehr wollen.
    Das Detail REPT(UNICHAR(8203),1) & “Existing Contacts” wurde motiviert aufgrund besserer Sortierung, wenn ich das richtig verstanden habe. Ich halte das offen gestanden für keine sehr gute Idee. Sämtliche Suchen auf diesen Strings “New Contact” in den Attributen der Calculated Column “New Customers” scheitern nun aufgrund der hidden spaces, die man hier eingeführt hat. Insbesondere wundert sich der Benutzer dieses so behandelten Measures sehr warum er mittels CALCULATE ( …., ‘Dim Customers'[New Contacts] = “Existing Contacts” ) nichts mehr zu ernten gibt. Mit nicht sichtbaren Zeichen in Spalten würde ich der Anwenderfreundlichkeit zuliebe daher eher sparsam umgehen und im Zweifel schaffe ich es auch selbst mir mit diesen Kunstgriffen ein Bein zu stellen, denn 1 Jahr später habe ich den Trick vergessen und scheitere spätestens dann mit diesem Spezial-String.
    Das aber lediglich meine persönliche Erfahrung. Das mindert keineswegs den sehr gelungenen Weg über die statische Neukundenlösung. Ich fand hierzu bislang lediglich die dynamische Lösung mit allen den von Ihnen herausgearbeiteten Licht- und Schattenseiten. Danke für den Beitrag!

    Antworten
    • Hallo Herr Moosrainer,
      vielen lieben Dank für das erfreuliche und wertvolle Feedback. Die von Ihnen beschriebenen Folgethematiken aufgrund der vorangestellten Zeichen – ich hatte diese tatsächlich nur wegen der Sortierung eingesetzt – waren mir nicht bewußt, leuchten aber absolut ein. Danke für das Teilen Ihrer Erkenntnisse, das bringt uns alle weiter! :)

      Antworten

Schreibe einen Kommentar

Kostenlos zum Newsletter anmelden

Ihre Anfrage

Schicken Sie uns Ihre Fragen und Anregungen!