Excel im betrieblichen Umfeld

in Power BI Desktop in die Modellansicht wechseln, neue Tabelle -> DAX einfügen:  (hier auf Basis Abfrage (Power Query) Name financials, Feldname [Date], entsprechend anpassen bei Übernahme in eigenes Datenmodell) --- SCHNIPP mit ADDCOLUMNS()  ---  Kalender_DAX =   //VAR StartDatum = EDATE(TODAY(),-48)  //VAR EndDatum = EDATE(TODAY(),48)  //VAR StartDatum =DATE(2013,1,1)  //VAR EndDatum = DATE(2014,12,31)  //oder mit DAX Funktionen FIRSTDATE,LASTDATE   VAR StartDatum = FIRSTDATE(financials[Date])  VAR EndDatum = LASTDATE(financials[Date])    RETURN     ADDCOLUMNS(         CALENDAR(StartDatum,EndDatum),         "Jahr", YEAR([Date]),         "GJ" , YEAR(EDATE([Date],9))-1 & "/" & YEAR(EDATE([Date],9)),         "Jahr Monat", YEAR([Date])  &"."& MONTH([Date]),         "Quartal", QUARTE...

Im folgenden wird beschrieben, wie man mit Power Pivot eine dynamische Gantt-Darstellung erstellen kann 1 Ausgangstabelle [tbl_Aufgaben] erstellen und zu Datenmodell hinzufügen 2 Tabelle für einzelne Tage [tbl_Tage] erstellen und in Datenmodell hinzufügen 3 folgende Spalten im Datenmodell  zu [tbl_Aufgaben] hinzufügen: [Status] =IF(tbl_Aufgaben[Prozent_Fortschritt]=1;"abgeschlossen";IF(tbl_Aufgaben[Ende]<TODAY() && tbl_Aufgaben[Prozent_Fortschritt]<1;"überfällig";"offen") [Faktor] =SWITCH(tbl_Aufgaben[Status];"abgeschlossen";2;"überfällig";-1;1) [Prozent] =IF(tbl_Aufgaben[Prozent_Fortschritt]=BLANK();0;tbl_Aufgaben[Prozent_Fortschritt]*100) & "%" 4 Datumstabelle [Kalender] im Datenmodell erstellen, mit [tbl_Tage] verknüpfen (hier: Felder [Kalender].[Wochentag] N, [tbl_Tage].[Tag] 1) und [Kalender] um folgende Spalten ergänzen [Woche_endet_am] =DATEADD(Kalender[Date];RE...

Periodenvergleiche können mit Excel Power Pivot mit der DAX Funktion DATEADD() umgesetzt werden Beispiel [Faktentabelle] 1 [Datumstabelle] mit Power Pivot erstellen 2 [Faktentabelle] und [Datumstabelle] über Datumsfeld verknüpfen 3 Measures in Faktentabelle anlegen Wert_Vorjahr:=CALCULATE ( SUM ( Tabelle1[Wert] ); DATEADD ( Kalender[Date]; -1; YEAR ) ) Wert_Vorvorjahr:=CALCULATE ( SUM ( Tabelle1[Wert] ); DATEADD ( Kalender[Date]; -2; YEAR ) ) Wert Vorquartal:=CALCULATE ( SUM ( Tabelle1[Wert] ); DATEADD ( Kalender[Date]; -1; QUARTER ) ) Wert Vormonat:=CALCULATE ( SUM ( Tabelle1[Wert] ); DATEADD ( Kalender[Date]; -1; MONTH ) ) 4 Ergebnis (Pivottabelle, vereinfachte Pivottabelle) Quelle https://powerpivotinsights.de/dateadd/ alternativ (getestet mit Power BI Desktop) DAX  YoY(Jahresvergleich) =   var _prev =   IF (   NOT ( ISBLANK ( [Umsatz] )),   CALCULATE ( [Umsatz] , PREVIOUSYEAR ( Kalender_DAX [Date] )  ))   RETURN...

Normalerweise werden Daten aus unterschiedlichen Datenquellen (Excel Tabellen usw) in das Power Pivot Datenmodell geladen, um sie anschließend mit einer Pivot Tabelle auszuwerten. Jedoch kann es immer wieder vorkommen, dass man die sorgfältig zusammengetragenen und aufbereiteten Daten aus dem Datenmodell herausbekommen will, um sie mit weiteren tools oder klassischem Excel weiter bearbeiten zu können. Das kann u.a. dann sinnvoll sein, wenn verschiedene Datenquellen mit Power Pivot in Relation gesetzt und angereichert worden sind. Hierzu folgendermaßen vorgehen: 1 Reiter Daten, externe Daten abrufen, vorhandene Datenverbindungen 2 Tabelle in neues Arbeitsblatt laden 3 Tabelle wird angelegt und befüllt, Abfrage kann bearbeitet werden (DAX) 4 rechte Maustaste auf Tabelle ->  Tabelle -> DAX bearbeiten EVALUATE Tabellenname Power Pivotmodell gibt alle Daten zurück Beispiele für DAX Ausdrücke EVALUATE tbl_Material ...

Die Kenntnis über das Datum der letzten Aktualisierung ist zwingend notwendig, um Kennzahl richtig interpretieren zu können. Falls ein solches Datum nicht vorhanden ist, kann dieses mit folgendem Trick aus dem Datenmodell bezogen werden: In folgendem Beispiel liegt das Belegdatum der Bestellungen vor. Mittels einer DAX Funktion kann ein measure gebildet werden, welches das letzte Belegdatum ermittelt Mit Hilfe einer CUBE Funktion kann dieses ausgelesen werden: =CUBEWERT("ThisWorkbookDataModel";"[Measures].[letztes_Belegdatum]") Mittels TEXT Funktion kann das Datum im richtigen Fomat dargestellt werden: ="letztes Bestelldatum: " & TEXT(CUBEWERT("ThisWorkbookDataModel";"[Measures].[letztes_Belegdatum]");"tt.MM.jjjj")

Im Folgenden wird eine Methode mit Power Pivot und den DAX Funktionen PATH(), PATHITEM(), LOOKUPVALUE() beschrieben, um ein Merkmal / Dimension zu hierarchisieren 1 Tabelle 1, Tabelle 2 zum Datenmodell hinzufügen (Power Pivot) 2 Tabelle 1, Spalte hinzufügen DAX Funktion =PATH([Warengruppe];[Parent]) 3 weitere Spalten für Ebenen hinzufügen (hier: Spalten Ebene1, Ebene2, Ebene3, Ebene4) =LOOKUPVALUE(Tabelle2[Warengruppe_Text];Tabelle2[Warengruppe];PATHITEM(Tabelle1[Pfad];1)) Exkurs LOOKUPVALUE() 4 in Diagrammsicht wechseln und neue Hierarchie erstellen erweiterter Ansatz Unschön sind dabei leere (Unter-) Zweige (gelb markiert) Diese können durch folgende Erweiterung des Modells vermieden werden berechnete Spalte anlegen (Name HierarchieTiefe): = Pathlength ( Tabelle2[Pfad] ) 3 neue Measures anlegen: BaumTiefe:=ISFILTERED ( Tabelle2[Ebene1] ) + ISFILTERED ( Tabelle2[Ebene2] )     + ISFILTERED ( Tabelle2[Ebene3] ) + ISFI...

Im Standard ist es nicht möglich, in einem Pivot Chart den Mittelwert einer Zahlenreihe abzubilden. Mit Hilfe von Power Pivot und den DAX Funktionen CALCULATE(), AVERAGE() und ALL() kann man diese Anforderung dennoch umsetzen. Die Vorgehensweise dabei ist wie folgt: 1 Tabelle über Reiter Power Pivot zum Datenmodell hinzufügen: 2 Mittels DAX Funktionen ein measure Mittelwert bilden, welches nicht auf Filter reagiert: Mittelwert:=CALCULATE(AVERAGE(Tabelle1[Wert]);All(Tabelle1)) Exkurs DAX ALL() Funktion 3 auf Basis des tabellarischen Modells ein Pivot Chart erstellen 4 Aufriss Pivot erstellen 5 measure Mittelwert als Linien Chart abbilden Exkurs Power Pivot DAX Funktionen

Power Pivot tabellarisches Modell

Die SQL Funktion IN ist nützlich, wenn man zB eine Gruppe von Werten testen / auswerten will. Jedoch existiert eine solche Funktion nicht in der Formelsprache DAX. SQL-Statement := SELECT DISTINCT MaterialgruppeName FROM Materialgruppe WHERE Materialgruppe IN ('Zahnrad', 'Ritzel', 'Steckhuelse' ) Man kann die SQL IN Funktion stattdessen aber mit verschachtelten OR Funktionen i nDAX abbilden CALCULATETABLE (     VALUES ( Materialgruppe[MaterialgruppeName] ),     OR (         OR (             Materialgruppe[MaterialgruppeName] = "Zahnrad",             Materialgruppe[MaterialgruppeName] = "Ritzel"         ),         Materialgruppe[MaterialgruppeName] = "Steckhuelse"     ) ) Als Alternative kann man auch den logischen Operator || für OR verwenden VALUES ( Materialgruppe[MaterialgruppeName] ),     Mater...

Der BLANK Wert ist ein spezieller Wert, welchem man vor allem bei Vergleichen besondere Aufmerksamkeit schenken sollte. BLANK in Power Pivot DAX ist nicht gleichbedeutend mit dem NULL Wert in SQL. Was ist ein BLANK Wert in DAX ? Jede Datentyp außer der BOOLEANsche (TRUE / FALSE) in DAX kann einen BLANK Wert enthalten. BLANK wird zugewiesen, wenn die Datenquelle einen NULL Wert enthält. Wenn man einen DAX Ausdruck verwendet wird ein BLANK Wert immer zu 0 oder Leerstring konvertiert, je nachdem welchen Datentyp der Ausdruck erwartet. Man erhält / erzwingt einen BLANK Wert in DAX, wenn man die DAX BLANK() Funktion verwendet. Die folgende Tabelle zeigt das Ergebnis mehrerer DAX Ausdrücke, die einen BLANK Wert enthalten: Ausdruck Ergebnis BLANK() BLANK BLANK()=0 TRUE BLANK() && TRUE FALSE BLANK || TRUE TRUE BLANK()+1 -1 BLANK()-1 -1 BLANK()/4 BLANK INT(BLANK()) BLANK Vergleiche mi...

Um eine Auflistung aller in einem Power Pivot Modell verwendeten measures / DAX Formeln zu erhalten, folgendermaßen vorgehen (Excel 2013): Reiter Daten -> externe Daten abrufen -> vorhandene Verbindungen, auf Reiter Tabellen wechseln, eine Datenverbindung auswählen und öffnen   Nachdem die Daten importiert wurden rechte Maustaste -> Tabelle -> DAX bearbeiten und als Ausdruck SELECT *  FROM $SYSTEM.MDSCHEMA_MEASURES hinterlegen sowie mit OK bestätigen. Es wird eine Tabelle angelegt. Die Spalten MEASURE_NAME (Bezeichnung der Kennzahl) und EXPRESSION (DAX Formel) enthalten Namen und Formeln aller im Modell verwendeten measures Lern Video

Unter Datenqualität versteht man die Vollständigkeit, Richtigkeit, Widerspruchsfreiheit (Konsistenz) und Aktualität von Daten. Datenqualität ist eine notwendige Bedingung für Informationsqualität. Zur Unterscheidung von Daten und Informationen siehe meinen blog Beitrag auf data-science-blog.com . Excel Power Pivot kann zur ersten Überprüfung von Datenqualität genutzt werden. Eindeutigkeit von Werten einer Spalte prüfen (Uniqueness), Dublettensuche Vollständigkeit von Werten einer Spalte überprüfen

Vollständigkeit von Werten in Spalten ist ein Kriterium der Datenqualität. Diese kann mit der DAX Funktion COUNTBLANK() überprüft werden. Beispiel Tabelle1 measures: Materialgruppe nicht zugeordnet:=COUNTBLANK([Materialgruppe]) Materialgruppe zugeordnet:=COUNTROWS(Tabelle1)-[Materialgruppe nicht zugeordnet] Zeilensumme:=[Materialgruppe zugeordnet]+[Materialgruppe nicht zugeordnet]

Youtube Channel Excel Power BI Siehe Einführung in DAX  für detaillierte Informationen Im folgenden werden nützliche DAX Funktionen aufgelistet siehe auch Microsoft DAX Syntaxspezifikationen für Power Pivot DAX Reference Cheat Sheet (Power BI Dashboard) DAX Guide (Marco Russo) DAX Tutorial (Learn Excel DAX) ALL() gibt alle Zeilen in einer Tabelle oder alle Werte in einer Spalte zurück, wobei alle möglicherweise angewendeten Filter ignoriert werden. ALLEXCEPT() gibt alle Zeilen in einer Tabelle mit Ausnahme der Zeilen zurück, die bei Anwendung der angegebenen Spaltenfilter gefunden werden AND()                                                           logischer Operator, überprüft, ob alle Agumente TRUE                                   ...

Die Anzahl eindeutiger Werte kann mit Hilfe einer sog. Matrixformel ermittelt werden. Matrixformeln werden mit der Tastenkombination STRG-SHIFT-RETURN abgeschlossen. Erkennbar ist eine gültige Matrixformeln an den geschweiften Klammern um die Formel: {=SUMME(1/ZÄHLENWENN(BEREICH;BEREICH))} Alternativ kann die Anzahl der eindeutigen Werte mit Power Pivot unter Einsatz der DAX Formel DISTINCTCOUNT() ermittelt werden.

Anstatt mehrere IF() >Verschachtelungen mit DAX zu verwenden, kann man alternativ die DAX Funktion SWITCH() verwenden. Der Vorteil gegenüber IF() besteht in einer besseren  Lesbarkeit bei vielen Bedingungen. Verknüpft man das ganze mit der booleanschen Funktion TRUE() können sogar komplexe Szenarien mit DAX umgesetzt werden. Zur Veranschaulichung anhand eines Beispiels aus der Praxis siehe folgenden Blog-Beitrag: Lieferantenbewertung, Kennzahlenbereichen Noten zuweisen

Mittels einer Kombination aus den DAX Funktionen CALCULATE() und FILTER() ist es möglich, mehrere Filterbedingungen auf eine Tabelle anzuwenden. Im gewählten Beispiel ermittelt das measure "Anzahl QABs" (= COUNTROWS() ) die Anzahl der Zeilen, für die gilt: Feld(inhalt) "Status" ist nicht "gelöscht" UND Feld(inhalt) "Fehler" = "JA"

Wenn man zwei Tabellen in Power Pivot in Relation setzen will, müssen die Werte des Primärschlüssel Feldes der einen Tabelle eindeutig sein (uniqueness). Was aber, wenn das bei angebundenen Tabellen nicht der Fall ist ? Wie finde ich die Dubletten mit DAX Funktionen innerhalb meines Power Pivot Modells ?  =COUNTROWS(FILTER(Tabelle1;Tabelle1[Objekt]=EARLIER(Tabelle1[Objekt])))

Manchmal wird z.B. im Lieferanten Controlling gefordert, dass Kennzahlen in bestimmten Wertebereichen Noten oder Punkte zugewiesen werden sollen. Ziel dieser Übung ist es, eine quantitative Größe (Kennzahl) zu qualifizieren (gut - schlecht), um deren Interpretation zu vereinfachen und zu standardisieren. Dies ist zB bei einer Lieferantenbewertung regelmäßig der Fall. Eine Notenzuweisung kann mit den verschachtelten DAX Funktionen SWITCH(), TRUE() und AND() erfolgen. SWITCH() leitet eine Fallunterscheidung ein, TRUE() ist ein booleanscher Wert (JA,NEIN), mit AND() kann man Wertebereiche abgrenzen (Intervalle). Die komplette Funktion (measure) lautet: Note:=SWITCH(TRUE();[Anzahl QAB]=0;1;AND([Anzahl QAB]>=1;[Anzahl QAB]<=2);2;AND([Anzahl QAB]>2;[Anzahl QAB]<=4);3;6) Erläuterung: Anzahl QAB = 0 := Note 1 Anzahl QAB größer 1 und kleiner 2 := Note 2 Anzahl QAB >2 und kleiner 4 := Note 3 Anzahl > 4 := Note 6

Mit der DAX Funktion LOOKUPVALUE() können Werte in einer anderen Tabelle nachgeschlagen werden, ohne dass beide Tabellen über ein identisches Feld (wie bei RELATED() notwendig; Primär-, Fremdschlüssel) miteinander in Relation stehen. Praxisbeispiel (ein Element, hier Feld [MANr], kommt mehrmals in einer Tabelle vor, zB 1222) Angenommen, man will für Elemente des Feldes Artikel (Tabelle Artikel) nur diejenigen Elemente eines Feldes MANr (Tabelle MA) zuordnen, für die gilt: MA[Gruppe] beginnt mit M (Rolle MGM) Schritt 1 Spalte zur Unterscheidung der einzelnen Gruppen anlegen (Spalte Rolle) Schritt 2 LOOKUPVALUE() in Tabelle Artikel, Feld [Rolle_MGM] aufbauen erster Parameter = Rückgabewert aus Tabelle MA, Feld Rolle zweiter Parameter = Tabelle MA, Feld [MANr] = Tabelle Artikel, Feld [MANr] dritter Paramter = Tabelle MA, Feld [Rolle] = M (für Rolle MGM) Eine Relation zwischen den Feldern Tabelle Artikel[MANr] und MA[MANr] ist in diesem Falle nicht möglich, da d...