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...

Lern Video Im folgenden wird eine Methode aufgezeigt, wie man mittels Power Pivot und einem Measure eine dynamische Segment Analyse durchführen kann. Ziel dabei ist es, die Anzahl der Aufträge je Datum nach ihrem Auftragswert zu segmentieren (Segmente Datenschnitt [Display] und zu analysieren (Pivottabelle, analytischer Bericht) 1 Tabelle mit Segmenten anlegen und ins Power Pivot Modell übernehmen 2 Datumstabelle in Power Pivot anlegen und Verknüpfung über Feld [Datum] (hier: [Bestelldatum], [Date] N:1) herstellen 3 Measure in Power Pivot anlegen Anzahl Aufträge segmentiert:=IF (     HASONEVALUE ( tbl_Segment[ID] );     COUNTROWS (         FILTER (             tbl_Auftragsdaten;             tbl_Auftragsdaten[Auftragswert] < VALUES ( tbl_Segment[bis] )         )     );     BLANK () ) 4 Pivot Tabelle auf Basis Power P...

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

Lern Video Ausgangstabelle 1 Verzug berechnen, Fallunterscheidung: [Wareneingangsdatum] = NULL -> Verzug NULL [Wareneingangsdatum] = [stat_Lieferdatum] -> Verzug = 0 [stat_Lieferdatum] < [Wareneingangsdatum] -> Verzug fxArbeitstage([stat_Lieferdatum],[Wareneingangsdatum] [stat_Lieferdatum] > [Wareneingangsdatum] -> Verzug fxArbeitstage([Wareneingangsdatum],[stat_Lieferdatum]) *(-1) 2 Lieferzeit fxArbeitstage([Bestelldatum],[Wareneingangsdatum]) Language M Code (Power Query) let fxArbeitstage = (start as date, end as date, optional Feiertage as list) as number =>         let            Liste_Feiertage = if Feiertage = null then {} else Feiertage,            Liste_Tage = {Number.From(start)..Number.From(end)},            Liste_Differenz  = List.Difference(Liste_Tage, Liste_Feiertage),       ...

Power Pivot tabellarisches Modell

Youtube Channel Excel Power BI Excel war und ist eine gute Wahl, um Geschäftsdaten in aussagekräftige Auswertungen / Analysen / Berichte zu überführen. Viele verdammen es, einige würden Excel gar gerne aus dem Controlling verbannen. Schaut man sich aber die Realität in Unternehmen an wird man feststellen, dass das oft nur ein frommer Wunsch ist. Das Thema Datenbeschaffung, Modellierung, Analyse und Visualisierung durch den Fachbereich hat in den letzten Jahren massiv Fahrt aufgenommen, mit Excel Power BI ! Diese neuen BI Funktionalitäten sind eindeutig dem Self Service Gedanken zuzuordnen: ein Fachbereich kann nun seine Controlling Anforderungen eigenständig umsetzen, ohne die meist stark beanspruchten IT Kapazitäten über Gebühr zu beanspruchen. Kurzum, mit Excel Power BI kann eine (Self Service) Business Intelligence Strategie in wenigen Schritten umgesetzt werden: Geschäftsdaten sammeln, integrieren, bereinigen, einschränken und kombinieren mit  Excel Power Query ...

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...

Mit Pivot Tabellen kann man ausschließlich sogenannte symmetrische Berichte aufbauen. Befinden sich im Wertebereich z.B der Rechnungswert, kann in den Zeilen nach Materialgruppen (Mechanik, Normhalbzeug usw) und Artikel (Zahnrad, Ritzel usw) in den Spalten in Jahr und Monate untergliedert werden. Symmetrisch wird dieser Aufbau deshalb genannt, weil jedem Jahr (2014 und 2015) alle Monate hierarchisch untergeordnet sind. Somit gibt es jeden einzelnen Monat (1 - 5) in jedem einzelnen Jahr (2014 - 2015). Will man jedoch das Vorjahr (2014) als einzelne Spalte (in Summe), das laufende Jahr (2015) aufgegliedert in einzelne Monate (1 - 5) sehen, spricht man von einem asymmetrischen Bericht. Diese Anforderung kann man mit Gruppen (sets) lösen, wenn die Pivottabelle auf einem Excel Power Pivot Modell basiert. Reiter Analysieren -> Felder, Elemente und Gruppen -> Gruppen verwalten Die erstellten Sets (Gruppen) werden in der Feldliste der Pivottabelle angezei...

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

Standardmäßig ist die maximale Anzahl abzurufender Datensätze bei einem DrillDown (OLAP-Drillthrough) auf 1.000 Datensätze beschränkt. Ist dies bei großer Anzahl Datensätze nicht ausreichend, kann man diese Anzahl erhöhen

Nach einem Drill Down (Aufriß) eines measures (Pivottabelle) werden die Spaltenbezeichnungen in der Form Tabellenname.Feldname -> Beispiel [$Tabelle1].[Artikel] in einer neuen Excel Tabelle aufgelistet. Diese Spaltenbezeichnungen sind nicht sehr benutzerfreundlich. Wenn man die Spaltenbezeichnungen markiert und folgendes VBA Snippet anwendet, werden die Spaltenbezeichnung auf die Feldbezeichnungen Feldname -> Artikel reduziert. Public Sub Spalten_Bereinigung_DrillDown() Dim rng As Range For Each rng In Selection     intPunkt = InStr(1, rng.Value, ".") + 2     rng.Value = Mid(rng.Value, intPunkt, Len(rng.Value) - intPunkt) Next End Sub

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]