Die Ermittlung von Abweichungen zwischen IST und Vorjahr oder IST und Plan ist zwar auf den ersten Blick nicht besonders schwierig, bei näherer Betrachtung existieren aber einige Fallen. Dazu sollten die zahlreichen Darstellungsvarianten zum besseren Verständnis im Unternehmen standardisiert werden.

Nach der Festlegung dieser inhaltlichen Best Practices zum Vorzeichenschema, zur Ermittlung und zur Formatierung der Abweichungen ist die Umsetzung in den Tools Excel und Power BI noch eine kleine technische Herausforderung. In Excel liegt der Schlüssel in der geschickten Verformelung und in der bedingten Zahlenformatierung, in Power BI im Einsatz der DAX-Formel FORMAT, die leider aber auch einige Nachteile mit sich bringt.

Ganz besonderen Dank möchte ich an Markus Wolff von unserer Partnerfirma chartisan richten, er hat uns wieder einmal sein komprimiertes Know-How aus dem IBCS®-Umfeld und aus seiner eigenen langjährigen Praxiserfahrung zur Verfügung gestellt.

1. Basislösung

Inhaltliche Best Practices

1. Bezugsgrößen (IST, VJ, Plan)
   Anwendung eines intuitiven Vorzeichenschemas: Erlöse + / Aufwendungen -, Absatzmenge + / Retouren -. Aktiva + / Passiva - usw. Formatiert wird nur das "-" vor den negativen Werten, nicht aber das "+" vor den positiven Werten.
2. Abweichungen absolut
   Die Berechnung erfolgt mit der sehr einfachen Formel "IST - Vorjahr" bzw. "IST - Plan"; die Formel ist in allen Zeilen einheitlich. In Verbindung mit Punkt 1 hat das den sehr großen Vorteil, daß die Abweichungen intuitiv richtig summieren. Die Formatierung erfolgt sowohl mit "+" als auch "-" und eventuell einem "+/-" für die 0-Werte, aus Platzgründen ohne Leerzeichen zwischen dem Vorzeichen und der Zahl. Aufgrund dieser Formatierung sind die Werte dieser Spalte ohne weitere Erklärung intuitiv als Abweichungsspalten erkennbar.
3. Abweichungen in %
   Die Berechnung muß zumindest die Division durch 0 (DIV/0) abfangen und optional einen Platzhaltertext für diesen Fall anzeigen (oder ein Blank, nicht aber ein 0 %). Die Formatierung erfolgt analog zur absoluten Abweichung mit dem +/-/0 Schema, je nach Platzverhältnissen wird das %-Zeichen am Ende (hier aber mit vorangehendem Leerzeichen) dazu formatiert oder alternativ ohne %-Zeichen formatiert und die Werte kursiv dargestellt.

Umsetzung in Excel

Die Umsetzung dieser Basislösung in Excel dürfte weitgehend bekannt sein:

• Abw = D7-E7
• Abw in % = WENN(E7=0;"---";G7/E7)

Für die Formatierung der Abweichungen bietet Excel die perfekten technischen Rahmenbedingungen, da die Formatierung von Zahlen mit 3 Bedingungen für positive, negative und 0-Werte erfolgen kann (siehe Blogbeitrag 1×1 der Zahlenformatierung in Excel):

• Abw: +#.##0;-#.##0;±0
• Abw in %: +0 %;-0 %;±0 %

Umsetzung in Power BI

Die Basisanforderungen sind in Power BI mit sehr excel-ähnlichen Formeln umsetzbar und erfordern zuerst einmal fast kein Know-How der DAX-Formelsprache. Im ersten Schritt werden 2 Measures für die Summierung der IST- und VJ-Werte definiert und dann das Measure für die absolute Abweichung angelegt:

• IST = Sum(Datentabelle[IST-Werte])
• VJ = Sum(Datentabelle[VJ-Werte])
• Abw = [IST]-[VJ]

Leider bietet Power BI nur sehr beschränkte Formatierungsoptionen für Measures und leider keine Formatierungsoption mit 3 Bedingungen für +/-/0 wie in Excel. Eine mögliche Alternative ist die gezielte Formatierung der einzelnen Berechnungsfälle innerhalb des DAX-Statements mit den Custom Numeric Formats der FORMAT-Formel für das Measure:

• Abw (als Text) =
  IF([IST]>[VJ];Format([IST]-[VJ];"+#,##0");
  IF([IST]<[VJ];Format([IST]-[VJ];"#,##0");
  IF([IST]=[VJ];Format([IST]-[VJ];"#,##0")
  )))

Beachten Sie bitte, daß im Formatierungsstring Tausenderpunkt und Komma der englischsprachigen Notation folgen und daß das negative Vorzeichen nicht extra formatiert werden darf (da sonst zwei "--" angezeigt werden).

Wie im folgenden Screenshot (in der vorletzten Spalte) zu sehen ist, erfolgt aber nicht nur die gewünschte Formatierung sondern auch eine Konvertierung der Berechnungsergebnisse in das Textformat, die Werte werden daher jetzt linksbündig im Table-Visual angezeigt. Eine Besonderheit der Formelsprache DAX ist, daß das Format eines Measures nicht "von außen" definiert werden kann sondern sich "von innen" aus den Berechnungsergebnissen ableitet. Wird daher obiger Formel noch eine - für die Funktion an sich unnötige - ELSE-Bedingung mit einer einfachen "0" hinzugefügt, dann wird das Measure wieder automatisch rechtsbündig ausgerichtet (da es jetzt zumindest wieder 1 theoretischen Zahlenwert gibt):

