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 verwendet folgende Parameter:

  • 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:

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:

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:

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:

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 .