Mit Power Query den Prozentanteil an der Gesamtsumme berechnen
Worum geht es beim Ermitteln des Prozentanteils?
Power Query bietet keine direkte Funktion, um für eine Spalte mit Zahlen deren Prozentwert an der Spaltensumme zu berechnen. In diesem Beitrag erfahren Sie, wie Sie mit einfachen Mitteln, ohne Programmierkenntnisse genau dieses Ziel erreichen.
Sie lernen dabei, wie man die Gesamtsumme bildet und wie Sie diese in einer späteren Berechnung als Variable nutzen und damit pro Zeile den entsprechenden Prozentsatz ermitteln.
Diese Technik ist nicht nur praktisch, sondern lässt sich auch auf viele andere Szenarien anwenden – etwa bei Marktanteilsanalysen, Anteilsberechnungen im Reporting oder zur Visualisierung in Diagrammen.
Die folgende Abbildung soll zunächst als Beispiel dienen. Sie sehen darin eine Tabelle in Power Query mit zwei Spalten für Produkte und deren Umsatz (in Euro).
Gesamtsumme berechnen
Zuerst benötigen Sie die Gesamtsumme der Spalte Umsatz. Aktivieren Sie im Menüband die Befehlsfolge Transformieren > Gruppieren nach.
Es öffnet sich das Dialogfeld Gruppieren nach. Sie möchten allerdings keine Gruppierung vornehmen, sondern die Summe über alle Zeilen berechnen. Sie müssen daher die ausgewählte Gruppierung oben im Dialogfeld löschen.
Klicken Sie oben auf Weitere und dann auf die drei Punkte rechts oben.
Wählen Sie hier Löschen, da Sie die Gesamtsumme über die Spalte berechnen wollen.
Im unteren Bereich geben Sie an, was berechnet werden soll:
- Neuer Spaltenname: Gesamtsumme
- Vorgang: Summe
- Spalte: Umsatz
Bestätigen Sie mit OK.
Nun haben Sie Ihre Gesamtsumme als eigene Tabelle vorliegen.
Gesamtsumme als Variable speichern
Damit Sie sich später in Berechnungen auf diese Summe beziehen können, müssen Sie noch einen Drilldown durchführen.
Erstellen Sie einen Rechtsklick auf den Wert in der Tabelle und wählen Sie im Kontextmenü den Befehl Drilldown ausführen aus.
Jetzt wird der Wert als einzelner Schritt im rechten Bereich gespeichert – ähnlich wie eine Variable, auf welche Sie sich beziehen können.
Zur ursprünglichen Datenquelle zurückkehren
Da nach dem Drilldown nur noch die Zahl angezeigt wird, müssen wir wieder zu den Ursprungsdaten zurück. Klicken Sie oben auf fx, um einen neuen Schritt hinzuzufügen.
Ersetzen Sie den automatisch erzeugten Bezug durch den Namen der Ursprungsquelle. Im Beispiel beziehen Sie sich dabei auf den zweiten Schritt Geänderter Typ, da hier die Zahlenformate angepasst wurden.
Erfassen Sie daher die folgende Formel:
= Geänderter Typ
Bestätigen Sie Ihre Einstellung, indem Sie die Enter-Taste drücken. Nach dem Drücken der Enter-Taste wird die Formel wie folgt dargestellt:
= #"Geänderter Typ".
Sie bekommen anschließend auch wieder den Inhalt der Abfrage wie beim Schritt Geänderter Typ angezeigt.
Prozentspalte hinzufügen
Jetzt kommt die eigentliche Berechnung des Prozentwertes. Aktivieren Sie im Menüband die Befehlsfolge Spalte hinzufügen > Benutzerdefinierte Spalte.
Es öffnet sich das Dialogfeld Benutzerdefinierte Spalte.
Erfassen Sie hier oben unter Neuer Spaltenname einen aussagekräftigen Namen, wie zum Beispiel Prozentwert.
Unter Benutzerdefinierte Spaltenformel erfassen Sie die folgende Formel
= [Umsatz] / Gesamtsumme * 100
Wichtig: Den Begriff Gesamtsumme bitte nicht in eckige Klammern setzen, da es sich hierbei um einen Schritt (=Variable) und nicht um eine Spalte handelt.
Bestätigen Sie Ihre Einstellungen, indem Sie das Dialogfeld durch Klick auf OK schließen.
Hinweis: Wenn Sie den Prozentwert anschließend mit dem Datenformat Prozentsatz formatieren wollen, lassen Sie in der Formel den Teil * 100 weg. Anschließend weisen Sie der neuen Spalte das Datenformat Prozentsatz zu.
Power Query berechnet nun automatisch den Anteil jedes Umsatzwertes an der Gesamtsumme in der neuen Spalte.
Der neuen Spalte können Sie noch ein geeignetes Datenformat zuweisen und dann diese Tabelle in Ihre Excel-Arbeitsmappe übernehmen. Aktivieren Sie dazu im Menüband die Befehlsfolge Start > Schließen & laden.
