Die Darstellung eines Profit & Loss Statements (P&L, Income Statement) ist in einem zellbasiertem Tool wie Excel keine große Sache, in einem multidimensionalen System wie Power BI ist es aber eine Herausforderung. Die Schwierigkeit liegt dabei in der unausgeglichenen Hierarchie, die die einzelnen Positionen der P&L typischerweise bilden sowie die Staffelform, die üblicherweise zur Darstellung verwendet wird.

Die hier beschriebenen Lösungsansätze gelten analog auch für die Darstellung von Deckungsbeitragsrechnungen, Bilanzen und Cash-Flow-Rechnungen.

Ausgangssituation

Mit dem folgenden P&L Erfassungssheet werden im Showcase Group Forecast unserer cloud-basierten Datensammlungstechnologie data1.io die monatlichen Finanzdaten der Tochtergesellschaften der Sigma Group eingesammelt ...

... und stehen damit für Power BI über die sogenannte ODATA-Schnittstelle online als Faktentabelle ("Facts") zur Verfügung:

Erlöse werden mit positivem und Aufwendungen mit negativem Vorzeichen geliefert, sodaß die Datensätze der P&L summierbar sind.

Die folgenden 3 Umsetzungsvarianten fokussieren auf die Frage, wie die Dimensionstabelle für die P&L aussehen muß, um diese möglichst vollständig darstellen zu können.

Variante 1: symmetrische Hierarchie

Eine einfache und weitverbreitete Lösung für die Abbildung von asymmetrischen (= unausgeglichenen) Hierarchien besteht darin, sich von der Originalstruktur der Hierarchie zu lösen und diese einfach als symmetrische (= ausgeglichene) Hierarchie zu definieren:

Die Dimensionstabelle ist dabei recht simpel aufgebaut, die symmetrische Hierarchie ist leicht daran erkennbar, daß es in den Level-Definitionen keine Leerzellen gibt:

In Power BI werden die beiden Level-Felder gemeinsam mit dem Feld Metric Name (= unterste Ebene) zu einer 3-stufigen Hierarchie kombiniert:

Als Measure genügt eine einfache Summe auf das Wertfeld:

• Measure v1 = Sum(Facts[Value])

Pro:

• Einfach umzusetzen

Con:

• Die tatsächliche P&L Struktur wird nicht abgebildet und wichtige Zwischenergebnisse wie der Operating Profit werden nicht ermittelt. Als Ersatz können diese Zwischenergebnisse aber recht einfach als Measures ermittelt werden, sodaß die Kennzahlen zwar nicht als Teil der Hierarchie aber beispielsweise für Visualisierungen oder KPIs dargestellt werden können.

Weiterführend:

• Hier ein schönes Showcase Financial Reporting bei Enterprise DNA (die Matrix ist hier nicht im "Stepped" Layout konfiguriert sondern im Spaltenlayout)
• Hier ein interessanter Ansatz, um aus einer balancierten Hierarchie mittels Measures eine P&L in Staffelform abzuleiten: powerpivotpro.com/2018/01/star-schema-switch-drill-income-statement-design
• Hier ein anderer Ansatz mit einem Bridge Table: www.thebiccountant.com/2016/07/21/easy-profit-loss-account-scheme-reports-power-bi-power-pivot-dax (Parent Child Strukturen) und www.thebiccountant.com/2017/01/16/easy-profit-loss-and-other-account-statements-part2 (Regular Strukturen)

Variante 2: balancierte Hierarchie

Soll die tatsächliche P&L Struktur korrekt abgebildet werden, dann braucht es eine sogenannte Balancierung. Damit werden die unterschiedlichen Tiefen der asymmetrischen Hierarchie nur soweit angezeigt, wie diese Sinn machen :

Grundsätzlich resultiert die gezeigte P&L Struktur aus der folgenden Dimensionstabelle, die (blauen) Leerzellen signalisieren die unterschiedlichen Tiefen innerhalb der Hierarchie:

Power BI bietet jedoch - anders als das Tabular Model im SQL Server 2017 - bisher keine Standardfunktionalität zur Handhabung von solchen unausgeglichenen Hierarchien. Daher sieht die obige Dimensionsstruktur im Matrix Visual erst mal so aus:

