Willkommen zu meinem Geheimtipp für die Berechnung von Abweichungen in Excel und Power BI! Es mag auf den ersten Blick einfach erscheinen, Abweichungen zwischen Ist- und Vorjahreswerten oder Ist- und Planwerten zu ermitteln, aber bei genauerer Betrachtung gibt es einige Fallstricke. Um ein besseres Verständnis im Unternehmen zu gewährleisten, sollten die zahlreichen Darstellungsoptionen standardisiert werden.
Inhaltsbezogene bewährte Verfahren
-
Bezugsgrößen (Ist, VJ, Plan): Verwenden Sie ein intuitives Vorzeichenschema, z.B. Umsatz + / Kosten -, Absatzmenge + / Rückgaben -, Vermögenswerte + / Verbindlichkeiten -, usw. Formatieren Sie nur das “-” vor den negativen Werten, nicht aber das “+” vor den positiven Werten.
-
Absolute Abweichungen: Berechnen Sie die Abweichungen mit der einfachen Formel “Ist – Vorjahr” bzw. “Ist – Plan”. Die Formatierung erfolgt mit “+” und “-” sowie möglicherweise einem “+/-” für Nullwerte, ohne Leerzeichen zwischen Vorzeichen und Zahl. Durch diese Formatierung sind die Werte intuitiv als Abweichungsspalten erkennbar.
-
Abweichungen in %: Stellen Sie sicher, dass die Berechnung Divisionen durch Null abfängt und optional einen Platzhaltertext für diesen Fall anzeigt (oder ein Leerzeichen, aber keine 0%). Die Formatierung erfolgt ähnlich wie bei absoluten Abweichungen mit dem +/-/0-Schema. Je nach Platzverhältnissen kann das Prozentzeichen am Ende hinzugefügt oder kursiv dargestellt werden.
Umsetzung in Excel
Die Umsetzung dieses Grundkonzepts in Excel sollte weitgehend bekannt sein. Verwenden Sie die folgenden Formeln:
- Abweichung (Abw) = D7 – E7
- Abweichung in % (Abw in %) = WENN(E7=0; “-“; G7/E7)
Excel bietet perfekte technische Rahmenbedingungen für die Formatierung von Abweichungen mit 3 Bedingungen für positive, negative und Nullwerte.
Umsetzung in Power BI
Die Basisanforderungen können in Power BI mit ähnlichen Formeln wie in Excel umgesetzt werden. Erstellen Sie zunächst 2 Measures für die Summierung der Ist- und Vorjahreswerte und dann ein Measure für die absolute Abweichung:
- IST = Sum(Datentabelle[IST-Werte])
- VJ = Sum(Datentabelle[VJ-Werte])
- Abweichung (Abw) = [IST] – [VJ]
Leider bietet Power BI nur begrenzte Formatierungsoptionen für Measures und keine Formatierungsoption mit 3 Bedingungen wie in Excel. Eine mögliche Alternative ist die gezielte Formatierung der einzelnen Berechnungsfälle innerhalb des DAX-Statements mit der FORMAT-Formel für das Measure.
Abweichung in %: Vorzeichenlogik und Umgang mit Ausreißern
Stellen Sie sicher, dass das Vorzeichen der Abweichung in % mit dem der absoluten Abweichung übereinstimmt. Verwenden Sie die folgende Best-Practice-Formel in Excel:
- Abweichung in % = WENNNFEHLER(WENN(G6/ABS(E6)>=999%; “>+999 %”; WENN(G6/ABS(E6)<-999%; “<-999 %”; G6/ABS(E6))); “-“
In Power BI kann die Umsetzung der erweiterten Best Practices mit dem folgenden DAX-Statement erfolgen:
- Abweichung in % (Formel 2 Best Practice) = IF(Divide([Abw];ABS([VJ]))>9,9; “>+999”; IF(Divide([Abw];ABS([VJ]))<-9,9; “<-999″; IF(Divide([Abw];ABS([VJ]))<0;Format(Divide([Abw];ABS([VJ]));”0 %”); IF(Divide([Abw];ABS([VJ]))>0;Format(Divide([Abw];ABS([VJ]));”+0 %”); IF(Divide([Abw];ABS([VJ]))=0;Format(Divide([Abw];ABS([VJ]));”0 %”); 0 )))))
Bitte beachten Sie, dass in der Formatierung die englische Notation für Tausendertrennzeichen und Dezimalpunkt verwendet wird.
Fazit
Die wichtigsten Aspekte bei der Berechnung von Abweichungen sind die inhaltlichen Best Practices. Solange diese nicht klar und vollständig sind, kann man leicht in das “Rate-spiel” verfallen. In Excel kann dies besonders zeitaufwendig sein, da die Abweichungsformel möglicherweise tausendfach kopiert werden muss. In Power BI haben wir die DIVIDE- und FORMAT-Formel kennengelernt, die optisch ansprechende Ergebnisse liefern, aber auch Einschränkungen bei der Visualisierung haben. Zahlenformatierungen sollten im Table- und Matrix-Visual von Power BI rechtsbündig ausgerichtet werden, um die Eigenschaften von Measures besser beobachten zu können.
Also, viel Spaß beim Berechnen von Abweichungen in Excel und Power BI!
