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. In einem früheren Blogbeitrag haben wir bereits gezeigt, wie P&L Strukturen mit dem Matrix Visual in hierarchischer Form abgebildet werden können, für die Darstellung in Staffelform wurde damals das (hervorragende) Zebra BI Custom Visual verwendet. In diesem Blogbeitrag zeigen wir, wie die Staffelform auch mit dem Standard Matrix Visual realisiert werden kann.

Der hier beschriebene Lösungsansatz gilt analog auch für die Darstellung von Deckungsbeitragsrechnungen, Bilanzen und Cash-Flow-Rechnungen.

1. Ausgangssituation: Hierarchisches Datenmodell

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 über die ODATA-Schnittstelle in Power BI 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 P&L Strukturen werden am effektivsten als sogenannte asymmetrische Hierarchie in einer Dimension abgebildet. Die gelben Zellen signalisieren dabei den relevanten sichtbaren Teil der Hierarchie, die blauen Zellen den wiederholenden und damit auszublendenden Teil der Hierarchie:

Wie in Variante 2 des hier zugrundeliegenden Blogbeitrags gezeigt wurde, kann mit einem DAX-Statement diese asymmetrische Hierarchie folgendermaßen dargestellt werden:

Aufgrund der ungewohnten Sicht ("die P&L steht auf dem Kopf") trifft diese Darstellung bei vielen Informationskonsumenten nur auf geringe Akzepanz. Daher wird im folgenden Beitrag gezeigt, wie auch die gewohnte Staffelform mit Power BI Bordmitteln dargestellt werden kann.

2. Lösung: Staffelform mit dem Matrix Visual

Die Darstellung der P&L in Staffelform mit dem Matrix Visual kann beispielsweise folgendermaßen visualisiert werden. Auf der linken Seite wird eine Variante mit Einrückungen gezeigt, auf der rechten Seite ohne Einrückungen und mit "=" bei den Ergebniszeilen. In beiden Varianten werden Leerzeilen zur Verbesserung der Lesbarkeit eingesetzt:

Baustein #1 "Disconnected P&L Table"

Die Realisierung erfolgt mit einem sogenannten P&L Table (allgemeiner könnte dieser auch "KPI Table" genannt werden). Dabei handelt es sich um eine Parametertabelle mit dem gewünschten Zeilenaufbau. Die beiden Darstellungsvarianten oben werden mit den beiden Feldern Metric DISPLAY I und Metric DISPLAY II erzeugt, die anderen beiden Felder werden für die Sortierung sowie die Abfrage im DAX-Statement benötigt:

Aus Datenmodellierungssicht handelt es sich um ein Disconnected Table Pattern, d.h. der P&L Table steht in keiner Beziehung zu einer Tabelle im Datenmodell (die logische Verbindung zum Datenmodell ergibt sich über das noch zu definierende Measure):

Für die Eignung als P&L Table müssen in dieser Tabelle zwei wesentliche Themen gelöst werden:

1. Die Leerzeilen zur Gliederung der P&L würden sowohl im Matrix als auch im Table Visual auf eine einzige Zeile verdichtet werden - die oben gezeigte Darstellung wäre also gar nicht möglich. Weiters könnte die Sort-by-Column Definition nicht gesetzt werden, da mehrere gleiche Leerzeilen unterschiedliche Werte in der Spalte Metric Index aufweisen und so die Sort-by-Column Definition verhindern würden.
2. Die vorangestellten Leerzeichen zur Einrückung der Positionsbezeichnungen würden zwar im Matrix Visual korrekt angezeigt werden, im Table Visual würden diese aber automatisch entfernt und daher nicht angezeigt werden können.

Beide Themen werden in der Excel Datenquelle für den P&L Table durch das nicht druckbare (= unsichtbare) UNICODE-Sonderzeichen 8203 gelöst:

• Die Leerzeilen werden durch eine unterschiedliche Anzahl dieses Sonderzeichens unterschiedlich gemacht. Am effektivsten kann das in Excel mit der REPT()-Funktion (deutsch: WIEDERHOLEN()-Funktion) erreicht werden -> hier wird ein Bezug auf die Spalte G gesetzt, dort muß in jeder Leerzeile eine andere Zahl eingetragen sein
• Vor jedem führendem Leerzeichen muß das Sonderzeichen vorangestellt werden. Dies geschieht wiederum am effektivsten mit einer REPT()-Kombination aus Sonderzeichen und Leerzeichen (= UNICODE Zeichen 32) -> hier wird ein Bezug auf die Spalte H gesetzt, um die Anzahl der führenden Leerzeichen einfach variieren zu können

Die Formel für die Spalte Metric DISPLAY I bildet beide Thematiken ab (die Formel gilt für alle Zeilen in der Tabelle, nicht nur für die Leerzeilen):

