Segmentanalyse mit VALUES()

    Die Auswertung der Auftragsanzahl über zwei oder drei Jahre ist heute Standard. Was aber, wenn die Aufträge völlig unterschiedlich sind? Eine Waschmaschine und eine Tintenpatrone sind einfach zwei Aufträge? Wir zeigen Ihnen, wie man in Power Pivot oder Power BI eine Segmentanalyse durchführt. Und nebenbei lernen Sie die Funktion VALUES() kennen.

    Ausgangslage

    In der Adventure Works Datenbank von Microsoft finden Sie die Tabelle SalesOrderHeader. In dieser Tabelle werden die Auftragsköpfe verwaltet. Drei Spalten sind für den Moment interessant:

    • SalesOrderId – die Auftragsnummer und damit der Schlüssel
    • OrderDate – Das Datum der Bestellung für die zeitliche Einordnung
    • SubTotal – der Nettowert des Auftrags ohne Fracht, Steuern etc.

    Für die zeitliche Einordnung brauchen wir eine Kalendertabelle – die Sie sich, wie in diesem Artikel beschrieben, auch leicht erzeugen lassen können. Unser benötigtes Datenmodell sieht dann wie folgt aus:

    Datenmodell SalesOrderHeader

    Mit diesem Datenmodell lässt sich leicht der Verlauf der Auftragsanzahl darstellen:

    Säulendiagramm mit Auftragsanzahl

    Segmentierung

    Manche Aufträge haben einen Auftragswert von ein paar Euro, manch andere das zehn- oder hundert-fache. Das spiegelt diese Darstellung nicht wieder. Deutlicher wird das, wenn man Auftragssegmente definiert und damit eine Segmentanalyse durchführt.

    Die Segmente definieren wir beispielsweise über eine Tabelle in Excel. Die können Sie dann direkt ins Datenmodell verknüpfen oder via Power Query ins Datenmodell importieren (ich empfehle mittlerweile immer den Weg über Power Query):

    Tabelle mit Segmentdefinitionen

    Ziel ist es, das jeweilige Segment über einen Slicer auszuwählen und die Entwicklung nur für das gewählte Segment zusehen:

    fertige Segmentanalyse als Linien-Chart

    Vorgehen

    Die Anzahl Aufträge muss über ein Measure berechnet werden. Das Measure darf dabei nur die Aufträge berücksichtigen, die durch das Segment eingeschränkt sind. Und der Benutzer darf nur ein Segment ausgewählt haben. Sonst ist nicht klar, welche Aufträge gezählt werden sollen und welche nicht.

    Die Funktion VALUES()

    Hier kommt die Funktion VALUES() ins Spiel. Die Funktion VALUES() gibt eine Liste eindeutiger Werte zurück. Allerdings hat die Funktion noch einen Zusatznutzen: Wenn es nur einen gültigen Wert gibt, dann gibt die Funktion den Wert selbst zurück an Stelle einer Liste mit einer Zeile. Damit dürfen wir folgendes formulieren:

    Anzahl Aufträge segmentiert := COUNTROWS(
    FILTER(
    SalesOrderHeader;
    SalesOrderHeader[SubTotal] > VALUES(Segmente[von])
    && SalesOrderHeader[SubTotal] > VALUES(Segmente[bis])
    )
    )

    Achtung: Die Einschränkung der Intervalle [von;bis] muss überschneidungsfrei sein. Daher muss SalesOrderHeader[SubTotal] GRÖSSER als “von” und KLEINER/GLEICH “bis” sein, damit Aufträge, deren Wert genau auf der Grenze liegen, nicht doppelt gezählt werden.

    HasOneValue()

    Die Formel aus dem vorherigen Abschnitt ist noch nicht ganz korrekt. Wenn der Benutzer im Slicer nicht auf einen Eintrag filtert, liefern die beiden Aufrufe von VALUES() mehr als einen Wert zurück. Die Formel verursacht dann einen Fehler. Wir müssen also sicherstellen, dass der Benutzer nur ein Segment ausgewählt hat. Dazu verwenden wir die Funktion HasOneValue():

    Anzahl Aufträge segmentiert := IF(
    HASONEVALUE(Segmente[ID]);
    COUNTROWS(
    FILTER(
    SalesOrderHeader;
    SalesOrderHeader[SubTotal] > VALUES(Segmente[von])
    && SalesOrderHeader[SubTotal] > VALUES(Segmente[bis])
    )
    );
    BLANK()
    )

    Wählt der Benutzer im Slicer genau einen Wert aus, dann ist die Rückgabe von HASONEVALUE wahr. Dann ist sichergestellt, dass wir via VALUES() auf “von” und “bis” zugreifen können. Hat der Benutzer nichts gewählt, wird einfach BLANK() zurück geliefert.

    Andere Darstellung

    Wir müssen nicht mit einem Slicer arbeiten. Der Slicer verdeutlicht die Funktionsweise nur. Wenn wir das Feld “Display” der Tabelle Segmente in das Chart aufnehmen, erhalten wir folgende Darstellung:

    Segmentanalyse mit allen Segmenten im direkten Vergleich

    Warum klappt das? Weil zu jedem Zeitpunkt, in dem unser Measure ausgeführt wird, das Segment eindeutig bestimmt ist. Oder anders gesagt: Die HASONEVALUE-Funktion gibt TRUE zurück. Denken Sie drüber nach Smile

    Kategorien: Artikel, Funktionen, Power BI, Power Pivot
    Holger Gubbels

    Holger Gubbels ist Spezialist für alles, was mit Business Intelligence zu tun hat. Der Diplom-Informatiker strukturiert und löst als Data Scientist und Berater der mogular GmbH tagtäglich komplexe Problemstellungen rund um die Datenanalyse. Als Dozent der Hochschule für Technik Stuttgart ist er ein ausgewiesener Experte seines Fachs und gewohnt, Inhalte einfach zu erläutern. Genau das macht er auch hier in seinen Beiträgen für das PowerPivotInsights Blog. Zusätzlich ist er Autor für die Computerwoche und den Springer-Verlag. Sie erreichen ihn unter hg@mogular.com

    4 Comments

    Antwort schreiben

    Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.