Interaktives HR Dashboard in Power BI – Teil 1

Teil 1: DAX-Patterns für Headcount, Ein-/Austritte und Fluktuation. Die Ermittlung des Headcount, die Visualisierung der direkten Einflußfaktoren Ein- und Austritte von Mitarbeitern sowie die Ermittlung von Kennzahlen wie der Fluktuation ist eine zentrale Aufgabe in jedem HR Reporting. Power BI bietet hierfür hervorragende Möglichkeiten nicht nur für die Berechnungen und Visualisierungen sondern vor allem auch für die interaktive Analyse bis ins Detail – und zwar sowohl am Smartphone, am Tablet als auch am Desktop.

In diesem ersten Teil der zweiteiligen Blogserie liegt der Fokus auf dem Datamodel und den DAX-Statements zur Ermittlung der Kennzahlen. Im zweiten Teil liegt dann der Fokus auf der Erstellung der Visualisierungen ausschließlich mit Power BI Bordmitteln (also ohne Custom Visuals).

1. Fertiges Dashboard – bitte klicken!

Zum Einstieg das fertige Dashboard des in diesem Blogbeitrag vorgestellten Showcases. Dieses wurde mit der Publish-to-Web Option im Power BI Service veröffentlicht und steht daher als interaktives Dashboard hier im Blogbeitrag zur Verfügung (bitte Säulen selektieren, Zeit-Slider verschieben, Drilldown durchführen, usw.):

2. Ausgangsdaten und Datenmodellierung

Die Faktentabelle ist eine Liste mit allen Ein- und Austritten des Unternehmens wobei ein Datensatz entweder einen Eintritt oder einen Austritt repräsentiert. Die beiden mit dem orangen Rahmen gekennzeichneten Spalten wurden in der zugrundeliegenden Query aus den Quelldaten abgeleitet, um die Visualisierungen möglichst effizient und interaktiv gestalten zu können. Wesentlich ist die Zusammenführung der beiden Datumsfelder Eintrittsdatum und Austrittsdatum auf ein einziges Feld Ein-/Austrittsdatum, damit in den Visualisierungen mit der gleichen Zeitachse gearbeitet werden kann (die Differenzierung erfolgt über die zusätzliche Spalte Transaktionstyp):

Für jeden Mitarbeiter steht über das Feld MA Nummer in der Dimension Mitarbeiter ein Eintrag mit Stammdaten („Attributen“) zur Verfügung, die für Auswertungen genutzt werden können.

Anstelle der AutoDate-Dimension in Power BI wird eine individuelle („CustomDate“) Datumsdimension verwendet und als „Date Dimension“ im Datamodel deklariert, um die sogenannten Time Intelligence DAX-Funktionen von Power BI nutzen zu können.

Die beiden Dimensionstabellen sind mit der Faktentabelle in einem klassischen Star Schema verbunden (mit 1:n Beziehungen und unidirektionaler Filterung).

Die vierte Tabelle ist der sogenannte Measure Table Berechnungen. Ein Measure Table ist eine Tabelle ohne Datensätze (und idR mit nur 1 Column) deren einziger Zweck die Aufnahme aller Measures ist:

3. Ermittlung der Basismeasures

Die Basismeasures werden als einfache Summe bzw. als gefilterte Summe aus der Faktentabelle abgeleitet:

• Ein-/Austritte = SUM(Faktentabelle[Ein-/Austritt])
• Eintritte = CALCULATE(
  [Ein-/Austritte];
  Faktentabelle[Transaktionstyp]=“Eintritte“
  )
• Austritte = CALCULATE(
  [Ein-/Austritte];
  Faktentabelle[Transaktionstyp]=“Austritte“
  )

Die Visualisierung erfolgt mittels Column Charts mit aktiver Rot-/Grün-Formatierung (mehr dazu in Teil 2):

4. Ermittlung des Headcount aus den Ein-/Austritten (DAX-Pattern „Running Total“)

Der Headcount wird aufgrund der Struktur des vorliegenden Datenmaterials in der Faktentabelle als sogenannter Running Total ermittelt, das ist die Summe aller Ein-/Austritte von Anbeginn bis zum jeweiligen Stichtag. Das DAX-Pattern für eine Running Total Ermittlung sieht so aus:

