Stellen Sie mir hier Ihre Fragen zu Power BI. Senden Sie mir Ihre Frage einfach per E-Mail an frage@jantrummel.de.
Ich werde alle Fragen selbstverständlich anonymisiert veröffentlichen, sodass keine Rückschlüsse auf Sie, Ihr Unternehmen oder Ihre Daten gezogen werden können.
Dieser Service ist für Sie kostenfrei!
Fragen aus dem April 2023
Durchschnittslinie
Frage vom 06.04.2023
Hallo Herr Trummel,
für unseren Krankenstand (der monatlich immer etwas in seiner Darstellung erweitert und verschönert wird) stehe ich gerade vor folgender Herausforderung:
Die Durchschnittslinie kann ich zwar einfügen, sie errechnet den Mittelwert aus den drei Monatswerten (3,22 + 9,88 + 8,36 = 21,46 / 3 =7,15%).
Der gewichtete Durchschnitt, der sich rechnerisch korrekt aus der Summe der Werte Jan, Feb und März ergibt, weicht davon so gut wie immer ab. Den bekomme ich mit 7,09% zwar errechnet, aber ich habe keinen Weg gefunden ihn auch als Linie (analog zur Ziellinie) anzuzeigen.
Wissen Sie wie es gehen kann?
Schöne Ostern und vielen Dank im Voraus😊
Antwort
Gehen wir einmal von folgenden Daten aus:
| Datum | Betrag |
|---|---|
| 01.01.2022 | 220,00 € |
| 01.02.2022 | 632,00 € |
| 01.03.2022 | 647,00 € |
| 01.04.2022 | 229,00 € |
| 01.05.2022 | 231,00 € |
| 01.06.2022 | 880,00 € |
| 01.07.2022 | 306,00 € |
| 01.08.2022 | 777,00 € |
| 01.09.2022 | 788,00 € |
| 01.10.2022 | 631,00 € |
| 01.11.2022 | 737,00 € |
| 01.12.2022 | 702,00 € |
| 01.01.2023 | 209,00 € |
| 01.02.2023 | 177,00 € |
| 01.03.2023 | 800,00 € |
| 01.04.2023 | 310,00 € |
| 01.05.2023 | 360,00 € |
| 01.06.2023 | 121,00 € |
| 01.07.2023 | 216,00 € |
| 01.08.2023 | 320,00 € |
| 01.09.2023 | 435,00 € |
| 01.10.2023 | 377,00 € |
| 01.11.2023 | 300,00 € |
| 01.12.2023 | 785,00 € |
Außerdem habe ich eine Kalendertabelle, welche mit dem UmsatzTabelle über eine 1-N-Beziehung verknüpft ist.
In einem Diagramm möchte ich den Umsatz (als Balken) und den Durchschnittsumsatz (als Linie) zeigen. Betrachten möchte ich immer nur ein bestimmtes Jahr.
Ich lege folgendes Measure an:
Durchschnittsumsatz =
CALCULATE(
AVERAGE('Umsätze'[Betrag]),
ALLEXCEPT(Kalender, Kalender[Jahr])
)Mithilfe der Funktion ALLEXCEPT entferne ich den Filterkontext für alle Spalten der Kalendertabelle (mit Ausnahme der Spalte Jahr).
Ich wähle ein Diagramm vom Typ Linien- und gruppiertes Säulendiagramm. Über einen Datenschnitt kann ich jeweils ein Jahr auswählen.
Das ist mein Ergebnis:
Fragen aus dem März 2023
SQL Unterabfrage Problem
Frage vom 20.03.2023
Hallo Herr Trummel
ich hoffe es geht Ihnen gut.
Sehr wahrscheinlich haben wir die folgende frage bereits in ihrem Kurs Anfang des letzten Jahres gehabt. Ich habe folgendes Problem:
Ich möchte aus der Datenbank ... zusätzlich zu den bereits angezeigten Zahlen aus Monat 1 bis 12 noch zusätzlich als eigene Spalte die entsprechenden Amount_Balance aus Monat 0 (EB/SALDO).
Hier das Beispiel wie wir es bisher händisch in EXCEL erstellt haben.
| Konto | Konto Bezeichnung | EB/SALDO | Sollsaldo | Habensaldo | Saldo |
| 503014 | xxxx | 6733248,42 | 0,00 | 0,00 | 6733248,42 |
| 503016 | xxxx | 228950,06 | 115706,84 | 115706,84 | 228950,06 |
Antwort
Gehen Sie von folgenden Daten in der Tabelle Buchhaltung aus:
Ich nutze jeweils 2 Unterabfragen, die ich über einen INNER JOIN verbinde.
In der Unterabfrage Daten filtere ich auf die Monate 1 - 12.
In der Unterabfrage Monat_0 filtere ich auf den Monat 0. Aus dieser frage ich nur die Spalte Saldo ab.
Die Abfragen verbinde ich jeweils über die KontoNr.
SELECT
Daten.KontoNr, Daten.Konto, Daten.Soll, Daten.Haben, Daten.Saldo,
Monat_0.Saldo AS 'EB/SALDO'
FROM
(
SELECT
KontoNr,
Konto,
SUM(Soll) AS Soll,
SUM(Haben) AS Haben,
SUM(Saldo) AS Saldo
FROM
Buchhaltung
WHERE
Jahr = 2022 AND
Monat BETWEEN 1 AND 12
GROUP BY
KontoNr, Konto
) AS Daten
INNER JOIN
(
SELECT
KontoNr,
Konto,
SUM(Saldo) AS Saldo
FROM
Buchhaltung
WHERE
Jahr = 2022 AND
Monat = 0
GROUP BY
KontoNr, Konto
) AS Monat_0
ON
Monat_0.KontoNr = Daten.KontoNr
ORDER BY
Daten.Konto;Hinweis: SQL-Code stammt von MS SQL Server.
Hier das Abfrageergebnis:
Fragen aus dem Februar 2023
Abfrage Measure
Frage vom 21.02.2023
Guten Morgen Herr Trummel,
gibt es die Möglichkeit, ein Measure zu schreiben, welches ein Datum aus diversen Zeilen erkennt und anhand des aktuellen Datums abgleicht, ob ein Datum „abgelaufen“ ist ?
Beispiel
| Datum |
|---|
| 15.02.2023 |
| 31.01.2023 |
| 28.02.2023 |
| 31.03.2023 |
Anhand des heutigen Datums, 21.02.2023, sollte das Measure den Wert ausgeben, dass 2x Einträge abgelaufen sind (31.01., 15.02.).
Antwort
Verwenden Sie folgendes Measure:
Abgelaufene Datumsangaben =
CALCULATE(
COUNTROWS(Datumsangaben),
Datumsangaben[Datum] < TODAY()
)Die Formel zählt alle Zeilen der Tabelle Datumsangaben, bei denen das Datum kleiner ist als das heutige Datum (Funktion TODAY).
Die Antwort (Stand heute) ist 2.
Kennzahlen Wochenproduktion errechnen
Frage vom 15.02.2023
Guten Morgen Herr Trummel,
ich habe eine Frage zu einer Berechnung in Power BI.
Meine Tabellen sehen als sehr vereinfachtes Beispiel wie folgt aus:
Diese Tabelle wird nach jeder Schicht durch Eingaben in eine Power App gefüllt.
Ich würde jetzt gerne in meinem Bericht ausweisen (wird jeden Tag morgens vom Vorgesetzten angesehen), wie viele Teile in der jeweiligen Kalenderwoche noch zu produzieren sind.
Könne Sie mir da helfen?
Vielen lieben Dank.
Antwort
Hier ein Lösungsansatz, der das Problem aber (wahrscheinlich) noch nicht in Gänze löst:
Sie benötigen eine Datumstabelle (heißt bei mir Kalender), über welche Sie die beiden Tabellen verbinden. In der Datumstabelle müssen Sie zuvor eine Spalte mit der Kalenderwochennummer erzeugen. Dazu können Sie die Funktion WEEKNUM benutzen.
Erzeugt eine Spalte mit Kalenderwochennummern:
KW Nummer = WEEKNUM(Kalender[Datum],2)Erzeugt eine Spalte mit Kalenderwochennummern nach ISO-Norm 8601:
KW Nummer = WEEKNUM(Kalender[Datum],21)Verbinden Sie die Tabellen IST und Kalender im Datenmodell wie folgt:
Anschließend Verbinden Sie die Tabellen SOLL und Kalender:
Nun können Sie folgende Measures anlegen:
IST Teile = SUM(IST[Produzierte Teile])SOLL Teile = SUM(SOLL[Teile SOLL])Differenz SOLL IST = [IST Teile] - [SOLL Teile]Nun erzeugen Sie eine Matrix. Geben Sie im Zeilenbereich das Feld SOLL[KW] aus. In den Wertebereich ziehen Sie die 3 Measures:
Hinweis: Beachten Sie bitte, dass dies noch keine vollständige Lösung ist, sondern eher als ein Lösungsvorschlag zu interpretieren ist. Beispielsweise können Sie hier noch nicht zwischen Früh- und Spätschicht unterscheiden.
Power BI Frage
Frage vom 03.02.2023
Hallo Herr Trummel,
nach unserem letzten Gespräch konnte ich nun einige Auswertungen starten und sehe auch, dass manches nicht immer so einfach ist. ...aber das wussten Sie sicherlich bereits 😉
So habe ich das Problem, dass ich in einer Auswertung (Power BI Desktop) in einer Matrix in mehreren Spalten Measures verwende, um auf die gewünschten Ergebnisse zu kommen. Das habe ich auch soweit hinbekommen, und es rechnet richtig. Nun rechnet es mir aber in diesen Spalten das Gesamtergebnis nicht richtig aus. Gibt es dafür eine Möglichkeit, dass diese Spalten bei Gesamtsumme nicht das Measures verwenden, sondern da vielleicht eine Art normaler Summen()-Funktion greift?
Antwort
Schauen Sie sich folgende Matrix an. Sie zeigt den Umsatz und den Umsatz im Jahresverlauf (auch „Year-to-Date“, abgekürzt „YTD“).
Im Measure Umsatz verwende ich folgende Formel:
Umsatz = SUM(Bestellungen[Gesamtpreis])Im Measure Umsatz YTD habe ich diese Formel eingegeben:
Umsatz YTD = CALCULATE([Umsatz], DATESYTD(Kalender[Datum]))Aufgrund des Filterkontextes ist die Gesamtsumme (im Bild oben rot eingekreist) nicht korrekt. Mithilfe der Funktion HASONEVALUE können Sie ermitteln, ob im jeweiligen Filterkontext nur ein Kalenderjahr enthalten ist. Die Funktion liefert True oder False. Damit können Sie ermitteln, ob sie in der Gesamtzeile sind. Beachten Sie, dass der Filterkontext für jeden einzelnen Ergebniswert in der Matrix ermittelt wird!
Mit dem Measure Umsatz YTD besser errechne ich in der Gesamtzeile das korrekte Ergebnis.
Umsatz YTD besser =
IF(
HASONEVALUE(Kalender[Jahr]),
CALCULATE(
[Umsatz],
DATESYTD(Kalender[Datum])
),
[Umsatz]
)Hier die Matrix mit dem neuen Measure:
Mehr über den Filterkontext erfahren Sie in meinem Seminar Power BI - Perfekt für Einsteiger!
Im Nachgang zum Seminar
Frage vom 02.02.2023
Hallo Herr Trummel,
gerne komme ich auf Ihr Angebot der Fragestellung zurück. Ich habe mittlerweile häufig in Power BI gearbeitet und damit z.B. die neue Krankenstatistik für unsere Gruppe erstellt.
Es geht mir um die grün dargestellte Y-Linie, die die Anzahl der Mitarbeiter der Bereiche darstellt. Optisch finde ich störend, dass die dazugehörigen (gelb markierten) Wertefelder weiß hinterlegt sind.
Bei den prozentualen Wertefeldern finde ich die weißen Hintergründe gut, bei den gelb markierten hätte ich die weißen Hintergründe gerne weg. Geht das?
Freue mich auf Ihre Antwort und bedanke mich sowohl im Voraus als auch nochmal für den Horizont, den Sie uns in den Seminartagen geöffnet haben😊
Antwort
Nehmen Sie folgendes Diagramm als Beispiel:
Sie sehen den Umsatz für Gewerbe- und Privatkunden als blaue Säulen, sowie die Absatzmenge als grüne Linie. Die Datenbeschriftung ist aktiviert und hat jeweils einen weißen Hintergrund.
Ich möchte nun den weißen Hintergrund bei der Absatzmenge deaktivieren.
Dazu gehe ich ins Format-Menü (Pinsel) und öffne den Abschnitt Datenbeschriftungen.
Im Unterabschnitt Datenreihen stelle ich die Absatzmenge ein. Meine Änderungen gelten also nur für die grüne Linie. Weiter unten stelle ich dann den Hintergrund aus (Schieberegler nach links schieben).
Das Diagramm sieht nun wie folgt aus:
Gegebenenfalls sollten Sie noch die Farben der Säulen ändern, da einige Datenbeschriftungen nun nicht mehr so gut zu erkennen sind.
Fragen aus dem Januar 2023
Power BI Liniendiagramm
Frage vom 07.01.2023
Sehr geehrter Herr Trummel,
ich möchte in einem Liniendiagramm lediglich einen einzelnen Wert (aktueller Monat, in dem Bsp. den Wert für Oct) über die Schriftgröße hervorheben.
In der Farbrolle besteht die Möglichkeit aber nur für die gesamte Linie. Gibt es einen Trick (oder eine spezielle Dax-Funktion, über die oder den man zum Ziel gelangt?
Antwort
Schauen Sie sich dieses Liniendiagramm an:
Ich möchte nun die Schriftfarbe der Datenbeschriftung in Rot umändern, wenn der Wert größer oder gleich 60 ist.
Im Format-Menü (Farbrolle) klicke ich im Abschnitt Datenbeschriftungen und dort im Unterabschnitt Werte auf die fx-Schaltfläche beim Befehl Farbe.
Dies öffnet das Menü der Bedingten Formatierung. Ich kann nun eine Regel eingeben (im Auswahlfeld Formatstil bitte noch von Farbverlauf auf Regeln umstellen), welche alle Zahlen, die größer oder gleich 60 sind, mit einer roten Schriftfarbe versieht.
Das Liniendiagramm sieht nun so aus:
Sie können bei allen Befehlen, bei denen es die fx-Schaltfläche gibt, die Bedingte Formatierung aktivieren.
Für die Schriftgröße gibt es leider keine Bedingte Formatierung. Aber vielleicht hilft Ihnen der Trick über die Schriftfarbe schon weiter.
Fragen aus dem Dezember 2022
Frage zu Power BI: Measure/Calculate in Abhängigkeit von der Belegung eines Auswahlfeldes
Frage vom 28.12.2022
Hallo Herr Trummel,
ich illustriere meine Frage mal an einem Beispiel:
- Tabelle 1 und Tabelle 2 sind vom Aufbau identisch, haben nur verschiedene Quellen (konkret hier: 2 verschiedene Portfolioreports). Beide beinhalten zB die Kenngrösse A in einer Spalte
- Measure 1 liefert mir via CALCULATE die Grösse A aus Tabelle 1 mit entsprechenden Filterkriterien
- Measure 2 liefert mir via CALCULATE die Grösse A aus Tabelle 2 mit den gleichen Filterkriterien
- Für Measure 1 und Measure 2 habe ich jeweils eine identische Visualisierung 1 und 2 erstellt
- soweit so klar
Nun würde ich aber gern noch EINE Visualisierung haben, bei welcher ich zwischen der Anzeige von Measure 1 und Measure 2 einfach umschalten kann über ein Auswahlfeld oä. Ich bräuchte also ein neues „Measure NEU“, welches Measure 1 liefert bei Auswahl 1 und Measure 2 liefert bei Auswahl 2… und eben die Möglichkeit der Auswahl zwischen 1 und 2 über welches Objekt auch immer…?
Wie kann ich das bewerkstelligen? Welches Objekt muss ich für die Auswahl erzeugen (bitte mehr als 2 Optionen, das Beispiel war nur vereinfacht). Und wie kann ich dann via Calculate im „Measure NEU“ auf dieses Objekt zugreifen und dann bedingt auf dessen Belegung rechnen?
Ich hoffe die Frage ist verständlich erklärt!?
Ergänzung vom 29.12.2022
Hallo nochmal Herr Trummel,
zur u.g. Frage noch eine Ergänzung bzw. Verallgemeinerung der gesuchten Lösung: In der neuen gesuchten EINEN Visualisierung sollte idealerweise nicht nur entweder Measure 1 oder Measure 2 oder Measure 3… angezeigt werden können, sondern bei erlaubter Mehrfachauswahl im Auswahl-/Filterobjekt dann eben auch die Summe der entsprechend ausgewählten „Measures“…
Also allgemein:
Gegeben:
- Tabellen 1, 2, …, X für Daten aus Portfolien 1, 2, …, X (Diese Tabellen sind von Aufbau und Struktur identisch)
- Measures 1, 2, …, X welche aus den Tabellen 1, 2, …, X die jeweils gleiche Kenngröße A liefert (unter Verwendung gewisser Bedingungen mittels CALCULATE)
- Visualisierungen 1, 2, …, X für die Measures 1, 2, …, X
Gesucht:
- Eine neue Visualisierung
- In dieser soll der Nutzer die Portfolien 1, 2, …, X auswählen können (Mehrfachauswahl möglich)
- Die Visualiserung zeigt dann die Summe der Measures 1, 2, …, X entsprechend der getroffenen Auswahl
Ich habe schon überlegt, ob ich eine neue Tabelle „Portfolioliste“ kreiere, in welcher die Portfolien 1, 2, …, X aufgelistet sind. Diese könnte ich mit einer ID versehen mittels derer ich die Datenverknüpfung zu den Tabellen 1, 2, …, X herstellen könnte, wenn ich dort eben auch die IDs mitführe. Die Portfolioliste könnte dann noch eine Spalte (Status“ erhalten, in welcher eben der Status aktiv/inaktiv gesetzt ist. Dann könnte ich darauf ja das neue bedingte „Summen-Measure“ verformeln. Die Frage bleibt allerdings, wie gelingt es mir den „Status“ dann dynamisch vom Nutzer setzen zu lassen… ?
Antwort
Wenn die Tabellen alle gleich aufgebaut sind, dann sollten Sie diese zu einer einzigen Tabelle zusammenfassen. Dazu können Sie zum Beispiel die Ordnerabfrage benutzen, die wir auch im Seminar besprochen haben.
Die Ordnerabfrage generiert eine Spalte mit dem Namen Source.Name, welche den ursprünglichen Namen der Datei beinhaltet. So lassen sich die Daten auch im Nachhinein der ursprünglichen Datenquelle zuordnen.
Anschließend brauchen Sie nur noch ein einziges Measure, um die Spalte mit den Kenngrößen zu summieren.
Der Benutzer kann dann über einen Datenschnitt (Mehrfachauswahl möglich) die gewünschten Daten auswählen. Im Datenschnitt könnten Sie zum Beispiel die Spalte Source.Name verwenden.
Den Status können Sie beispielsweise über eine berechnete Spalte erzeugen. Der Anwender kann dann ebenfalls über einen Datenschnitt den Status wählen.
Und noch zu Ihrer 1. Frage, wie Sie über ein Auswahlfeld zwischen verschiedenen Measures wechseln können: Dazu finden Sie in Ihrem Buch auf den Seiten 659 ff. entsprechende Anleitung. Der Autor zeigt dort, wie Sie mit einer verbindungslosen Tabelle und einem Parameter die Datenauswahl flexibler gestalten können. Dieser Lösungsweg ist aber, wenn Sie die oben beschriebene Ordnerabfrage verwenden, nicht mehr notwendig.
Fragen aus dem September 2022
Frage zu Power BI
Frage vom 21.09.2022
Hallo Herr Trummel,
ich habe eine Frage zu Power BI, vielleicht können Sie mir dabei helfen. Ich habe eine kurze Beispieltabelle erstellt, mit ausgedachten Daten.
Ich möchte jetzt eine KPI ausrechnen, also eine Zahl, welche ich z.B. in einem Tachometer darstellen möchte.
Die Frage ist: Wie viele User haben für dasselbe System pro Monat mehr als einen Antrag gestellt?
In diesem Beispiel wäre die Antwort
User 1, User 2, User 5, User 6, User 7 => Also 5.
Antwort
Wenn die Frage lautet: Wie viele User haben für dasselbe System in einem Monat mehr als einen Antrag gestellt, dann kann ich Ihre oben genannte Antwort nicht ganz nachvollziehen.
Lassen Sie mich daher erklären, wie ich die Aufgabenstellung verstanden habe:
Zunächst einmal vermute ich, dass ein Antrag gleichbedeutend mit einer Zeile in der Tabelle ist.
Wenn ich in einer Matrix die Anzahl der Zeilen in der Tabelle bestimme, eine Zeilenbeschriftung nach User und eine Spaltenbeschriftung nach System einfüge und in einem Datenschnitt auf den Monat Januar filtere, dann komme ich auf folgendes Ergebnis:
Die Antwort müsste meiner Ansicht nach lauten:
Im Januar haben folgende User mehr als einen Antrag für das System A1 bearbeitet:
User 1, User 3 und User 5.
Für System B1 waren es:
User 2, User 4 und User 6.
Insgesamt komme ich also auf 6 User, die im Januar für dasselbe System mehr als einen Antrag bearbeitet haben.
Bitte korrigieren Sie mich, wenn ich die Aufgabenstellung falsch verstanden habe. Ich möchte dennoch meinen Gedankengang fortsetzen und zeigen, wie ich rechnerisch auf diese 6 User komme.
Mit diesem Measure errechne ich das genannte Ergebnis:
Anzahl User =
COUNTX(
FILTER(
ADDCOLUMNS(
SUMMARIZE(
Beispieltabelle,
Beispieltabelle[User],
Beispieltabelle[System]
),
"Ergebnis",
CALCULATE(
COUNTROWS(Beispieltabelle)
)
),
[Ergebnis] > 1
),
[Ergebnis]
)
Hier eine kurze Beschreibung dieser Kennzahl:
Mit ADDCOLUMNS und SUMMARIZE erzeuge ich eine Zusammenfassungstabelle, die gruppiert ist nach User und System. Sie enthält eine Ergebnisspalte mit der Anzahl der Zeilen pro Gruppe.
Diese Tabelle sieht folgendermaßen aus:
Mit FILTER reduziere ich die Tabelle auf alle Zeilen, bei denen in der Spalte Ergebnis ein Wert steht, der größer ist als 1. Die Tabelle sieht nun folgendermaßen aus:
Mit COUNTX zähle ich die Zeilen dieser Tabelle. Ich komme also auf 6.
Den Filterkontext für den Monat erzeuge ich mit dem oben gezeigten Datenschnitte. Für Februar kommen keine Ergebnisse heraus.
Rückfrage vom 27.09.2022
Hallo Herr Trummel,
ich habe Ihnen eine Bewertung geschrieben, Ihr Service ist wirklich super. Ich habe noch eine weiterführende Frage zu meinem Beispiel, vielleicht können Sie mir das auch schnell beantworten.
Ich möchte das ganze jetzt in einem Graph darstellen (z.B. ein Säulendiagramm). Allerdings soll auf der x-Achse die Anzahl Anträge und auf der Y-Achse die Anzahl dieser dargestellt sein.
Bedeutet in meinem Beispiel wären es für den Monat Januar nur eine Säule nämlich bei der zwei und diese wäre 6 hoch. Gäbe es jetzt einen User der für dasselbe System drei Anträge gestellt hat (sprich drei Zeilen würden in der Ausgangstabelle dafür auftauchen) hätten wir eine Säule bei der 3 welche eins hoch wäre.
Können Sie mein Anliegen verstehen?
Antwort
Legen Sie über den Befehl Neue Tabelle eine berechnete Tabelle an.
Geben Sie folgenden Code ein:
Ergebnistabelle =
FILTER(
ADDCOLUMNS(
SUMMARIZE(
Beispieltabelle,
Beispieltabelle[Antragsdatum].[Jahr],
Beispieltabelle[Antragsdatum].[Monat],
Beispieltabelle[User],
Beispieltabelle[System]
),
"Ergebnis",
CALCULATE(
COUNTROWS(Beispieltabelle)
)
),
[Ergebnis] >= 2
)Power BI legt nun diese Tabelle an:
Verwenden Sie die Spalte Ergebnis auf der X-Achse und zählen Sie die Einträge in der Spalte System.
In einem Datenschnitt können Sie dann auf ein bestimmtes Jahr und einen bestimmten Monat filtern.
Fragen aus dem Juli 2022
Dim Date mit mehreren Datumspalten verknüpfen
Frage 12.07.2022
Guten Tag Herr Trummel
Mein Problem ist folgendes:
Ich habe eine Datumstabelle und mehrere Umsatztabellen. In diesen Umsatztabellen hab ich Erfassungsdatum des Lieferscheins / Dokumentdatum des Lieferscheins / Rüstdatum des Lieferscheins.
Die Datumstabelle wirkt sich auf alle Umsatztabellen aus. Wenn ich in diesem Beispiel einen Filter in einer Umsatztabelle setzte, wirkt sich dieser nicht auf die anderen Umsatztabellen aus.
Muss ich die Beziehungen anders einstellen?
Antwort
Aktuell sieht Ihr Datenmodell so aus:
Die Tabellen scheinen sehr ähnlich zu sein. Daher drängt sich mir die Frage auf, weshalb Sie diese Tabellen nicht zu einer einzigen Tabellen zusammenfügen können (z.B. mit Power Query >> Befehl Anfügen).
Hinweis: ggf. müssen Sie noch in Power Query die Spaltennamen der Tabellen angleichen.
Ihr Datenmodell hätte dann nur noch eine Umsatztabelle, wodurch auch die Auswertung sehr viel einfacher wäre:
Rückfrage vom 14.07.2022
Guten Tag Herr Trummel
Herzlichen Dank schonmal für die Antwort.
Leider löst die nicht ganz mein Problem. Eine Umsatztabelle wäre mir sehr recht, jedoch hab ich mehrere verschiedene Datumsspalten.
Ich kann nun das Datum(Spalte MartDimDate) nur mit einer anderen Spalte verknüpfen z.B. Bestelldatum.
Wenn ich jetzt aber im Bericht das Datum nach Juli filtere, zeigt es mir nur, welche Dokumente im Juli bestellt sind.
Jedoch möchte ich auch sehen können, welche Kunden im Juli aktiviert wurden (Spalte Datum Kunde aktiviert)
Kann man also das Datum MartDimDate mit mehreren Datumspalten verknüpfen?
Falls nicht, muss ich die Umsatztabelle trennen, dann hab ich jedoch das Problem wie unten beschrieben, dass sich die Filter nicht auf jede Tabelle auswirken.
Antwort
Sie können mehrere Beziehungen zwischen zwei Tabellen erzeugen. Schauen Sie sich dieses Beispiel an:
Die Tabellen sind über die Felder Datum und Verkaufsdatum verbunden. Dies ist die aktive Beziehung. Die Linie ist durchgezogen.
Außerdem gibt es eine Beziehung zwischen Datum und Lieferdatum. Diese ist eine inaktive Beziehung. Die Linie ist gestrichelt.
Ich möchte die Umsatzerlöse (Summe der Spalte Gesamtpreis) berechnen. Dazu erstelle ich folgendes Measure:
Umsatzerlöse = SUM('Verkäufe'[Gesamtpreis])Wenn ich die Umsatzerlöse dann im Bericht z.B. nach Jahren aufschlüssle, verwendet das Measure immer die aktive Beziehung zwischen Datum und Verkaufsdatum.
Um auch die inaktive Beziehung verwenden zu können (also die Umsatzerlöse nach Lieferdatum aufzuschlüsseln), schreibe ich ein weiteres Measure:
Umsatzerlöse (Lieferdatum) =
CALCULATE(
SUM('Verkäufe'[Gesamtpreis]),
USERELATIONSHIP(
'Dim Date'[Datum],
'Verkäufe'[Lieferdatum]
)
)Mit der Funktion USERELATIONSHIP kann ich die inaktive Beziehung nutzen.
Filtern von Summen nach Datum
Frage vom 11.07.2022
Hallo Herr Trummel,
ich habe eine Tabelle mit Schichtzeiten von Mitarbeitern. In der Tabelle ist immer ein Tag pro Mitarbeiter Anwesenheit drin.
Wenn ich die Stunden summieren möchte pro Mitarbeiter benutze ich die SUM-Funktion:
Brutto Stunden = SUM(Zeitplandaten_Sharepoint[Stunden])Filtere ich die Tabelle nach Monat, möchte ich im aktuellen Monat nur die Zeiten bis heute summieren und nicht den kompletten restlichen Monat dazu. Kann ich das in dem Measure mit einbauen?
Vielleicht haben sie da eine Idee.
Vielen Dank.
Antwort
Verwenden Sie diese Formel:
Gesamtstunden bis heute =
CALCULATE(
SUM(Zeitplandaten_Sharepoint[Stunden]),
Zeitplandaten_Sharepoint[Datum] <= TODAY()
)Fragen aus dem April 2022
Frage zu Power BI
Frage vom 19.04.2022
Hallo Herr Trummel,
ich habe eine Frage bezüglich Power BI:
Ich habe eine Kennzahl, welche die IST Werte enthält, eine welche die PLAN Werte enthält und außerdem eine Hirarchie, sagen wir einfach mal mit Produkgruppen (z.B. Lebensmittel (Schokolade, Kaugummi, Kekse), Hygieneprodukte(Zahnpasta, Duschgel).
Ich möchte in einer Matrix die Hirarchie darstellen, welche sich ausklappen lässt und zu jedem Eintrag soll die prozentualle Abweichung eingetragen sein (IST/PLAN).
Eine berechnete Spalte hilft mir hier nicht weiter, da ich die Prozentwerte nicht einfach addieren kann.
Können Sie mir bei der Lösung meines Problems helfen?
Antwort
Gehen Sie von folgenden Daten aus:
| Nr | Produktgruppe |
|---|---|
| 1 | Lebensmittel |
| 2 | Hygieneprodukte |
| Nr | Produkt | Produktgruppen-Nr |
|---|---|---|
| 1 | Schokolade | 1 |
| 2 | Kaugummi | 1 |
| 3 | Kekse | 1 |
| 4 | Zahnpasta | 2 |
| 5 | Duschgel | 2 |
| IST | PLAN | Produkt-Nr |
|---|---|---|
| 80 | 100 | 1 |
| 150 | 100 | 2 |
| 90 | 100 | 3 |
| 100 | 100 | 4 |
| 70 | 100 | 5 |
Ich habe folgende Measures erstellt:
Das Measure IST-Werte:
IST-Werte = SUM(Werte[IST])Das Measure PLAN-Werte:
PLAN-Werte = SUM(Werte[PLAN])Das Measure Abweichung, welches die Differenz aus IST-Werte minus PLAN-Werte ins Verhältnis zu den PLAN-Werten setzt:
Abweichung =
DIVIDE(
[IST-Werte] - [PLAN-Werte],
[PLAN-Werte]
)In einer Matrix kann ich nun die Produktgruppen und Produkte als Zeilenbeschriftung (Produktgruppen sind dann aufklappbar) sowie die 3 Measures ausgeben:
Hier können Sie die Power BI Desktop-Datei kostenfrei herunterladen:
Frage zu unserer Schulung
Frage vom 13.04.2022
Hallo Herr Trummel,
ich habe da eine Frage zu dem Kalender als Tabelle.
Ich habe mir auch wie in der Schulung einen Kalender gebaut. Leider kann ich damit nicht Filtern. Wenn ich Monat oder Jahr hinzu füge, passiert nichts.
Können sie mir da kurz helfen, wo ich den Fehler mache?
Wenn die die Spalten direkt in der Datentabelle anlege, geht es. Die Beziehung zwischen den Tabellen und die Tabelle als Datumstabelle deklariert habe ich gemacht.
Vielleicht haben sie kurz Zeit sich das anzuschauen.
Vielen Dank.
Antwort
Die Spalte hat das falsche Datenformat. Ändern Sie in den Datentyp in Power Query um in Datum.
Wichtig: Die Änderung muss in Power Query erfolgen. Eine Umstellung des Datentyps im Datenmodell von Power BI reicht nicht aus.
Fragen aus dem Februar 2022
DAX-Funktionen
Frage vom 16.02.2022
Hallo Herr Trummel ,
Ich bin es mal wieder… Vielen dank für die Hilfe zuvor, Sie machen es einem echt leicht, ein Verständnis für Power BI zu kreieren . Mir ist heute beim Fortbilden das DAX-Studio über den Weg gelaufen. Dort kam mir die Frage auf , ob es möglich sei bzw. wie es funktionieren könnte , dass ich zuerst eine Abfrage bezüglich einer Datei mache und anschließend von dieser Abfrage bzw. Datei eine andere abrufe. Zum Beispiel möchte ich eine Person mit einer bestimmten ID aufrufen und anhand dessen beispielsweise die letzte Aktion abrufen. Wie würde sowas ausschauen?
Antwort
Eine gute Anleitung zum Einstieg in die Arbeit mit dem DAX-Studio finden Sie hier:
https://daxstudio.org/tutorials/writing-dax-queries/
Wenn Sie von Abfragen auf Dateien sprechen, dann sind wir beim Thema Power Query. Eine Abfrage können Sie im Power Query-Editor kopieren und in der kopierten Abfrage dann entsprechend die Transformationsschritte anpassen (zum Beispiel im Schritt Quelle eine andere Datenquelle angeben).
Eine Anleitung, wie Sie Abfragen im Power Query-Editor ganz einfach kopieren können, finden Sie auf folgender Seite:
Wenn Sie dagegen in einem Measure (also mit DAX-Funktionen) auf bestimmte Teilmengen Ihrer Tabelle zugreifen möchten, dann empfehle ich Ihnen, dass Sie sich einmal mit der Funktion CALCULATE auseinandersetzen.
Fragen aus dem Oktober 2021
Frage zu Aging Report in Power BI
Frage vom 26.10.2021
Hallo Herr Trummel,
Ich habe im Juli und August an einer Power BI Anfängerschulung von Ihnen teilgenommen und Sie sagten am Ende, dass wir Sie weiter kontaktieren dürfen, wenn wir Fragen zu Power BI haben. Und weil ich bei einem speziellen Problem auch mit Google nicht weiterkomme, wollte ich dieses Angebot annehmen.
Zu meinem Problem:
Ich bin gerade dabei einen Ageing Report zu basteln und diesen möglichst vollständig zu automatisieren. Es wird automatisch jeden Monat eine neue Excel-Datei erstellt und in einem Ordner abgelegt. Ich greife mit Power BI auf diesen Ordner zu, füge alle Excel-Dateien aneinander und lasse mir das Erstellungsdatum der Excel-Dateien in einer Spalte ausweisen, um die Einträge nach Monaten aufteilen zu können. Diese Spalte heißt Stichtag.
Auf einer Berichtsseite möchte ich nun automatisiert eine Übersicht der Veränderungen des aktuellsten Monats zum Monat davor anzeigen lassen. Dafür brauche ich Formeln, die mir in der Stichtagsspalte nach dem letzten Datum und dem Vorletzten Datum filtert.
Zuerst zum Vergleich meine nicht automatisierte Formel:
Difference = CALCULATE(SUM('Open items'[Betr. in HW]),'Open items'[Stichtag] = DATE(2021,09,30)) - CALCULATE(SUM('Open items'[Betr. in HW]),'Open items'[Stichtag] = DATE(2021,08,31))
Das Measure Difference berechnet also zuerst die Summen meiner Rechnungsbeträge [Betr. In HW] in der Tabelle Open Items für die manuell eingegebenen Daten 30. September und 31. August und zieht diese voneinander ab.
Das funktioniert auch problemlos. Ich erhalte hier die richtigen Werte.
Nun zu meiner automatisierten Formel:
Da ich in der Calculate-Funktion nicht auf Measures verweisen darf, musste ich hier mit Variablen arbeiten, die auf zwei Measures verweisen.
DifferenceAuto =
VAR LastDatum = [LastDate]
VAR sndtolastdate = [2ndToLastDate]
RETURN
CALCULATE(SUM('Open items'[Betr. in HW]),'Open items'[Stichtag] = LastDatum) - CALCULATE(SUM('Open items'[Betr. in HW]),'Open items'[MonthStich] = sndtolastdate)
Das Measure LastDate:
LastDate = LASTDATE('Open items'[Stichtag])
Das Measure 2ndtolastDate:
2ndToLastDate = MONTH(LASTDATE('Open items'[Stichtag])) -1
DifferenceAuto berechnet also die Differenz genauso, wie die nicht automatisierte Formel. Die beiden Daten erhält das Measure einerseits aus LastDate, das mir einfach das letzte Datum aus der Spalte Stichtag zurückgibt. Und andererseits aus 2ndtolastDate, das vom letzten Datum aus der Spalte Stichtag nur den Monat betrachtet und davon nochmal einen Monat abzieht. Dieser wird dann mit einer neuen Spalte verglichen, die nur den Monat aus der Stichtags-Spalte enthält.
Das funktioniert prinzipiell auch, allerdings erhalte ich seltsame Fehler, die ich mir nicht erklären kann:
Die Summe der beiden Spalten stimmt überein und die einzelnen Positionen bei Difference stimmen auch. Die Summe bei DifferenceAuto dürfte aber überhaupt nicht stimmen und auch sonst sind die Positionen teilweise anders als bei Difference.
Und weil es nur teilweise nicht stimmt, verstehe ich auch überhaupt nicht wo das Problem ist.
Wenn man sich eine falsche Position genau ansieht, sieht man auch Folgendes:
Die beiden Daten Measures LastDate und 2ndtolastDate geben Werte zurück, die sie nach den Formeln überhaupt nicht annehmen dürften.
Ich kann Ihnen leider nicht einfach die Datei zusenden, da die Daten natürlich vertraulich sind.
Vielleicht können Sie mir ja weiterhelfen. Gerne können wir hierzu auch mal einen Skype-Termin vereinbaren, wenn Ihnen das lieber ist als so eine lange Email.
Abschließend möchte ich mich schon mal für jede Hilfe bedanken und auch nochmals Danke sagen für das Training. Bis zu dieser Stelle konnte ich den Report nämlich ohne größere Probleme bauen, was mich und meinen Chef sehr gefreut hat.
Antwort
Um diese Aufgabe zu lösen, brauchen Sie:
- Einen Abfragekontext, der den Bericht auf einen bestimmten Zeitraum filtert
- Eine Datumstabelle, die den relevanten Zeitraum lückenlos abbildet
- Zeitintelligenzfunktionen, um die Beträge vergangener Perioden zu ermitteln
Gehen Sie einmal von folgenden Beispieldaten aus:
Die Excel-Datei können Sie hier herunterladen:
Einen Ausschnitt der Datumstabelle sehen Sie hier:
Mehr zu Datumstabellen lesen Sie in diesem Artikel von Microsoft:
https://docs.microsoft.com/de-de/power-bi/guidance/model-date-tables
Verbinden Sie die Tabelle Open items mit der Kalendertabelle, indem Sie den Stichtag auf die Datumsspalte der Kalendertabelle ziehen.
Jetzt legen Sie danach folgende Measures an:
Betrag = SUM('Open items'[Betr. in HW])
Betrag Vormonat = CALCULATE([Betrag],PREVIOUSMONTH(Kalender[Datum]))
Differenz Betrag = [Betrag] - [Betrag Vormonat]
Damit die Berechnungen die korrekten Ergebnisse liefern, müssen Sie den Bericht auf einen bestimmten Monat filtern. Dazu eignet sich zum Beispiel ein Datenschnitt, in welchem Sie das Feld Monat aus der Kalendertabelle benutzen.
Nun noch eine kurze Erklärung der Measures:
Das Measure Betrag summiert die Spalte Betr. in HW.
Das Measure Betrag Vormonat liefert alle Beträge des Vormonats (immer bezogen auf den für den Bericht gefilterten Zeitraum).
Das Measure Differenz Betrag subtrahiert den Betrag des Vormonats vom Betrag des gewählten Monats.
Kennen Sie schon die Kompetenzgruppen? Hier erarbeiten wir uns im kleinen Kreis Lösungen für aktuelle Herausforderungen mit Power BI!
Testen Sie selbst den fertigen Bericht:
Sequenzielle Darstellung von Projektphasen (sozusagen als liegende gestapelte Balken) in xViz
Frage vom 05.10.2021
Guten Tag Herr Trummel,
eben bin ich auf Ihre sehr interessante Seite aufmerksam geworden. Aktuell bin ich auf der Suche nach einer geeigneten Möglichkeit mehrere Projektphasen in einem Gantt Visual darzustellen ähnlich wie hier dargestellt:
Lingaro kann das, gefällt mir aber nicht besonders. xViz wäre bevorzugt, hierzu habe ich bislang aber unterschiedliche Infos gefunden. So heißt es in der Beschreibung zum obigen Bild ( https://community.powerbi.com/t5/Custom-Visuals-Ideas/Gantt-Chart-Collapsed-with-Milestones/idi-p/342907/page/3#comments) auf Seite 3:
„Although xViz looks nice and does have wonderful functionality, it does not present the same capability as what the original post is requesting."
Antwort
Mit dem GANTT-Visual von xViz habe ich bislang noch nicht gearbeitet. Vielleicht reicht Ihnen ja aber auch das Gantt-Visual von Microsoft, welches ich mir im Folgenden einmal für Sie angeschaut habe.
Sie können Aufgaben und Meilensteine sowie einen Aufgabenstatus (z. B. „offen“ oder „erledigt“) hinterlegen. Zu jeder Aufgabe können Sie eine Ressource (erscheint als Text neben dem Balken) hinzufügen, außerdem zu wie viel Prozent Sie die Aufgabe schon erledigt haben (nicht erledigter Teil wird transparenter dargestellt).
Die Einteilung der Aufgaben Meilensteine erfolgt über die Achse (links).
Hier ein Bericht mit dem Gantt-Visual. Die Tabelle mit den dazugehörigen Daten sehen Sie direkt darunter:
Mehr Infos zum GANTT-Visual von xViz inkl. einem Video und einer Gegenüberstellung, was dieses mehr kann als das Microsoft Gantt-Visual, finden Sie hier auf der Herstellerseite.
Umsatzfenster
Frage vom 05.10.2021
Guten Tag Herr Trummel,
ich habe hier ganz viele Kunden mit Umsätzen.
Ich würde gerne die Kunden in ein Umsatz Fenster einordnen, z.b
Umsätze über 500.000/499.99 - 400.000/399.99 - 300.000 usw
Wie kann ich das machen?
Die Kunden über Filter heraus zu suchen finde ich mühselig, da ich ja immer wieder die Filtergröße neu eingeben müsste.
Können Sie mir da weiterhelfen?
Antwort
Gehen wir einmal beispielhaft von folgenden Kunden und Umsätzen aus:
| Kunde | Umsatz |
|---|---|
| A | 92.100 |
| B | 116.400 |
| C | 69.700 |
| D | 402.200 |
| E | 581.200 |
| F | 129.000 |
| G | 194.300 |
| H | 30.800 |
| I | 416.800 |
| J | 473.100 |
In dieser Tabelle habe ich eine berechnete Spalte hinzugefügt, welche für jeden Kunden ein Umsatzfenster generiert:
Umsatzfenster = SWITCH( TRUE(), Kundenliste[Umsatz]>=500000,">=500.000", Kundenliste[Umsatz]>=400000,"< 500.000 und >= 400.000", Kundenliste[Umsatz]>=300000,"< 400.000 und >= 300.000", Kundenliste[Umsatz]>=200000,"< 300.000 und >= 200.000", Kundenliste[Umsatz]>=100000,"< 200.000 und >= 100.000", "< 100.000 und >= 0" )
Die Funktion SWITCH ist mit einem mehrfach verschachtelten IF vergleichbar, aber wesentlich einfacher zu schreiben.
Das Feld Umsatzfenster kann ich dann in einem Datenschnitt verwenden, um die Kundenliste ganz einfach auf bestimmte Kunden zu filtern.
Tipp: Sortieren Sie die Daten im Datenschnitt in alphabetisch absteigender Reihenfolge, damit das höchste Umsatzfenster ganz oben steht.
Hier ist der fertige Bericht:
Fragen aus dem August 2021
Frage vom 06.08.2021
Hallo Herr Trummel,
ich bin Werkstudentin und bin übers Internet auf Ihre Seite gestoßen. Ich habe folgende Frage:
Ich bekomme jeden Monat eine Excel-Datei zugeschickt, in der Daten zu einem bestimmten Monat enthalten sind. Die Excel-Dateien enthalten die gleichen Daten, aber die Zahlen für den jeweiligen Monat ändern sich. In Power BI habe ich bereits eine Tabelle mit den Daten vom Juni. Jetzt würde ich gerne die Tabelle mit den Daten vom Juli erweitern. Sprich ich würde gerne wissen wie ich die Tabelle in Power BI mit den Zahlen einer anderen Excel-Datei (die aber dieselben Spalten hat) erweitern kann. Ich hoffe meine Frage war verständlich.
Ich danke Ihnen im Voraus.
Antwort
Diese Aufgabe können Sie in Power BI mit einer Abfrage auf einen Ordner lösen.
In meinem Blog-Beitrag erkläre ich Schritt für Schritt, wie Sie eine solche Abfrage ganz einfach erstellen:
Fragen aus dem Juli 2021
Visualisierungen mit Messachsen
Frage vom 29.07.2021
Hallo Herr Trummel,
vielen dank für die wirklich sehr gute Antwort zu meinem gestrigen Thema.
Ich habe folgendes Ergebnis:
Mein Fragebogen hat verschiedene Kategorien aus denen ich die Antwort Punktzahl jeweils zusammen gerechnet habe.
Diese Kategorien habe ich ebenfalls zu einer Gesamtpunktzahl zusammen gerechnet.
Nun möchte ich gern diese Ergebnisse Grafisch darstellen, am liebsten auf die Weise wie es in docs.microsoft.com zu finden ist.
Der Link ist: Leitfaden - Bewertungen | Microsoft Docs
Hier ein Screenshot:
Dies sind 2 Visualisierungen und ich benötige beide oder ähnliche. Leider finde ich keine passenden.
Gibt es derartige Visualisierungen oder müsste ich mich mit dem Thema Visualisierungen selbst erstellen befassen?
Antwort
Exakt solche Visualisierungen kann ich leider nicht finden. Ich habe jedoch in der App Source Visualisierungen entdeckt, die der Aufgabenstellung nahekommen.
LinearGauge - xViz
Die erste Visualisierung ist das LinearGauge der Firma xViz. Es erzeugt eine einzige Messachse, die Sie in drei unterschiedliche Kategorien einteilen können (Bedingte Formatierung). Außerdem kann der aktuelle Wert mithilfe einer Linie visualisiert werden.
Mehr Infos zu dieser Visualisierung und ein fertiges Beispiel zum Herunterladen finden Sie auf der Seite des Herstellers: https://xviz.com/visuals/linear-gauge/?utm_source=xviz_landing&utm_medium=powerbi_productuser
Multiple Sparklines
Um mehrere Messachsen (zum Beispiel für jede Person) zu erzeugen, können Sie die Visualisierung Multiple Sparklines des Entwicklers Zubair nutzen. Verwenden Sie dort im Bereich Bullet / Bar Chart eine Spalte in der Form Zielwert | AktuellerWert (s. Blatt Die Daten).
Hier kann jedoch nur eine Frage pro Person ausgewertet werden.
Mehr Infos zu dieser Visualisierung und ein Beispiel zum Herunterladen finden Sie auf der Seite von Zubair unter https://www.excelnaccess.com/sparklines/.
Und hier meine Demodatei zum Ausprobieren.
PS: Die Visualisierungen sind kostenpflichtig, deshalb können Sie sie in meinem Demobericht nur eingeschränkt nutzen.
Zahlen aus Spalten verrechnen
Frage vom 28.07.2021
Hallo Herr Trummel,
gern möchte ich Ihren kostenlosen Service nutzen und Ihnen eine Frage stellen.
Wir haben Umfragewerte aus MSForms.
Die Fragen bilden je eine Spalte für sich und haben je einen Wert zwischen 0 – 10.
Nun möchte ich gern bestimmte Spalten miteinander Summieren um einen Gesamtwert zu erhalten.
An dieser Stelle komme ich mit SUM nach meinem Wissen nach nicht weiter, ebenso mit SUMX.
Ich hoffe sehr, dass Sie eine Antwort parat haben und mir aus meiner Misere heraus helfen können.
Antwort
Diese Aufgabe lässt sich mit dem Entpivotieren von Power Query lösen.
Gehen wir einmal von folgendem Beispiel aus:
| Person | Frage 1 | Frage 2 | Frage 3 |
|---|---|---|---|
| A | 10 | 3 | 10 |
| B | 9 | 2 | 5 |
| C | 9 | 10 | 4 |
| D | 0 | 10 | 1 |
| E | 5 | 3 | 4 |
Laden Sie die Daten in den Power Query-Editor. Markieren Sie die drei Spalten Frage 1, Frage 2 und Frage 3.
Wählen Sie dann auf dem Reiter Transformieren den Befehl Spalten entpivotieren.
Power Query löst die drei Spalten nun in zwei Spalten mit Attribut-Wert-Paaren auf. Diese können Sie natürlich noch umbenennen und anschließend ins Datenmodell laden.
Jetzt können Sie die Wert-Spalte einfach summieren und je Person bzw. Frage auswerten.
Zeiten in Schichten einteilen
Frage vom 26.07.2021
Hallo Jan,
ich bin übers Internet zu deiner Seite gekommen und ich hoffe du kannst mir helfen.
Meine Frage:
Ich habe eine große Excel Datei, die ich im Power BI nach gewissen Prozessen und Bereich auswerten will.
Dabei muss ich aber auch noch nach der Zeit/Schichten auswerten.
Kann ich irgendwo mir Zeitfilter setzen wie zB. Von 06:00-14:29; 14:30-22:59; 23:00-05:59 ?
Vielen lieben Dank für deine Hilfe
Antwort
Gehen wir einmal von folgendem Beispiel aus:
| Arbeiter | Beginn |
|---|---|
| A | 09:00 |
| B | 09:00 |
| C | 16:30 |
| D | 16:30 |
| E | 23:00 |
Wir wollen herausfinden, welche Arbeiter in welcher Schicht waren.
Dazu habe ich mir in Excel eine minutengenaue Tabelle mit Uhrzeiten von 00:00 – 23:59 Uhr angelegt. Jeder Uhrzeit habe ich die entsprechende Schicht zugeordnet:
Anschließend habe ich die Tabelle ins Datenmodell geladen und dort beide Tabellen miteinander verbunden:
Jetzt kann ich das Feld Schicht aus der Zeitdimension als Filter benutzen. Probieren Sie es aus:
PS: Die Zeitdimension habe ich nur der Vollständigkeit halber mit in den Bericht aufgenommen. Sie mussten Produktivbericht natürlich nicht gezeigt werden.
Frage zu PowerBI Desktop
Frage vom 26.07.2021
Guten Tag Herr Trummel,
Meine Frage:
Ich habe 4 Exceltabellen mit Umsatzdaten aus 4 verschiedenen Jahren und möchte diese in einer Grafik visualisieren. Die Grafik soll primär die Umsätze der verschiedenen Jahre mit den Monaten als "x-Achse" zeigen.
Dazu habe ich versucht, Beziehungen zwischen den Exceltabellen über die Spalte "Bestelldatum" herzustellen, die in jeder Tabelle vorhanden ist (z.B. 01.01.2019). Alle "Bestelldatum"-Spalten sind vom Typ "Datum".
Ich gehe davon aus, dass es notwendig ist, ein extra Excelblatt zu verwenden, um die Excel-Blätter über das Datum zu verknüpfen? Wenn das richtig ist, wie muss dieses aussehen?
Im nächsten Schritt möchte ich auch die Summe der Umsätze, die bis zu jedem Monat im Jahr aufgelaufen sind, in der gleichen Grafik darstellen (falls diese Information für die Beziehungsaufgabe notwendig ist).
Vielen Dank im Voraus.
Antwort
Ich gehe davon aus, dass die vier Excel-Tabellen gleich aufgebaut sind.
In diesem Fall müssen Sie die vier Tabellen zu einer großen Tabelle zusammenfassen. Sie können die vier Tabellen mit Power Query einlesen und dann über den Befehl Abfragen anfügen zu einer großen Abfrage vereinigen.
Hier lesen Sie mehr zum Thema Abfragen anfügen.
Schauen Sie sich bei der Gelegenheit auch einmal die Abfrage auf einen Ordner an. Damit können Sie solche Aufgaben automatisieren.
Hier lesen Sie mehr zu Abfragen auf Ordner.
Nehmen Sie dann das Feld Jahr aus der integrierten Datumshierarchie des Feldes Bestelldatum, um den Umsatz für alle Jahre in einer Visualisierung anzuzeigen.
Für umfassende Datumsberechnungen empfehle ich jedoch die Arbeit mit einer Datumstabelle.
Fragen aus dem Mai 2021
Prozentuale Abweichungsberechnung
Frage vom 27.5.2021
Ich habe bei Power BI eine Übersichtsseite für die KPIs ganz nach vorne gesetzt. Hier ist es so, dass jeweils von dem aktuellen Monat z.B. die Nachbearbeitungszeit im Team „IT Ops“ dargestellt wird. Ich würde jetzt gerne auch noch die prozentuale Abweichung zu dem jeweiligen Vormonat in einer %-Zahl darstellen. Unter dem eigentlichen KPI Wert zum Beispiel. Allerdings komme ich nicht darauf, wie ich das darstellen kann. Kennen Sie hier eine Lösung?
Antwort
Um diese Aufgabe zu lösen, benötigen Sie eine Kalendertabelle, sowie die Funktionen CALCULATE und PREVIOUSMONTH. Wenn die Nachbearbeitungszeiten in Summe über 24 Stunden hinausgehen, müssen Sie für die korrekte Darstellung ein eigenes Measure schreiben.
Wie dies gelingen kann, habe ich beispielhaft in folgendem Beitrag skizziert:
Fragen aus dem April 2021
Spezielle Sortierung einstellen
Frage vom 08.04.2021
Hallo Herr Trummel,
wenn ich eine Auswertung in Power BI fahre, wie kann ich das Ergebnis nach meinem Wunsch anzeigen lassen.
Es geht z.B. um folgende Tabelle:
| Effort | März | April | Gesamt |
|---|---|---|---|
| Low | 10 | 13 | 23 |
| High | 25 | 15 | 40 |
| Medium | 25 | 12 | 37 |
Ich würde gerne zuerst Low, dann Medium und dann High anzeigen lassen.
Gibt es hierzu eine Sortiermöglichkeit? Alphabetisch geht es ja nicht und leider auch nicht anhand der Anzahl, da sich diese jeden Monat ändern kann.
Antwort
Sie benötigen eine Zahlenspalte, um die Sortierung von Effort an diese zu binden. Da ich nicht weiß, woher Ihre Daten kommen und wie sie im Detail aussehen, gehe ich wie folgt vor:
Hilfstabelle für die Sortierung erzeugen
Erzeugen Sie in Power BI Desktop eine neue Tabelle. Klicken Sie dazu auf der Registerkarte Start auf die Schaltfläche Daten eingeben. Tragen Sie folgende Werte in die Tabelle ein und klicken Sie anschließend auf Laden.
Beziehung zwischen den Tabellen erzeugen
Wechseln Sie nun in den Bereich Modell und erzeugen Sie eine Beziehung zwischen Ihrer Tabelle und der neu geschaffenen Tabelle Effort Sortierung. Ziehen Sie dazu das Feld Effort aus Ihrer Tabelle auf das Feld Sort-Bezeichnung in der Hilfstabelle.
Sortierspalte mit DAX-Formel einbinden
Nun wechseln Sie in den Bereich Daten. Wählen Sie Ihre Tabelle aus und erzeugen Sie eine neue Spalte. Geben Sie folgende Formel ein:
Nr = RELATED('Effort Sortierung'[Sort-Nr])
Die Anweisung ordnet jeder Zeile die richtige Nummer zu.
Effort nach Spalte Sort-Nr sortieren
Wählen Sie nun die Spalte Effort aus, klicken Sie auf die Registerkarte Spaltentools und dort auf die Schaltfläche Nach Spalte sortieren. Wählen Sie aus der Liste das Feld Nr aus.
Nun werden die Daten aus Effort so sortiert, wie die Zahlen in der Spalte Nr.
Tipp: Die Hilfstabelle können Sie jetzt über das Kontextmenü ausblenden, damit sie in der Felder-Liste nicht mehr angezeigt wird.
Dezimalzahlen in Uhrzeiten umwandeln (auch über 24 Stunden)
Frage vom 06.04.2021
Guten Morgen Herr Trummel,
ich hoffe Sie hatten schöne Osterfeiertage.
Vielen Dank für ihre Antwort. Es funktioniert auch soweit, allerdings habe ich einige Zeiten, die über 24 Stunden hinausgehen. Dort zeigt mir Power Query beim transformieren „Error“ an.
Gibt es hierfür auch noch eine Möglichkeit?
Diese Frage bezieht sich auf die Antwort der Frage vom 31.03.2021 (Dezimalzahlen in Uhrzeiten umwandeln).
Antwort
Laden Sie die Daten - ohne vorherige Bearbeitung durch Power Query - ins Datenmodell.
Erzeugen Sie eine neue Spalte und geben Sie folgende Formel ein:
Uhrzeit =
IF(Tabelle1[Zeiten]>=1440,
QUOTIENT(Tabelle1[Zeiten], 60)
&
FORMAT(
MOD(Tabelle1[Zeiten], 60) / 24,
":nn:ss"
),
FORMAT(
Tabelle1[Zeiten]/1440,
"hh:nn:ss"
)
)
Die Idee ist, die Dezimalzahlen geschickt umzurechnen und dann in Uhrzeiten umzuwandeln. Beachten Sie jedoch bitte, dass Sie im Ergebnis einen Text erhalten werden. Nun zur Erklärung der Formel:
Mithilfe der IF-Funktion prüfen Sie, ob die Dezimalzahl größer oder gleich 1440 ist.
Ist dies der Fall, folgen zwei Berechnungen:
Zunächst einmal berechnen Sie die Stunden. Dazu führen sie mit der Funktion QUOTIENT eine Division durch 60 ohne Rest durch. Das Ergebnis ist die Stundenangabe. Es folgt der Verkettungsoperator &.
Jetzt berechnen Sie die Minuten und Sekunden. Dazu verwenden Sie den Rest der Division durch 60. Nutzen Sie hierfür die Funktion MOD. Diesen Rest müssten Sie theoretisch wieder mit 60 multiplizieren, um eine vollwertige Stundenangabe zu erhalten, und das Ergebnis anschließend durch 1440 teilen. Die Multiplikation im Zähler kürzt sich jedoch weg und im Nenner bleibt 24 stehen. Das Ergebnis dieser Berechnung wandeln Sie dann in eine Zeichenkette um, die Minuten und Sekunden darstellt. Hierzu nutzen Sie die Funktion FORMAT. Beachten Sie, dass die Funktion als Angabe für Minuten tatsächlich das "n" erwartet!
Wenn die IF-Funktion ermittelt, dass die Dezimalzahl kleiner als 1440 ist, ist die Berechnung dagegen wesentlich einfacher. Teilen Sie die Dezimalzahl durch 1440 und wandeln Sie das Ergebnis mithilfe der Funktion FORMAT in eine Uhrzeit um.
Fragen aus dem März 2021
Dezimalzahlen in Uhrzeiten umwandeln
Frage vom 31.03.2021
Hallo Herr Trummel,
ich bin gerade dabei meine KPI-Auswertungen weiter zu bauen. Allerdings sitze ich jetzt schon eine ganze Weile daran, herauszufinden wie ich Uhrzeiten, die ich als Dezimalwerte (z.B. 5,8 Minuten) vorliegen habe, in ein Uhrzeitformat umwandeln kann. In Excel ist das mit dem Format „Uhrzeit“ ja recht einfach, aber für Power Bi finde ich einfach keine passende Formel oder Funktion.
Können Sie mir hier weiterhelfen?
Antwort
Gehen wir beispielhaft von folgenden Dezimalzahlen aus, die allesamt Minuten darstellen sollen:
| Zeiten |
|---|
| 5,8 |
| 0,6 |
| 7,2 |
| 0,5 |
| 0,1 |
Vorüberlegungen
Bei der Beantwortung dieser Frage hilft uns ein bisschen Mathematik:
Halten wir uns vor Augen, dass sowohl in Excel als auch in Power BI die Zahl 1 für einen Tag steht. Darüber hinaus wissen wir, dass ein Tag aus 24 Stunden (also aus 24 Einheiten) besteht. Ein Tag entspricht also rechnerisch dem Bruch 24 Vierundzwanzigstel (24/24). Eine Stunde können wir daher als ein Vierundzwanzigstel (1/24) darstellen.
Die nächstkleinere Einheit ist die Minute. Eine Stunde umfasst 60 Minuten. Somit können wir den Nenner des oben genannten Bruchs erweitern, indem wir 24 × 60 = 1440 rechnen. Eine Minute können wir also darstellen als 1/1440.
Dezimalzahlen in Power Query umrechnen
Laden Sie die Daten der Beispieltabelle jetzt in Power Query. Nun teilen Sie alle Werte der Spalte durch 1440. Wählen Sie dazu auf der Registerkarte Transformieren in der Befehlsgruppe Zahlenspalte die Schaltfläche Standard. Klicken Sie dann auf den Befehl Dividieren.
Geben Sie die Zahl 1440 in das Textfeld ein und bestätigen Sie mit OK.
Ändern Sie nun noch den Datentyp der Spalte in Zeit. Damit sind Sie fertig.
Summe für aktuelles Jahr
Frage vom 23.03.2021
Hallo Herr Trummel,
der Grund meines Anrufes ist ein häufiges Problem, bei dem ich gerne Daten des aktuellen Jahres summieren / zählen möchte, ohne einen Datenschnitt zu benutzen.
Das Measure soll aufgrund der Systemzeit erkennen, wann der erste Tag des Jahres ist, und soll die Periode bis „Today()“ berechnen.
Gibt es hierzu eine Standardlösung?
Antwort
Gehen wir von folgender Tabelle aus:
| Datum | Betrag |
|---|---|
| 01.01.2020 | 10,00 € |
| 20.05.2020 | 10,00 € |
| 24.12.2020 | 10,00 € |
| 01.01.2021 | 20,00 € |
| 01.02.2021 | 20,00 € |
| 01.03.2021 | 10,00 € |
| 20.05.2021 | 30,00 € |
Gemäß Aufgabenstellung darf das Measure nur die Beträge vom 01.01.21, 01.02.21 und 01.03.21 summieren (Stand heute: 23.03.2021).
Laden Sie die Tabelle ins Datenmodell von Power BI und verbinden Sie sie mit der Kalendertabelle.
Erzeugen Sie nun das folgende Measure:
Betragssumme dieses Jahr =
CALCULATE(
SUM(Beispieldaten[Betrag]),
DATESBETWEEN(
Kalender[Datum],
DATE(YEAR(TODAY()),1,1),
TODAY()
)
)
Die Funktion DATESBETWEENN erzeugt eine Liste mit Datumsangaben, und zwar von einem Start- bis zu einem Enddatum.
Das Startdatum ist hier immer der 1. Januar des aktuellen Jahres. Diesen errechnen Sie mit folgendem Ausdruck:
DATE(YEAR(TODAY()),1,1),
Die Funktion YEAR gibt aus dem aktuellen Datum (Funktion TODAY) die Jahreszahl zurück. Dieses verwenden Sie in der Funktion DATE, um ein gültiges Datum zu erzeugen.
Das Enddatum ist immer das heutige Datum, also TODAY.
Die Funktion CALCULATE sorgt nun dafür, dass SUM nur Beträge addiert, deren Datumsangaben in der von DATESBETWEEN erzeugten Datums-Liste enthalten sind.
Das Measure errechnet (Stand heute: 23.03.2021) einen Wert von 50,- €.
Wenn Sie nicht summieren, sondern zählen möchten, nutzen Sie statt SUM einfach die Funktion COUNT.
Fragen aus dem Januar 2021
Formel aus dem Seminar
Frage vom 21.01.2021
Diese Frage bezieht sich auf eine Übungsaufgabe aus meinem Power BI-Seminar. Die Aufgabe lautete:
Schreiben Sie ein Measure, das den Umsatz mit den Büchern zeigt, wobei nur solche Bücher beachtet werden sollen, von denen mindestens 30 Stück verkauft wurden.
Hier ist die Lösung:
Umsatz Bücher ab 30 Stück =
CALCULATE(
[Umsatz],
FILTER('Bücher',[Anzahl verkaufte Bücher] >= 30)
)
In einer Matrix-Visualisierung sollten Sie dieses Measure dann für jede Literaturepoche ausgeben, sodass sich folgendes Bild hätte ergeben sollen:
Ein Teilnehmer hatte jedoch eine andere Formel geschrieben. Diese sehen Sie hier:
Teilnehmerlösung =
CALCULATE(
[Umsatz],
FILTER(Bestellungen,COUNT(Bestellungen[Buch-Nr])>=30)
)
Die Matrix zeigte dann auch ein anderes Bild:
Was genau tut das Measure Teilnehmerlösung und warum liefert es ein anderes Ergebnis? Lesen Sie im Folgenden die Antwort!
Antwort
Zuerst beschreibe ich Ihnen, warum das Measure Umsatz Bücher ab 30 Stück die richtige Antwort liefert. Betrachten Sie als Beispiel die Zeile Biedermeier, für die das Measure einen Umsatz von 418 € liefert.
Die Funktion CALCULATE berechnet das Measure Umsatz unter Berücksichtigung einer Filterbedingung neu. Diese Filterbedingung wird hier mit der Funktion FILTER realisiert. FILTER liefert eine virtuelle Tabelle, die eben jene Zeilen enthält, für die der Umsatz neu kalkuliert werden soll.
Betrachten Sie nun die Funktion FILTER genauer: Die Funktion bezieht sich auf eine der Grundtabellen des Datenmodells. Dies ist hier die Tabelle Bücher. Der Ausdruck [Anzahl verkaufte Bücher] >= 30 ist wiederum eine Filterbedingung, die in der Tabelle bestimmte Zeilen ermittelt. Dies sind die Bücher, die mindestens 30 Mal verkauft worden sind.
Nun müssen Sie auch noch den Filterkontext berücksichtigen, der von der Matrix-Visualisierung erzeugt wird! Für unsere Beispielzeile mit der Epoche Biedermeier bedeutet dies, dass die Grundtabelle Bücher, die in FILTER benutzt wird, bereits auf alle Bücher aus dem Biedermeier gefiltert ist. Was FILTER sieht ist im Prinzip Folgendes:
Die Anweisung [Anzahl verkaufte Bücher] >= 30 von FILTER selbst reduziert diese Tabelle dann weiter auf die Zeile mit dem Titel Das Hospiz auf dem Großen St. Bernhard, da nur dieses Buch die Filterbedingung erfüllt.
Und somit zeigt die erste Matrix aus der Frage oben für den Biedermeier auch nur einen Umsatz von 418 €.
Nun beschreibe ich Ihnen, warum das Measure Teilnehmerlösung andere Daten liefert:
Die Funktion FILTER greift hier auf die Tabelle Bestellungen. Bedingt durch den Filterkontext der Matrix sieht FILTER nur die Zeilen der Grundtabelle, in denen Bücher aus der jeweiligen Epoche verkauft worden sind. Nehmen wir als Beispiel wieder den Biedermeier, dann sieht FILTER diese Daten:
Die Filterbedingung COUNT(Bestellungen[Buch-Nr])>=30 besagt nun, dass diese Zeilen von FILTER zurückgegeben werden, wenn die Anzahl der Buch-Nummern größer oder gleich 30 ist. Dies ist hier der Fall (s. Bild)! Der Umsatz aller Bestellungen mit Biedermeier-Büchern beläuft sich auf 874 €. Und das ist auch der Wert, der in der zweiten Matrix aus der Frage in der Zeile Biedermeier steht!
Beachten Sie, dass es in der Teilnehmerlösung keinen Bezug auf ein bestimmtes Buch gibt! Es geht immer um die gesamten Bestellungen der jeweiligen Literaturepoche!
Nun bleibt abschließend nur noch zu klären, warum das Gesamtergebnis der zweiten Matrix aus der Frage 5.230 € zeigt, wobei Sie bei der Addition der einzelnen Werte 1.028 + 360 + 874+ 965 + 913 + 492 doch auf 4.632 € kommen! Macht Power BI hier einen Fehler? Mitnichten!
Für Measures in Power BI gilt, dass Sie in jeder Zeile einer Visualisierung exklusiv und unabhängig von anderen Zeilen berechnet werden. Dies ist ein entscheidender Unterschied zu Berechnung von Kennzahlen in klassischen Pivot-Tabellen in Excel! Für die Zeile Gesamt gilt nun, dass FILTER auf alle Zeilen der Tabelle Bestellungen blickt. Eigentlich auch logisch, da es in der Gesamt-Zeile ja keine Einschränkung mehr auf eine Literaturepoche gibt. Und da die Anzahl der Buch-Nummern in der ungefilterten Tabelle Bestellungen natürlich größer als 30 ist, wird eben auch der Gesamtumsatz zurückgegeben.
Datenschnitt - Datumsbereich nur bis heute
Frage vom 21.01.2021
Bei dieser Frage geht es um einen Datenschnitt in Power BI, über den ein Datumsbereich gewählt werden kann. Nehmen Sie als Beispiel diesen Datenschnitt:
Die Frage war, ob es möglich ist, das angezeigte Enddatum (im Bild der 31.12.2025) immer dynamisch auf das aktuelle Datum zu setzen.
Antwort
Um diese Frage zu beantworten, habe ich in der Kalendertabelle mit folgender Formel eine neue berechnete Spalte angelegt:
Datumsauswahl =
IF(
Datumstabelle[Datum] <= TODAY(),
Datumstabelle[Datum],
BLANK()
)
Die Formel prüft für jede Zeile der Kalendertabelle, ob das Datum vor dem heutigen Datum liegt bzw. ob es dem heutigen Datum entspricht. Die Funktion TODAY liefert dafür das aktuelle Systemdatum. Mit BLANK fügen Sie ein Leerzeichen ein, wenn das Datum noch in der Zukunft liegt.
Wenn Sie nun die Spalte Datumsauswahl in den Datenschnitt einfügen, reicht der auswählbare Datumsbereich nur bis zum aktuellen Systemdatum.
Arbeitstage (inkl. Feiertage) berechnen
Frage vom 21.01.2021
Wie können in einem Projekt die Arbeitstage berechnet werden? Wie können sowohl Wochenendtage als auch Feiertage aus der Zählung ausgeschlossen werden?
Antwort
Lesen Sie dazu meinen Beitrag in der Rubrik Wissenswertes. Ich habe ihn um die Berechnung von Feiertagen erweitert:
Fragen aus dem Dezember 2020
Power BI und Teams - keine Lizenz
Frage vom 18.12.2020
Können Anwender in Teams freigegebene Power BI-Berichte lesen, wenn sie keine Power BI Pro-Lizenz besitzen?
Antwort
Nein, das ist nicht möglich. Benutzer, die weder die Power BI Pro-Lizenz besitzen, noch Mitglied einer Power BI Premium-Kapazität sind, können in Teams freigegebene Bericht zwar anklicken. Der Inhalt wird jedoch nicht geladen.
Mehr dazu lesen Sie in diesem Microsoft-Artikel:
https://docs.microsoft.com/de-de/power-bi/collaborate-share/service-collaborate-microsoft-teams
Bericht mit dynamischer Berechtigung freigeben
Frage vom 18.12.2020
Wir wollen unseren Power BI-Bericht freigeben, allerdings soll jeder Nutzer nur eine Projekte sehen können. Geht das?
Antwort
Ja, das geht. Ich habe einen Beitrag dazu geschrieben:
Monatsnamen in Datumstabelle einfügen
Frage vom 08.12.2020
Ich benutze eine Kalendertabelle als markierte Datumstabelle, um Berechnungsfehler auszuschließen (empfohlen von Marco Russo und Alberto Ferrari in Ihrem Buch Power BI…).
Dabei ist mir aber aufgefallen das der Drilldown in Diagrammen nicht so gut funktioniert wie in einer automatischen Datumshierarchie.
Außerdem möchte ich gerne 2 Jahre im Diagramm einsehen. Die Daten sollen natürlich nach Datum sortiert sein. Angezeigt werden soll aber nur das Jahr und der Monat als Name nicht als Zahl.
Also:
2019 Januar, Februar, ...
2020 Januar, Februar, ...
Wenn ich das über die Funktion gruppieren löse, ist das Datum etwas unscharf da die Daten monatlich auf 30 Tage verteilt werden (kaufm. Jahr).
Haben Sie einen Tipp das zu lösen?
Antwort
Erzeugen Sie in Ihrer Datumstabelle mit der Funktion FORMAT eine Spalte mit Monatsnamen.
Wechseln Sie in Power BI Desktop in den Bereich Daten und wählen Sie die Datumstabelle aus. Klicken Sie dann auf der Registerkarte Start auf Neue Spalte.
Geben Sie folgende Formel ein:
Monatsname = FORMAT(Datumstabelle[Date],"MMMM")
Des Weiteren gehe ich davon aus, dass Sie in Ihrer Datumstabelle eine Spalte mit Monatsnummern (Zahlen) haben (berechnet z.B. mit der Funktion MONTH).
Damit die Monatsnamen nicht alphabetisch sortiert werden, binden Sie die Sortierung der Monatsnamen an die Monatsnummern.
Wählen Sie Spalte Monatsname an und klicken Sie auf der Registerkarte Spaltentools auf die Schaltfläche Nach Spalte sortieren. Wählen Sie in der Liste die Spalte Monatsnummer.
Nun können Sie die Monatsnamen in Ihren Visualisierungen verwenden.
DAX-Measure für eine m:n-Beziehung
Frage vom 03.12.2020
Ich möchte gern aus 2 Tabellen ein DAX-Measure erstellen, bei dem es um folgendes geht:
Tabelle 1: Fertigungen mit Spalte Lieferdatum
Tabelle 2: Reklamationen mit Spalte Auftragsdatum
Beide Tabellen sind über die Spalte FANummer miteinander verknüpft (m:n-Beziehung).
Nun möchte ich eine Quote ermitteln:
Wie viele Reklamationen sind innerhalb von 15 Monaten nach Lieferdatum für eine FANummer eingegangen.
Hier sind die beiden Tabellen (Demodaten):
Tabelle Fertigungen
| FANummer | Lieferdatum |
| 1 | 01.01.2020 |
| 1 | 01.01.2020 |
| 2 | 02.01.2020 |
| 2 | 02.01.2020 |
| 2 | 02.01.2020 |
| 3 | 30.06.2020 |
| 4 | 07.09.2020 |
| 4 | 07.09.2020 |
Tabelle Reklamationen
| FANummer | Auftragsdatum | Bezeichnung |
| 1 | 31.12.2020 | A |
| 1 | 09.05.2021 | B |
| 1 | 27.03.2020 | C |
| 2 | 07.08.2021 | D |
| 3 | 14.05.2021 | E |
| 3 | 01.01.2021 | F |
| 4 | 06.12.2021 | G |
| 4 | 03.03.2022 | H |
| 4 | 04.09.2023 | I |
| 4 | 06.07.2029 | J |
In diesem Beispiel wären das die Reklamationen A, C, E, F und G.
Ich brauche ein Measure, dass die Anzahl der von der Bedingung betroffenen Reklamationen ermittelt, hier also 5.
Antwort
Folgendes Measure löst die Aufgabe:
Reklas M15 =
SUMX(
SUMMARIZE(
'Fertigungen',
Fertigungen[FANummer],
"Anzahl Reklas M15",
COUNTROWS(
FILTER(
RELATEDTABLE(Reklamationen),
Reklamationen[Auftragsdatum] >= MAX('Fertigungen'[Lieferdatum]) &&
Reklamationen[Auftragsdatum] <= MAXX(Fertigungen,EDATE(Fertigungen[Lieferdatum],15))
)
)
),
[Anzahl Reklas M15]
)Gehen wir die einzelnen Funktionen einmal von außen nach innen durch:
Mit SUMX addiere ich Zahlen in einer Tabellenspalte.
Die Tabelle erzeuge ich hier mit Hilfe der Funktion SUMMARIZE (es handelt sich um eine sogenannte virtuelle Tabelle). Sie gruppiert die Datensätze der Tabelle Fertigungen nach FANummern und berechnet die Ergebnisspalte „Anzahl Reklas M15“, indem sie die Anzahl der verbundenen Reklamationen berechnet, bei denen das Auftragsdatum zwischen dem Lieferdatum und dem Lieferdatum in 15 Monaten liegt.
Diese Berechnung ist nicht ganz simpel, ich versuche es aber mal in einfachen Worten zu erklären:
Der Trick ist, dass ich mir zu jeder Fertigung (also zu jeder Zeile in der von SUMMARIZE gelieferten Tabelle) die mit ihr verbundenen Reklamationen ermitteln lasse. Das erreiche ich mit der Funktion RELATEDTABLE. Die von der Funktion gelieferten Datensätze muss ich jedoch weiter filtern, weshalb ich RELATEDTABLE als zu filternde Tabelle in der Funktion FILTER nutze.
Als Filterbedingung gebe ich nun an, dass das jeweilige Auftragsdatum der Reklamation größer oder gleich dem Lieferdatum und kleiner oder gleich dem Lieferdatum in 15 Monaten sein muss. Achtung: Da wir uns jetzt in der Tabelle Reklamationen bewegen, kann ich nicht mehr direkt auf das Lieferdatum zugreifen. Ich kann es aber mit Hilfe der Funktion MAX ermitteln, die auf Grund des Filterkontextes, den SUMMARIZE erzeugt, das Lieferdatum der in der äußeren Funktion betrachteten Fertigung liefert. Ja, das ist wirklich etwas knifflig...
Das Lieferdatum in 15 Monaten erzeuge ich, indem ich mit der Funktion EDATE das Lieferdatum um 15 Monate in die Zukunft verschiebe. Auch hier kann ich nicht direkt auf das Liederdatum zugreifen, daher nutze ich die Funktion MAXX – MAXX, weil ich hier eine Berechnung eingeben kann (nämlich EDATE).
Die Funktion COUNTROWS zählt die Zeilen der von FILTER zurückgegebenen Tabelle. Damit erhalte ich die Anzahl der Reklamationen, die der jeweiligen Fertigung zugeordnet sind und im 15-Monats-Rahmen des Lieferdatums liegen. Die Zahl steht in der der Ergebnisspalte "Anzahl Reklas M15".
Zum Schluss addiert SUMX alle Zahlen aus der Ergebnisspalte "Anzahl Reklas M15" auf und bildet damit das Gesamtergebnis.
Schauen wir uns die Ergebnisse an:
In einer Tabellen-Visualisierung gebe ich die Spalten FANummer (aus Fertigung), Lieferdatum und das Measure aus:
In einer weiteren Tabellen-Visualisierung gebe ich zur Kontrolle die FANummer (diesmal aus Reklamationen), das Auftragsdatum, die Bezeichnung sowie das Measure aus:
Meiner Ansicht nach sollte das Ergebnis korrekt sein.
Trotzdem gilt: Bitte testen Sie das Measure ausgiebig, bevor Sie den Ergebnissen wirklich vertrauen! Bekanntlich steckt der Teufel im Detail!
Fragen aus dem November 2020
Anzahl Arbeitstage in einem Projekt berechnen
Frage vom 09.11.2020
Wie kann man die Arbeitstage in einem Projekt oder einem Auftrag bestimmen?
Antwort
Dazu habe ich den Beitrag in meiner Rubrik Wissenswertes geschrieben. Sie finden die Antwort hier:
Fragen aus dem Oktober 2020
Externe Gastbenutzer in Power BI
Frage vom 27.10.2020
Kann man Power BI-Berichte auch an externe Gastbenutzer weitergeben?
Antwort
Ja das geht. Das Gastbenutzer benötigt dafür eine Power BI Pro-Lizenz. Sie können ihm diese Lizenz zur Verfügung stellen, falls der Gastbenutzer selbst keine Lizenz besitzt.
Wie Sie Gastbenutzer anlegen und diesem Lizenzen zuweisen, erklärt dieser Artikel: https://docs.microsoft.com/de-de/power-bi/admin/service-admin-azure-ad-b2b
Alle DAX-Funktionen erklärt
Frage vom 27.10.2020
Wo finde ich eine Übersicht über die DAX-Funktionen in Power BI?
Antwort
Eine sehr gute Übersicht über die DAX-Funktionen finden Sie hier:
https://docs.microsoft.com/de-de/dax/dax-function-reference
GANTT-Diagramme in Power BI
Frage vom 27.10.2020
Kann man in Power BI ein GANTT-Diagramm (Balkenplan-Diagramm) erstellen, um Projekte / Aufgaben in zeitlicher Abfolge in Form von Balken auf einer Zeitachse darzustellen?
Antwort
Ja, das geht. Gehen Sie folgendermaßen vor:
- Laden Sie die Visualisierung aus der App Source von Microsoft herunter. Hier ist der Link zu der Seite:
https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380765?tab=Overview&signInModalType=2&ctaType=1 - Importieren Sie die heruntergeladene Datei in Power BI Desktop.
- Lernen Sie die Visualisierung kennen, indem Sie in Excel eine einfache Demo-Tabelle anlegen, die Sie in Power BI Desktop importieren:
- Fügen Sie in Power BI Desktop die neue Gantt-Visualisierungen in einen Bericht und ziehen Sie die Felder Aufgabe, Startdatum und Enddatum in die gleichnamigen Bereiche in der Felder-Leiste.
Hier ist Ihr Gantt-Diagramm in Power BI!
Erfahrungen mit Power BI
Frage vom 27.10.2020
Auf welche Weise findet Power BI erfahrungsgemäß in Unternehmen seine Anwendung?
Es ist ja als Selbsthilfe-Tool für jeden gestrickt, trotzdem halte ich es auch nicht für unwahrscheinlich, dass am Ende sehr wenige Personen für sehr viele Personen Dashboards basteln.
Welche Erfahrungen haben Sie denn schon gemacht bzw. gibt es da eine Tendenz die sich abzeichnet?
Antwort
Im Prinzip soll es genau so sein, wie Sie vermuten: Einige Berichtsdesigner erzeugen mit Power BI Desktop Berichte, welche sie über den Power BI-Dienst einer größeren Zahl an Nutzern zur Verfügung stellen.
Das Konzept soll gerade nicht sein, dass jeder mit Power BI Desktop seine eigenen Berichtsdateien baut, denn dann erzeugt jeder seine eigenen kleinen Wissensinseln und es findet kein Austausch von Erkenntnissen statt.
Wie dies in den Unternehmen umgesetzt wird, hängt natürlich sehr stark davon ab, ob diese den Power BI-Dienst verwenden und ob die Vorteile der Anwendung richtig kommuniziert werden.
Meiner Erfahrung nach tun sich viele Unternehmen mit dem Dienst sehr schwer, da sie ihre Daten nicht in der Microsoft-Cloud speichern wollen. Der Preis spielt dabei eine eher untergeordnete Rolle. Es geht vielmehr um Fragen der Datensicherheit.
Gerade durch die Corona-Krise haben Unternehmen jedoch den Nutzen und die Notwendigkeit von Cloud-Lösungen erkannt. Dagegen steht, dass Datenschützer jüngst wieder massiv Microsoft für seine Datenschutzbedingungen kritisieren.
https://www.heise.de/news/Datenschutzbehoerden-erklaeren-den-Einsatz-von-Microsoft-365-fuer-rechtswidrig-4931745.html
Es bleibt abzuwarten, wie sich die Lage hier entwickelt und ob Microsoft mehr auf die Wünsche der europäischen Kunden in Punkto Datenschutz eingehen wird.