Die Formel für die Spalte Metric DISPLAY II ist simpler aufgebaut, da nur die Leerzeilen-Thematik gelöst werden muß:

Baustein #2 "Measure für den P&L Table"

Der disconnected P&L Table bildet die Zeilenstruktur im Matrix (oder Table) Visual, für die Zahlen braucht es ein Measure. Dem eigentlichen Measure liegt ein sehr einfaches Summen Measure auf die Faktentabelle zugrunde:

Sum Measure = SUM(Facts[Value])

Das Measure für den P&L Table ist umfangreich, aber vergleichsweise simpel. Pro Ergebniszeile - das sind solche, die in der P&L Dimensionstabelle in verschiedenen Levels zu finden sind - wird eine Bedingung im SWITCH()-Statement mit einer Abfrage auf den exakten Knoten in der P&L Dimensionstabelle eingesetzt:

P&L Table Measure = 
VAR varSelection = SELECTEDVALUE('P&L Table'[Metric KEY])

RETURN
    SWITCH(
        TRUE();
        varSelection = "Gross Profit"; 
            CALCULATE([Sum Measure];'Dim P&L'[Level 6]="Gross Profit");
        varSelection = "EBIT";
            CALCULATE([Sum Measure];'Dim P&L'[Level 5]="Operating Profit");
        varSelection = "Finance Profit";
            CALCULATE([Sum Measure];'Dim P&L'[Level 5]="Finance income")
            + CALCULATE([Sum Measure];'Dim P&L'[Level 5]="Finance costs");
        varSelection = "Profit before income taxes";
            CALCULATE([Sum Measure];'Dim P&L'[Level 4]="Profit before income taxes");
        varSelection = "Profit from continuing operations";
            CALCULATE([Sum Measure];'Dim P&L'[Level 3]="Profit from continuing operations");
        varSelection = "Profit & Loss";
            CALCULATE([Sum Measure];'Dim P&L'[Level 1]="Profit & Loss");
        varSelection = "EBIT in %";
            FORMAT(
                DIVIDE(
                    CALCULATE([Sum Measure];'Dim P&L'[Level 5]="Operating Profit");
                    CALCULATE([Sum Measure];'Dim P&L'[Metric NAME]="Revenue")
                );
                "0.0 %")
                ;
        varSelection = "Profit & Loss in %";
            FORMAT(
                DIVIDE(
                    CALCULATE([Sum Measure];'Dim P&L'[Level 1]="Profit & Loss");
                    CALCULATE([Sum Measure];'Dim P&L'[Metric NAME]="Revenue")
                );
                "0.0 %")
                ;
        If(varSelection <> Blank();
            CALCULATE(' Measures'[Sum Measure];
                FILTER('Dim P&L';'Dim P&L'[Metric KEY]=varSelection)
            );
        ""
        )
    )

Das Matrix Visual erhält jetzt aus dem P&L Table die Zeilenstruktur und aus dem Measure dynamisch die Ergebnisse je Zelle berechnet:

Die erste Besonderheit des DAX-Statements ist die SONST-Bedingung des SWITCH()-Statements. Hier wird der gefundene Zeileneintrag aus dem P&L Table einfach auf die Spalte Metric KEY der P&L Dimensionstabelle angewendet (das erspart die Auflistung aller Ergebniszeilen im SWITCH()-Statement):

P&L Table Measure (Kern) = 
VAR varSelection = SELECTEDVALUE('P&L Table'[Metric KEY])
RETURN
        If(varSelection <> Blank();
            CALCULATE(' Measures'[Sum Measure];
                FILTER('Dim P&L';'Dim P&L'[Metric KEY]=varSelection)
            );
        ""
        )

Die P&L mit diesem verkürzten Measure würde daher nur die unterste Ebene der P&L zeigen (nicht aber die Ergebniszeilen, diese kommen durch die anderen Bedingungen im SWITCH()-Statement hinzu):

Mit Blick auf die Leerzeilen ist es sehr wichtig, daß im DAX Statement ein "" und kein BLANK() - wie sonst häufig Best Practice in DAX) - als SONST-Bedingung verwendet wird:

P&L Table Measure (Kern Variante) = 
VAR varSelection = SELECTEDVALUE('P&L Table'[Metric KEY])
RETURN
        If(varSelection <> Blank();
            CALCULATE(' Measures'[Sum Measure];
                FILTER('Dim P&L';'Dim P&L'[Metric KEY]=varSelection)
            );
        BLANK()
        )

Die Verwendung eines BLANK() als Ausgabewert führt zum Ausblenden der Leerzeilen, da jedes Visual in Power BI BLANK()-Werte unterdrückt:

