Flexible ABC Analyse mit Excel Power Pivot und DAX

Eine klassische Methode im Bereich des Controllings ist sicherlich die ABC-Analyse, auch Paretoprinzip oder 80/20-Regel genannt. Das Paretoprinzip beschreibt ein statistisches Phänomen, bei dem eine kleine Anzahl hoher Werte (Kategorie A)  mehr zum Gesamtwert beiträgt als eine große Anzahl kleiner Werte (Kategorie C). Vilfredo Pareto (1848 – 1923)  entdeckte dieses Prinzip, als er die Bodenverteilung in Italien untersuchte. Er fand heraus, daß ca. 20 % der Bevölkerung ca. 80 % des Bodenbesitzes besitzen.

Im Folgenden soll mit Hilfe von Excel Power Pivot (ein Mitglied der Microsoft Power BI Familie) und DAX Formeln (Data Analysis Expressions) ein Weg aufgezeigt werden, wie eine flexible ABC-Analyse (Materialgruppenübergreifend oder je Materialgruppe) auf Basis von Artikelumsätzen (Einkaufsvolumen, EVO) realisiert werden kann. Dabei werden die Artikel mit den Kategorien A (80 %), B (15%) sowie C (5%) im Modell gekennzeichnet, so daß Anzahl Artikel und Umsätze (EVO) bei Bedarf aggregiert nach Materialgruppe dargestellt werden können.

„Aber das kann ich doch mit der Pivottabelle auch schon machen!“ werden Sie jetzt vielleicht sagen. Richtig ist, daß man mit der klassischen Pivottabelle den kumulierten Anteil in % ausweisen kann. Dazu muß ein Wertfeld lediglich über die Wertfeldeinstellungen, Reiter „Werte anzeigen als“ auf die Option „% von Ergebnis in“ umgestellt werden. Soweit so gut, nur was ist mit der Zuweisung der Klasse A, B, C? Wie kann man nach der Klassifizierung gruppieren? Und weiter, wie stellt man die Anzahl der Artikel nicht als einzelne Elemente sondern als Aggregat dar?

Diese kleine aber entscheidende Lücke im klassischen Pivot Modell kann man durch Anwendung eines Excel Power Pivot Modells schließen.

Als Datenstruktur dient folgende Excel Tabelle (Datenstruktur):

Über den Reiter „Power Pivot“ -> zu Datenmodell hinzufügen werden die Daten im tabellarischen Modell abgebildet.

Als berechnete Felder (measures) werden

Anzahl Artikel:=COUNTROWS() sowie

EVO:=SUM([EVOArtikel]) angelegt.

Danach wird eine neue berechnete Spalte EVOJeMaterialgruppe mit folgender DAX Formel ergänzt

CALCULATE (Ausdruck,Filter) benötigt als ersten Parameter eine Aggregatfunktion, hier Ausdruck =measure = [EVO] = SUM([EVOArtikel]) = Aggregatfunktion SUM() und wertet diese unter der angegebenen Filterbedingung aus. Filterbedingung ist eine weitere Funktion namens ALLEXCEPT(Tabelle; Feld). Es werden alle Filter ignoriert, außer der Filter auf das Feld [Materialgruppe].

Im Ergebnis wird je Zeile innerhalb einer Materialgruppe die Summe des Feldes [EVOArtikel] ausgegeben.

Jetzt wird es etwas tricky. Wir erweitern unser Modell um eine berechnete Spalte [EVOJeMaterialgruppeAnteil%]:

Was passiert hier?

Die DAX Funktion EARLIER() sorgt dafür, dass innerhalb einer Materialgruppe ein Ranking nach dem Feld [EVOArtikel] erstellt wird. Rangwert 1 wird ins Verhältnis zum Feld [EVOJeMaterialgruppe] gesetzt. Rangwert 2 wird mit dem Vorgänger Rangwert addiert und ins Verhältnis gesetzt usw. Somit erhält man eine kumulierte Summe, welche je Zeile ins Verhältnis zum Feld [EVOJeMaterialgruppe] gesetzt wird.

Zur Verdeutlichung der intern durchgeführten Rechenschritte soll folgende Tabelle dienen:

Abschließend erfolgt die Zuweisung der Klasse (A, B oder C) in Abhängigkeit des kumulierten Prozentsatzes. Hierzu wird eine weitere berechnete Spalte ergänzt ([ABC_EVOJeMaterialgruppe]):

Die DAX Funktion SWITCH() bildet eine Fallunterscheidung ab. Es wird je Zeile geprüft, in welches Intervall der kumulierte Prozentsatz fällt, als Ergebnis wird in der berechneten Spalte [ABC_EVOJeMaterialgruppe]  die Klasse zurückgegeben (A, B oder C).

Das berechnete Feld [ABC_EVOJeMaterialgruppe] gibt nun je Zeile = Artikel die Klassifizierung zurück und steht somit dem Modell als manifestiertes Feld zur Verfügung.

Erstellt man auf Basis des tabellarischen Modells abschließend eine Pivottabelle und legt man auf das Feld [ABC_EVOJeMaterialgruppe] anschließend einen Datenschnitt (slicer) hat man das gewünschte Ergebnis:

Mit nur einem Klick auf die gewünschte Materialgruppe werden Klassifizierung, Anzahl Artikel und EVO angezeigt. Bei Bedarf können auch mehrere Materialgruppen betrachtet werden. Entfernt man den Filter hat man eine materialgruppenübergreifende ABC-Analyse. Im gewählten Beispiel ist es somit möglich, insgesamt 8 ABC-Analysen (materialgruppenübergreifend, je Materialgruppe = 7) durchzuführen, information at your fingertip.

Sven Galonska

Sven Galonska ist bei einem deutschen Maschinenbauer in der IT tätig. Hier beschäftigt er sich v.a. mit den Themen Business Intelligence, Fokus Einkauf und Logistik. Für analytische Zwecke kommt hierbei SAP BW Analysis for Office sowie vermehrt Microsoft Excel Power BI zum Einsatz. Das Thema Enterprise Reporting wird durch das Frontend tool SAP Web Intelligence (Standard-Berichte) abgedeckt. Darüber hinaus führt er Inhouse Schulungen für Endanwender über die genannten BI Frontend tools durch.