Wie Sie Pareto Analysen in Power BI erstellen

    Pareto Charts sind nützliche Darstellungen. Sie zeigen, welche 20% des Aufwands 80% der Ergebnisse erzielen. So helfen sie beispielsweise in der Qualitätssicherung oder bei der Bewertung von Produkten oder Kunden. Die Erstellung ist leider nicht ganz einfach. In diesem Artikel zeigen wir Ihnen detailliert, wie das in Power BI funktioniert.

    Das Ziel

    In der Adventure Works Datenbank befinden sich in der Tabelle SalesOrderDetail die verkauften Waren sowie der Umsatz. Jedes Produkt gehört zu eine Produkt-Kategorie. Wir wollen wissen, mit wie vielen und vor allem welchen Produkt-Kategorien wir 80% unseres Umsatzes tätigen – dargestellt als Pareto-Diagramm:

    Pareto Chart

    Das Datenmodell

    Aus der Adventure Works Datenbank nehmen wir die Tabellen ProductSubcategory, Product und SalesOrderDetail und verknüpfen die Tabellen zu folgendem Modell:

    Datenmodell aus Adventure Works

    Die Tabelle Product ist nur zur Zuordnung zwischen SalesOrderDetail und ProductSubcategory gut – da sie sonst nicht weiter benötigt wird, habe ich alle anderen Felder nicht importiert.

    Wie gehe ich vor?

    Zunächst benötigen wir die Summe des getätigten Umsatzes pro ProductSubcategory. Das ist eigentlich einfach, denn wir müssen nur LineTotal in ein Säulendiagramm einfügen und den Namen der Kategorie als Achse. Für die Kurve, die den relativen Anteil am Gesamtumsatz darstellen soll, funktioniert das aber nicht. Wir benötigen eine Maßzahl, die alle Umsätze sortiert in der Reihenfolge ihrer Größe.

    Das bedeutet:

    1. Wir gruppieren SalesOrderDetail nach ProductSubcategory
    2. Die Gruppen erhalten ein Ranking – die größte Summe Platz 1, die zweitgrößte Platz 2 usw.
    3. Wir erstellen eine Maßzahl, die alle Umsätze der Gruppen addiert, deren Ranking kleiner ist als das aktuell betrachtete (wir verwenden das Ranking als Sortierung)
    4. Wir setzen diese neue Maßzahl ins Verhältnis zum Gesamtumsatz

    Tabellen gruppieren

    Maßzahlen in DAX dürfen nur skalare Werte zurückgeben, niemals Listen oder gar Tabellen. In Power BI kann man aber eine Tabelle anlegen über eine DAX-Formel, die eben eine ganze Tabelle zurück gibt. In der Datensicht in Power BI Desktop findet man unter Modellierung eine entsprechende Funktion:

    Tabelle anlegen in Power BI

    Die Tabelle wird mit DAX gebildet, wie wenn man eine Measure anlegt. Wir erhalten daher die entsprechende Formel für die Tabelle:

    DAX zur Anlage einer Tabelle

    Eine Gruppierung funktioniert in DAX über die Funktion SUMMARIZE. Die Formel ist eigentlich ganz einfach:

    SUMMARIZE(

    • <Welche Tabelle soll gruppiert werden>;
    • <Nach welchem Feld soll gruppiert werden>;
    • <Wie ist der Name des neuen Gruppen-Feldes>;
    • <Mit welcher Aggregatfunktion soll der Wert des neuen Guppenfeldes berechnet werden

    )

    Da die Tabellen SalesOrderDetail und ProductSubcategory verknüpft sind, dürfen wir schreiben:

    LineTotalGrouped := SUMMARIZE(SalesOrderDetail; ProductSubcategory[ProductSubcategoryID]; “Summe”; SUM(SalesOrderDetail[LineTotal]))

    Ranking für Umsätze erstellen

    In der neuen Tabelle LineTotalGrouped haben wir jetzt den Umsatz pro ProductSubcategory. Für das Ranking legen wir in dieser neuen Tabelle eine neue Spalte an:

    UmsatzRanking := RANKX(LineTotalGrouped; LineTotalGrouped[Summe])

    Die Funktion RANKX vergibt dazu einfach den Wert 1 für den höchsten Wert, 2 und mehr für die entsprechend folgenden.

    Zwischenergebnis

    Zunächst verbinden wir unsere neue Tabelle LineTotalGrouped mit ProductSubcategory

    Datenmodell mit neuer Tabelle

    Die beiden Tabellen Product und SalesOrderDetail sind für die weitere Betrachtung nicht mehr relevant.

    In einen Power BI Bericht legen wir eine Visualisierung vom Typ “Linien- und gestapeltes Säulendiagramm” an. Als gemeinsame Achse nehmen wir den Namen der Kategorie, als Wert das Feld Summe:

    Pareto Chart Versuch 1

    Damit die größten Umsatzbringer vorne sind können wir nach Umsatz oder eben nach unserem neuen Feld UmsatzRanking sortieren.

    Cumulative Total

    Die Sortierung ist natürlich wichtig für die neue Maßzahl. Wir wollen Umsätze kumulieren – aber eben nicht über die Zeit sondern über die Produkt-Kategorien. Nur warum sollten die Kategorien Road Bikes, Mountain Bikes und Touring Bikes bei der Betrachtung der Kategorie Touring Bikes kumulieren? Ganz einfach, weil wir in Reihenfolge des größten Umsatzes absteigend kumulieren möchten. Und genauso definieren wir die Maßzahl:

    CumulativeTotal := CALCULATE(
    SUM(LineTotalGrouped[Summe]);
    ALL(LineTotalGrouped);
    FILTER(ALL(LineTotalGrouped); LineTotalGrouped[UmsatzRanking]<= MAX(LineTotalGrouped[UmsatzRanking]))
    )

    Falls die Formel nicht ganz selbst erklärend sein sollte, schauen Sie sich die Artikel über DAX im Blog an!

    Noch eine Maßzahl: Das Verhältnis

    Die Maßzahl CumulativeTotal ist prima, nur möchten wir nicht den absoluten Wert, sondern den Anteil der addierten Umsätze zum Gesamtumsatz. Daher definieren wir den Anteil als:

    RelativeCumulativeTotal := DIVIDE( [CumulativeTotal];[TotalSum];BLANK())

    Und die Maßzahl formatieren wir im Menü noch als Prozentsatz.

    Das Ergebnis

    Das Ergebnis haben wir weiter oben ja bereits gesehen. In einem anderen Werkzeug habe ich noch eine rote Linie eingefügt, um die 80% deutlicher zu kennzeichnen:

    Pareto Chart - mit 80% Marke

    Man sieht also, dass Adventure Works bereits mit knapp über 2 Produkt-Kategorien mehr als 80% des Gesamtumsatzes tätigt.

    Und man sieht auch, dass in der Visualisierung bei Power BI die Möglichkeit fehlt eine solche Trendlinie einzuzeichnen, die es dem Betrachter die Einschätzung erleichtern. Aber: Was ist nicht kann ja noch kommen Smile

    Kategorien: Artikel, Power BI, Tipps
    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

    Antwort schreiben

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