Die zweite Besonderheit ist die Ermittlung von zusätzlichen Kennzahlen, die nur über Measures und nicht in der Dimensionsstruktur ermittelt werden können. Wir zeigen anhand der Kennzahl "EBIT in %" den Lösungsansatz. Da es sich um eine %-Kennzahl handelt, wird die %-Formatierung mit einem FORMAT()-Statement im Measure erreicht:

P&L Table Measure (% Kennzahlen) = 
VAR varSelection = SELECTEDVALUE('P&L Table'[Metric KEY])

RETURN
    SWITCH(
        TRUE();
        varSelection = "EBIT";
            CALCULATE([Sum Measure];'Dim P&L'[Level 5]="Operating Profit");
        varSelection = "EBIT in %";
            FORMAT(
                DIVIDE(
                    CALCULATE([Sum Measure];'Dim P&L'[Level 5]="Operating Profit");
                    CALCULATE([Sum Measure];'Dim P&L'[Metric NAME]="Revenue")
                );
                "0.0 %");
        BLANK()
    )

Das FORMAT()-Statement ist notwendig, weil es in Power BI bisher kein Cell-Level Formatting für Measures gibt (wie das aus den SQL Server Analysis Services bekannt ist). Das FORMAT() Statement erzeugt nämlich keine formatierte Zahl sondern einen Text, der lediglich aussieht wie ein Zahl. In vielen Szenarien ist das ein grosser Nachteil, da bspw. kein Conditional Formatting mehr möglich ist. Im vorliegenden Fall ist das allerdings ein Glücksfall, da das Aussetzen der Balkenformatierung auf den Zeilen für die %-Kennzahlen durchaus sinnvoll ist:

3. Evaluierung

Die Darstellung der P&L in Staffelform füllt eine wichtige Lücke in Power BI im Bereich des Financial Reportings. Die Umsetzung ist zwar nicht trivial, aber mit der entsprechenden Anleitung auch nicht schwierig. In vielen Fällen wird es sinnvoll sein, zusätzlich zur Staffelform (für Standardberichte) auch die hierarchische Darstellung (für Analysen) im Datenmodell abzubilden.

Leider gibt es auch einige Schwachstellen:

• Eine differenzierte Formatierung der Ergebniszeilen ist - nach unserem Kenntnisstand - derzeit weder im Matrix noch im Table Visual nicht möglich. Mit einem measure-basierten (dynamischen) Conditional Formatting können zwar die Zahlen dynamisch formatiert werden, nicht aber die Zeilenköpfe. Aber selbst für die Zahlen stünde nur die Formatierung mittels Hintergrundfarbe zur Verfügung, da das Conditional Formatting derzeit noch keine Schrift- oder Linienformatierung für die Zellen unterstützt. Aber möglicherweise fallen diese Einschränkungen ja in einem zukünftigen Release.
• Eine differenzierte Formatierung der %-Ergebniszeilen ist - ebenfalls nach unserem Kenntnisstand - derzeit nur über die DAX-Funktion FORMAT() möglich, da es bisher kein Cell-Level Formatting in Power BI gibt. Das Conditional Formatting bietet derzeit 5 Formatierungsmöglichkeiten (Hintergrundfarbe, Schriftfarbe, Datenbalken, Icons und Web URL), aber keine dynamische Anwendung von Zahlenformaten. Während für einen "KPI Table" diese Einschränkung massiv wäre, ist das für die Visualisierung der P&L glücklicherweise in vielen Fällen sogar ein Vorteil.
• Wird bspw. auf eine Firma gefiltert, die keine Buchungen bspw. im Bereich des Finance profit hat, so werden diese unbebuchten Zeilen der P&L automatisch unterdrückt. Die Leerzeilen bleiben aber unverändert erhalten was dazu führen kann, daß dann mehrere Leerzeilen hintereinander angezeigt werden. Eine mögliche Lösung liegt darin, die Option Show items with no data zu aktivieren (oder das Measure so zu adaptieren, daß immer alle Zeilen des P&L Tables mit einer "0" oder einem "" ausgegeben werden).
• Aufgrund der fehlenden Beziehung des P&L Tables ("Disconnected Table") zum Star Schema wird kein Drilldown bspw. auf die Kontenebene unterstützt. Aauch wird kein Crossfilter, keine Page Tooltips und kein Drillthrough unterstützt. Das sind massive Einschränkungen, wenn beispielsweise die Einzelbuchungen nachgeschlagen werden sollen.

Wir zeigen im Teil 2 dieser Beitragsserie, wie die zuletzt genannte Einschränkung durch eine relativ einfache Adaption der hier vorgestellten Lösungsvariante behoben werden kann.

Quellen:

Enterprise DNA: How To Create Unique Subtotals In Power BI Tables Using DAX