Zur Balancierung muß die Dimensionstabelle in zweierlei Hinsicht erweitert werden:

1. Die (blauen) Leerzellen werden mit dem jeweiligen Metric Name (= unterste Ebene) aufgefüllt
2. Pro Zeile wird die maximale Hierarchietiefe in der zusätzlichen Spalte Hierarchy Depth (= blaue Spalte) als Zahl zwischen 1 und 7 (bei einer 7-stufigen Hierarchie) eingetragen

In Power BI werden 2 Hilfs-Measures zur Ermittlung der tatsächlichen Hierarchietiefe sowie der in Dimensionstabelle hinterlegten maximalen Hierarchietiefe definiert:

• Measure v2 BrowseDepth = ISFILTERED ('Dim Metrics v2'[Level 1])
  + ISFILTERED ('Dim Metrics v2'[Level 2])
  + ISFILTERED ('Dim Metrics v2'[Level 3])
  + ISFILTERED ('Dim Metrics v2'[Level 4])
  + ISFILTERED ('Dim Metrics v2'[Level 5])
  + ISFILTERED ('Dim Metrics v2'[Level 6])
  + ISFILTERED ('Dim Metrics v2'[Metric NAME])
• Measure v2 MaxDepth = MAX ('Dim Metrics v2'[Hierarchy Depth])

Zur Verdeutlichung der konkreten Berechnungsergebnisse können die beiden Measures in einem Matrix Visual aufgetragen werden:

Das Prinzip ist folgendes: an jenen Stellen der Hierarchie, an denen die tatsächliche Hierarchietiefe die maximal definierte Hierarchiestufe überschreitet (= roter Rahmen, beispielhaft) soll die Anzeige dieser Zeilen unterdrückt werden. Solange die tatsächliche Hierarchietiefe kleiner oder gleich der maximalen Hierarchietiefe ist (= grüner Rahmen, beispielhaft), soll die Zeile weiterhin angezeigt werden.

Dieses Verhalten wird durch das folgende Measure erreicht:

• Measure v2 = IF (
  [Measure v2 BrowseDepth] > [Measure v2 MaxDepth];
  BLANK ();
  SUM ('Facts'[Value])
  )

Die Verwendung der DAX-Funktion BLANK() bewirkt die Unterdrückung der Zeile in allen Power BI Visuals, folglich auch im Matrix Visual:

Pro:

• Inhaltlich richtige Ermittlung aller P&L Zwischenergebnisse aus den Dimensionsstammdaten
• (Detail-)Analysen bis auf Kontenebene

Con:

• Die Formatierung der Zeilen im Matrix Visual ist nicht ansprechend möglich, so können beispielsweise nicht die Ergebniszeilen gezielt hervorgehoben werden
• Die Ebenen im Matrix Visual können derzeit nur nach dem "Alles oder Nichts" Prinzip auf- und zugeklappt werden ... die aus den Excel PivotTables bekannte Funktion, bei der mittels "+/-"-Zeichen einzelne Äste gezielt auf- und zugeklappt werden können, existiert leider bisher im Matrix Visual nicht (das Feature ist aber angekündigt)
• Für das Reporting ist die hierarchische Form (= Ergebniskennzahlen oben) gegenüber einer Staffelform (= Ergebniskennzahlen unten) deutlich unterlegen

Quelle:

https://www.daxpatterns.com/parent-child-hierarchies

Variante 3: Staffelform mit dem Zebra BI Visual

Die Königsdisziplin des P&L Reportings ist die Darstellung in der Staffelform. Eine sehr schöne und einfach zu handhabende Lösung bietet das kostenpflichtige Zebra BI Custom Visual unserer Partnerfirma Zebra BI. Neben der Staffelform werden sehr leistungsfähige Visualisierungen bereitgestellt und zu einer ansprechenden Grafiktabelle kombiniert.

Zebra BI hat eine sehr clevere Vorgehensweise für die Gestaltung der Dimensionsdaten gewählt. Die P&L wird als 2-stufige Hierarchie abgebildet, wobei in jeder P&L-Zeile lediglich das nachfolgende Staffelergebnis definiert wird. Die richtige Reihenfolge muß wie auch schon in den anderen Varianten über ein Sortierfeld sichergestellt werden.