• Abw (als Text mit 0) =
  IF([IST]>[VJ];Format([IST]-[VJ];"+#,##0");
  IF([IST]<[VJ];Format([IST]-[VJ];"#,##0");
  IF([IST]=[VJ];Format([IST]-[VJ];"0");
  0
  )))

Trotz dieser Verbesserung der Ausrichtung - die übrigens auch über die Settings des Table- und Matrix-Visuals auf rechtsbündig geändert werden könnte - bleibt das Measure ein Text. Es kann daher auf das Measure im Table- und Matrix-Visual leider kein Conditional Formating angewendet werden und das Measure kann auch in keinem Chart-Visual verwendet werden. Das ist eine wesentliche Einschränkung der Funktionalität dieser Text-Measures.

Die Ermittlung der Abweichung in % kann mit der DIVIDE-Formel recht elegant gelöst werden, da diese die Division durch 0 (DIV/0) abfängt und einen Platzhaltertext als optionales Argument ermöglicht:

• Abw in % (Formel 1) = Divide([Abw];[VJ])
• Abw in % (Formel 1 mit Divide-Text) = Divide([Abw];[VJ];"---")

Die Verwendung eines Textes als Platzhalter für den DIV/0-Fall eröffnet wieder das aus der Ermittlung der absoluten Abweichung bereits bekannte Thema der Formatkonvertierung des Measures: die %-Formatierung für die anderen Ergebniswerte gehen jetzt leider verloren (das Measure gilt aber noch als Zahl und ist visualisierbar). Ein optisch sauberes Ergebnis bringt das folgende Text-Measure, jedoch um den bereits bekannten Preis der Nicht-Visualisierbarkeit:

• Abw in % (Formel 1 als Text und 0) =
  IF([VJ]=0;"---";
  IF([VJ]<>0;Format(Divide([Abw];[VJ]);"0 %");
  0
  ))

2. Abweichung in %: Vorzeichenlogik und Umgang mit Ausreißern

Inhaltliche Best Practices

1. Das Vorzeichen der Abweichung in % muß ident sein wie die absolute Abweichung, da alles andere unverständlich ist. Das Problem der bisher verwendeten Formel wird nämlich evident, wenn nicht nur positive Bezugswerte sondern auch negative wie Kosten oder Ergebniszeilen in die Abweichungsermittlung einbebezogen werden. Es kommt zu einem "Vorzeichendreher", wenn die Bezugsgröße (hier: VJ) negativ ist:
   
2. Ausreißer - das sind sehr große Prozentwerte, im obigen Beispiel dargestellt in den letzten beiden Zeilen - sollten ab einem definiertem Grenzwert durch einen Platzhaltertext abgefangen werden. Als Best Practice hat sich ein relativer Grenzwert von > 999 % (3 stellig) etabliert, absolute Grenzwerte für die Bezugsgröße von bspw. < 0,5 haben sich hingegen nicht bewährt.

Umsetzung in Excel

Die Umsetzung in Excel ist mit folgender Best-Practice-Formel zu machen:

• Abw in % =
  WENNNFEHLER(
  WENN(G6/ABS(E6)>=999%;">+999 %";
  WENN(G6/ABS(E6)<-999%;"<-999 %";
  G6/ABS(E6)));
  "--")

Der Vorzeichendreher wird durch die Division durch den Absolutwert der Bezugsgröße (VJ = Zelle E6) verhindert und damit die Vorzeichengleichheit zwischen der absoluten Abweichung und der Abweichung in % sichergestellt. Die DIV/0 wird hier durch die alles umschließende WENNFEHLER-Formel abgefangen und der Platzhaltertext "---" zugewiesen. Die Ausreißer werden durch zwei vorangestellte Bedingungen mit dem Grenzwert +/- 999 % versehen.

Umsetzung in Power BI

Die Umsetzung dieser erweiterten Best Practices kann mit folgendem DAX-Statement erfolgen:

• Abw 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
  )))))

Der Preis der optisch ansprechenden Zahlenformatierung ist jedoch - wie bereits weiter oben auseinandergesetzt - die Nicht-Visualisierbarkeit dieses Text-Measures.

Fazit

Das Wichtigste bei der Ermittlung von Abweichungen sind die inhaltlichen Best Practices, solange diese unvollständig oder unklar sind, kommt man aus dem "Formelraten" nicht heraus (insbesondere in Verbindung mit dem "Vorzeichendreher" kann das leicht passieren).

In Excel kann das in großen Berichten sehr aufwendig werden, da die Abweichungsformel vielleicht 1000-fach auskopiert wird. In Power BI haben wir zur Ermittlung der Abweichungen  die DIVIDE- und insbesondere die FORMAT-Formel kennengelernt. Letztere liefert zwar optisch ansprechende Ergebnisse im Table- und Matrix-Visual, kann aber aufgrund der Text-Eigenschaft nicht visualisiert werden. Der Einsatz muß daher im Einzelfall evaluiert werden. Übrigens sollten als Best Practice in Power BI im Table- und Matrix-Visual die Column Header für Wertspalten rechtsbündig ausgerichtet werden, wir haben das in diesem Blogbeitrag nicht getan, um die Zahlen-/Texteigenschaft eines Measures besser beobachten zu können.