Power Query, laufende Summe, ABC Analyse

Im Folgenden wird eine Methode beschrieben, wie man mit Excel Power Query eine
laufende Summe absolut, prozentual zum Gesamtergebnis, ABC Klassifizierung und somit
eine ABC Analyse durchführen kann.

Ausgangsbasis ist eine Tabelle mit 2 Spalten (Produkt, Wert)

Exkurs:

Excel Tabelle als Abfrage zur weiteren Bearbeitung in Power Query übernehmen



Schritt 1
Im Power Query Editor absteigend nach Spalte [Wert] sortieren

Schritt 2
benutzerdefinierte Spalte hinzufügen, [Custom]

= List.Buffer(Sortierung_Wert[Wert])

Jeder einzelne Wert der Spalte [Wert] wird in einem List Objekt gespeichert


Schritt 3
benutzerdefinierte Spalte [kumuliert] (laufende Summe) hinzufügen

=List.Sum(List.Select([Custom], (x)=>x>=[Wert]))

im jeweiligen Zeilenkontext wird dadurch die laufende Summe aller vorherigen Werte ermittelt

Schritt 4
benutzerdefinierte Spalte [Prozent_kumuliert] hinzufügen

= [kumuliert]/List.Sum([Custom])

Schritt 5
benutzerdefinierte Spalte [Klasse] hinzufügen,

=if [Prozent_kumuliert] < 0.8 then "A" else
    if [Prozent_kumuliert] < 0.95 then "B" else
"C"




Hinweis:
eine laufende Summe kann auch mit der Kombination aus
List.Sum(List.Range()) ermittelt werden, siehe Funktionen Language M

1    Index-Spalte beginnend mit 1 hinzufügen
2    benutzerdefinierte Spalte hinzufügen := List.Sum(List.Range(#"Geänderter Typ"[Wert],0,[Index]))

Kommentare

  1. Hallo Svens,

    sehr interessanter Artikel, der mir auch schon sehr weitergeholfen hat. Aber aktuell stehen ich vor dem Problem, dass ich mehrere Kriterien benötige. Das könnte zB. so sein Betrieb/Warengruppe/Artikel.
    Hast du eine Idee, wie ich das am besten umsetze. Bin echt ratlos

    Danke!!!!
    Sebastian

    AntwortenLöschen
  2. Deine Anforderung ist etwas Komplexer, kann aber mit Power Pivot / DAX gelöst werden, siehe folgenden Artikel http://blog.gbrueckl.at/2013/01/dynamic-abc-analysis-in-powerpivot-using-dax/

    AntwortenLöschen
    Antworten
    1. Ich habe mal eine quick & dirty eine Beispiel Arbeitsmappe erstellt, welche Deine Anforderungen abdecken könnte. Diese findest Du in folgendem Blog Beitrag als Download weiter unten http://svens-excel-welt.blogspot.de/2015/04/dynamische-abc-analyse-dax-allexcept.html

      Löschen
  3. Wow, vielen dank für die schnelle Hilfe. Ich hab leider große Datenmengen > 200T Zeilen, dabei bricht Powerpivot nach ca. 1h Berechnung mit hinweis auf den Speicher ab. Dabei sind 16GB Ram und 64 bit installiert. Daher wollte ich die Berechnung in die Powerquery verlegen.

    Vielen Dank für deine schnelle Hilfe!!!

    Grüße
    Sebastian

    AntwortenLöschen
  4. verstehe, ein Ansatz könnte sein, die Daten komplett in eine Abfrage mit Power Query zu laden, dann die Struktur um die erforderlichen Berechnungen anzureichern. Anschließend kann man neue Abfragen erstellen, welche auf die erste verweisen (rechte Maustaste auf Abfrage -> Verweis. In diesen (auf erste Abfrage verweisende Abfragten) kann dann über Filterung der jeweiligen Spalten / Merkmale eine gewünschte Analyse-Sicht manifestiert werden. Jede Abfrage repräsentiert dann quasi eine konkrete Anforderung an die ABC Analyse

    AntwortenLöschen
  5. ... ich hab die Lösung, manchmal ist es echt verrückt an welchen Kleinigkeiten etwas scheitern kann. Ich hab einfach von Dezimal auf Ganzzahl umgestellt und schon ist der Speicherhunger deutlich kleiner. Wahnsinn das waren mindestens 5 Nächte und dann so eine Kleinigkeit.

    Interessanter Ansatz, das teste ich trotzdem aus.

    Danke dir für die schnelle und umfangreiche Hilfe!

    Sebastian

    AntwortenLöschen

Kommentar veröffentlichen

Beliebte Posts aus diesem Blog

Vergleich mit 2 Bedingungen, INDEX(), VERGLEICH()

Dropdown Liste mit Mehrfachauswahl

nützliche Text Funktionen in Power Query