Das Zebra BI Custom Visual kann als kostenlose Testversion heruntergeladen werden und wird dann über den Befehl Import Custom Visual -> From File nach Power BI Desktop importiert:

Zur Abfrage von IST und Vorjahr aus der Faktentabelle werden zwei Measures definiert:

• Measure v3 Actual = CALCULATE(
  Sum(Facts[Value]);
  Facts[Scenario]="ACT.2018.03"
  )
• Measure v3 PY = CALCULATE(
  Sum(Facts[Value]);
  Facts[Scenario]="ACT.2017.03"
  )

Die Belegung des Visuals is sehr einfach: die zweistufige P&L Hierarchie kommt in den Bereich Category und die beiden Measures für Actuals und Prior Year in die Bereiche Values und Prior Year. Abschließend müssen lediglich die Staffelergebnisse der P&L mit dem Kontextmenübefehl als Result definiert werden, was zur korrekten Ermittlung der Ergebnisse führt (die Ergebniszeilen werden mit einem "=" gekennzeichnet):

Weitere nützliche Funktionen des Zebra BI Custom Visuals für das P&L Reporting:

• Einfache Auswahl der Display Unit (bspw. "in Tausend") und optionale Anzeige im Header des Visuals anstelle bei jeder Zahl
• Automatische Ermittlung der Abweichungen und Handhabung der Ausreißer bei der Ermittlung der relativen Abweichungen in %
• Aufnahme von bis zu 3 Measures - es können also zusätzlich zum Vorjahr auch die Planwerte in das Visual aufgenommen werden
• Einfacher Wechsel des Grafiktyps für die Abweichungen von Wasserfall auf Balkendiagramm
  
• Responsives Verhalten des Visuals - je nach vorhandenem Platz werden die angezeigten Inhalte angepaßt
  
• Anstelle der "2-stufigen Staffelhierarchie" kann auch ein "1-stufiger Flattable" verwendet werden, der bereits die vorberechneten Ergebniszeilen beinhaltet (wie das etwa bei den MS Dynamics NAV Datenfeeds der Fall ist)
  
  Quelle: Zebra BI
• Das Visual kann mit positiven Vorzeichen für Erlöse und Kosten umgehen. In diesem Fall werden die Aufwandspositionen mit dem Kontextmenübefehl Invert entsprechend deklariert (siehe Abbildung oben, erkennbar am "-" Vorzeichen vor dem Positionstext)
• Das Visual kann seit Version 2.0 mittels Page Tooltips um wertvolle Informationen - bspw. zur zeitlichen Entwicklung jeder einzelnen P&L Position - angereichert werden

Pro:

• Perfekte Darstellung der P&L in Staffelform inklusive hervorragender Visualisierung der Abweichungen mit zahlreichen praxiserprobten Features

Con:

• Der Drilldown bis auf Einzelkonto- oder Einzelbuchungsebene ist derzeit nicht direkt im Visual möglich (da auf 2 Ebenen begrenzt). Diese Funktion ist seitens Zebra BI aber bereits angekündigt, in der Zwischenzeit können aber relativ einfach mit einem korrespondierenden Matrix-Visual die Details nachgeschlagen werden ...
• Die vertikale Struktur des Visuals kann als Alternative zum Responsive Mode teilweise fix konfiguriert werden, aber nicht vollständig.

Weiterführende Informationen:

• Das Zebra BI Custom Visual kann für derzeit EUR 8,00 pro User und Monat bei uns bezogen werden
• zebrabi.com/pbi
• zebrabi.com/income-statements-power-bi-webinar-recording
• zebrabi.com/microsoft-income-statement-fy18-q4-results

Fazit

Das P&L Reporting in Power BI erfordert einiges an Hintergrundwissen zur analytischen Datenmodellierung sowie zur DAX-Formelsprache. Das Zebra BI Custom Visual macht das Thema P&L Reporting dagegen in bisher nicht gekannter Einfachheit auch für "Normal User" realisierbar und liefert dazu hervorragende Abweichungsvisualisierungen.