Eine Tabelle enthält Ihre Versandarten (DPD, DHL, GLS, etc.) sowie einen Paketpreis. Dagegen steht eine Auftragstabelle mit der Anzahl Packstücke und der genutzten Versandart. Stimmen die vom Spediteur berechneten Frachtkosten mit denen laut meinem System überein? Wir zeigen Ihnen, wie Sie schnell mit der Funktion RELATED() eine einfache Prüfung in Power Pivot erstellen!

Die Ausgangsdaten

Als Datenbasis nehmen wir zwei verbundenen Tabellen aus Excel – das sind zugegebenermaßen nicht viele Daten, aber das Prinzip sollte deutlich werden: Aufträge

Versandarten Diese beiden Tabellen importieren Sie als verknüpfte Tabellen in Ihr Power Pivot Datenmodell. Ich habe die Tabellen gleich umbenannt in Versandarten und Aufträge: Verknüpfte Tabellen im Power Pivot Modell Die beiden Tabellen verbinden wir über die Versandart. Verknüpfung

Die Berechnung des Preises

Mit den jetzigen Daten lässt sich ein einfaches Pivot erstellen, beispielsweise eine Übersicht über versandte Packstücke pro Versandart: Pivot Anzahl Packstücke/Versandart Aber das Ziel war die Frachtkosten zu berechnen. Dazu müssen wir das Feld Preis aus der Tabelle Versandarten in die Tabelle Aufträge bekommen. Dann können wir den Preis mit dem Feld Menge multiplizieren. Früher eine typische Aufgabe der VLOOKUP-Funktion, mit der die meisten von uns eine Hassliebe verbindet – nicht nur, dass sie sehr langsam war, sie war leider auch sehr fehleranfällig. Power Pivot stellt uns die RELATED()-Funktion zur Verfügung. Durch die Verknüpfung der beiden Tabellen, ist die Verbindung zwischen Aufträge und Versandarten eindeutig. Damit kann von einem berechneten Feld in der Tabelle Aufträge direkt auf Versandarten und damit auch auf das Feld Preis zugegriffen werden: berechnete Spalte mit RELATED() Damit können wir in einer weiteren Spalte den Preis berechnen: Kosten berechnen

Das Problem mit der Datenqualität

In der Tabelle Versandarten darf eine Versandart natürlich nur einmal vorkommen. Haben Sie zwei Zeilen mit beispielsweise DPD, verhindert Power Pivot bereits die Verknüpfung zwischen Aufträge und Versandarten. Natürlich werden mit Absicht in der Auftragstabelle aber Versandarten verwendet, die wir nicht in der Versandarten-Tabelle finden – oder aber wir haben keine Versandart angegeben. Damit erhalten wir für diese Fälle, also konkret für die Versandart AUX und im Falle des Auftrages mit der Nummer 5, keine Frachtkosten. Das sieht etwas unschön aus – eigentlich müssten wir herausfinden, ob die Funktion RELATED() etwas gefunden hat und falls nicht, eine Null eintragen:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

ISBLANK() prüft, ob in der Zelle ein Wert vorhanden ist. Falls nicht, wird Null eingetragen, falls doch, werden unsere Kosten korrekt berechnet.  “Eigentlich” deshalb, weil man sich in diesem Fall auch einfacher behelfen kann:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Wenn Sie Null zu “nichts” addieren, kommt bei Power Pivot ebenfalls Null heraus Smiley

Ich habe aber verschiedene Werte für Land und Versandart

Praktisch sind Frachtberechnungen komplexer – beispielsweise kostet DHL in die Schweiz mehr als nach Deutschland. Damit müssen wir die Auftrags- und Versandarten-Tabelle um ein Land ergänzen. Eine Verknüpfung funktioniert dann nicht, da man nur über ein Feld verknüpfen kann. Dies löst man mit der LOOKUPVALUE()-Funktion – ein Thema für einen weiteren Blog-Artikel Smiley