Jahresbudget in Power BI auf Monate verteilen: Teil 3 Query-basierte Lösung

Die Verteilung von Jahreswerten auf Monate oder von Monatswerten auf Tage ist eine häufige Anforderung in Power BI.

Die Umsetzung in Power BI kann entweder DAX-basiert oder query-basiert erfolgen. In Teil 1 haben wir gesehen, daß die DAX-basierte Verteilung mit einem relativ einfachem DAX-Statement umgesetzt werden kann. In Teil 2 haben wir festgestellt, daß die eigentliche Herausforderung in den darauf aufbauenden Folgeberechnungen wie bspw. einer Year-to-Date oder einer Running Total Berechnung liegt. In diesem Teil 3 betrachten wir jetzt die query-basierte Lösung, die in vielerlei Hinsicht die praxistauglichste Lösung darstellt.

1. Ausgangssituation

Zur Erinnerung nochmals die Beschreibung der Ausgangssituation: Die IST-Daten liegen auf Monatsebene vor, die BUDGET-Daten als Jahreswerte. Diese Jahreswerte sollen auf Basis der Anzahl der Kalendertage auf Monate verteilt werden:

Das Datenmodell ist ein sehr simples Star Schema mit 1 Faktentabelle und nur 1 Dimensionstabelle - letztere wurde auch als Date Table zur Nutzung der Time Intelligence DAX-Funktionen definiert. Die Measures sind - wie in all unseren Showcases - in einem (ansonsten leeren) Measure Table eingehängt:

Die Faktentabelle enthält Datensätze für IST- als auch BUDGET-Daten, die Trennung erfolgt über die Spalte Scenario:

Die IST- und BUDGET-Werte werden über 2 Measures getrennt ausgewertet, damit es zu keiner Summierung der beiden Größen kommen kann:

ACTUAL = 
    CALCULATE(
        SUM('Fact Sales'[Value]);
        'Fact Sales'[Scenario]="ACTUAL"
    )
BUDGET (imported) = 
    CALCULATE(
        SUM('Fact Sales'[Value]);
        'Fact Sales'[Scenario]="BUDGET"
    )

2. Erzeugen der 12 Monatsdatensätze für das Budget

Im ersten Schritt werden in der zugrundeliegenden Query die beiden Spalten Date und Value auf das Suffix (temp) umbenannt, damit diese Spaltenbezeichnungen frei werden für die Felder mit den aufgeteilten Budgetwerten:

Der zentrale Vorgang in diesem Lösungsansatz ist das Erzeugen einer Liste mit den Einträgen 1-12 auf jedem Datensatz der Faktentabelle. Dafür wird eine Custom Column mit dem folgenden sehr simplen Syntax zur Erzeugung einer Liste verwendet:

Zur Verdeutlichung kann in ein Feld - in die leere Fläche, rechts vom Hyperlink List - geklickt werden, um die 12-zeilige Liste auch tatsächlich sehen zu können:

Nun wird diese Column mit dem Befehl Expand to New Rows expandiert ...

... was dazu führt, daß jeder ursprüngliche Datensatz 12-fach dupliziert wird und sich vorerst nur durch den Inhalt der Spalte Month Multiplier unterscheidet:

Jetzt wird eine Filterspalte angelegt, um nur die 12-fachen Datensätze für die BUDGET Werte zu behalten und die Vervielfachung der ACTUAL Werte rückgängig zu machen:

Die Spalte wird anschließend auf den Wert TRUE gefiltert. Alternativ hätte auch bereits bei der Erzeugung der 12-zeiligen Liste eine Bedingung für BUDGET und ACTUAL eingesetzt werden können.

3. Finalisieren der Berechnungen

Die Datensatzgranularität stimmt jetzt, es braucht jetzt nur noch die konkrete Ermittlung der beiden Spalten Date und Value.

Die finale Spalte Date kann recht einfach mit dieser Custom Column ermittelt werden:

Zur Berechnung der auf Monate aliquotierten Budgetwerte werden die beiden Hilfsspalten Days in Month und Days in Year angelegt:

Die Berechnung der aliquotierten Budgetwerte erfolgt jetzt in der neuen Custom Column Value:

Hier zur Verdeutlichung die Herleitung der aliquotierten Budgetwerte:

Zum Abschluß werden jetzt nur noch alle (temp) Spalten entfernt:

4. Ergebnis

Das Ergebnis sind "ganz normale" (= importierte) Monatsdatensätze für die Budgetwerte:

Auf Basis dieser granularen Datensatzstruktur können jetzt sämtliche Standard DAX Funktion für Year-to-Date, Year-over-Year, Running Total usw. verwendet werden - und zwar ohne viel Zeit in fortgeschrittene DAX Lösungen investieren zu müssen. Natürlich können in dieser Lösungsvariante auch die Quick Measures ohne weiteres genutzt werden.

Fazit

Die query-basierte Aliquotierung der Budgetwerte ist genauso einfach umzusetzen wie die DAX-basierte, jedoch mit dem Riesenvorteil, daß sich keine komplizierten Folgethemen auftun. Weiters ist im query-basierten Ansatz die Transparenz zur Herkunft sämtlicher Werte sehr einfach zu gewährleisten.

Für mich hat sich daher in der Praxis eindeutig die query-basierte Lösung als klarer Favorit herauskristallisiert. Feedback und Meinungen sind herzlich willkommen!

Über den Autor

Blog auf Feedly abonnieren

Kategorien

Verwandte Beiträge

Power BI Camp - Präsenztrainings in Wien und Nürnberg!

Dashboarding mit Power BI, DAX & Datenmodellierung und Power Query - als einzelnes Modul oder als ganze Trainingswoche!

Nürnberg 07. bis 11. Juni 2021
Wien 21. bis 25. Juni 2021

Jetzt buchen und -5 % Last Minute Rabatt lukrieren.
Jetzt buchen
Share on linkedin
Share on twitter
Share on facebook

Leave a Replay

Schreibe einen Kommentar

Kostenlos zum Newsletter anmelden

Ihre Anfrage

Schicken Sie uns Ihre Fragen und Anregungen!