• Headcount = CALCULATE(
  [Ein-/Austritte];
  FILTER(
  ALL(‚Dimension Datum'[Datum]);
  ‚Dimension Datum'[Datum] <= MAX(‚Dimension Datum'[Datum])
  ))

Die Berechnung des Headcounts über die Datumsdimension funktioniert einwandfrei, es wird im aktuellen Jahr 2019 der tatsächliche Mitarbeiterstand von 104 Mitarbeitern ausgewiesen:

Mit der DAX-Funktion FILTER ist eine sogenannte Iterator-Funktion im Einsatz, die sehr CPU-intensiv ist und daher bei einer großen Anzahl zu ermittelnder Werte langsam ist. Die Berechnung der Werte beim Drilldown von der Jahres- auf die Datumsebene dauert dann etwa 10-20 Sekunden:

5. Anzeige der Ein-/Austritte der höchsten selektierten Periode (DAX-Pattern „Bestandslogik“)

Die zentralen KPIs des laufenden Jahres (bzw. des höchsten selektierten Jahres) sollen in einer Reihe von Card Visuals hervorgehoben werden. Für die Kennzahl Headcount wird automatisch der Letztstand (und nicht die Summe aller Headcounts) ausgewiesen, das DAX-Pattern für Running Total impliziert also bereits die sogenannte Bestandslogik. Für die anderen KPIs wird allerdings die Gesamtsumme über den gesamten Zeitraum der Faktentabelle ausgewiesen, was in diesem Fall aber nicht erwünscht ist:

Für die korrekte Anzeige benötigt es das DAX-Pattern der sogenannten Bestandslogik, es soll also auf der Total-Ebene nicht die Summe sondern der Wert der letzten Periode angezeigt werden.

• Aktuelles Jahr = CALCULATE(
  MAXX(Faktentabelle;Year(Faktentabelle[Ein-/Austrittsdatum]));
  ALL(‚Dimension Mitarbeiter‘)
  )

Hinweis: die Iterator-Funktion MAXX wird verwendet, um das Jahr aus dem höchsten Ein-/Austrittsdatum in einem Row Context ableiten zu können.

• Ein-/Austritte (Bestand) =
  VAR
  Highest_Year = [Aktuelles Jahr] RETURN
  IF(
  ISBLANK(Highest_Year);
  BLANK();
  CALCULATE(
  [Ein-/Austritte];
  ‚Dimension Datum'[Jahr]=Highest_Year
  ))
• Eintritte (Bestand) = (analoge Ermittlungsformel)
• Austritte (Bestand) = (analoge Ermittlungsformel)

Die Measures zeigen nun die Werte des höchsten Jahres in der aktuellen Selektion bzw. wenn kein Filter gesetzt ist, dann wird das höchste in der Faktentabelle vorkommende Jahr angezeigt:

6. Ermittlung der Fluktuation (DAX-Pattern „Period-over-Period“)

Für die Ermittlung der Fluktuation muss zuerst der Headcount am Beginn der Betrachtungsperiode (= Wert am Ende des Vorjahres) ermittelt werden:

• Headcount VJ = CALCULATE(
  [Headcount];
  DATEADD(‚Dimension Datum'[Datum];-1;YEAR)
  )
• Fluktuation = Divide([Austritte];([Headcount VJ] + [Headcount])/2) * -1
  Hinweis: natürlich ist dies nur eine von vielen möglichen Formeln zur Ermittlung der Fluktuation.

Die Visualisierung erfolgt mit einem Stepped Line Chart im Sparkline Format (mehr dazu im Teil 2):

Fazit

Die Ermittlung der HR Kennzahlen in diesem Showcase ist nicht besonders schwierig und erfordert lediglich mittleres DAX Know-How.

Der Beitrag ist urspünglich auf dem Blog von Linearis erschienen. Weitere Infos unter: www.linearis.at

Weiterbildungstipp

Certified Reporting Professional | Steigern Sie die Effektivität und Effizienz Ihres Berichtswesens
Wann? Start am 25. September 2019 | Wo? Seminarhotel & Palais Strudlhof, 1090 Wien, Eingang: Strudlhofgasse 10