DeclutterData Datenoptimierung und Controlling DeclutterData Datenoptimierung und Controlling

Startseite / Wissen / DAX

Wer war am besten? Den Namen des Top-Verkäufers per DAX anzeigen

DAX · 5 Min. Lesezeit · von Phillip Seefeld

Illustration: Eine Frau sitzt vor Liniendiagramm, Balken und einem Kreisdiagramm

Hallo Daten-Freunde! Dein Bericht zeigt den höchsten Umsatz je Region. Die Geschäftsführung fragt als Nächstes: Wer hat diesen Umsatz erzielt? 🤔

Ein Measure mit MAX hilft dabei nicht, denn es liefert nur die Zahl. Den Namen holst du mit TOPN und CONCATENATEX. TOPN holt die beste Zeile, CONCATENATEX macht aus ihr Text, und bei Gleichstand erscheinen mehrere Namen. Alle Beispieldaten in diesem Artikel sind ausgedacht.

Das Beispielmodell

Das Modell hat zwei Tabellen. Verkäufer enthält die Spalte Name. Umsatz enthält die Spalten Verkäufer, Region und Betrag. Die Beziehung läuft von Umsatz[Verkäufer] zu Verkäufer[Name], viele zu eins.

VerkäuferRegionBetrag
AnnaNord1.800
BenNord1.100
DoraSüd1.500
EmilSüd1.500
FinnSüd700

Dora und Emil liegen in Süd gleichauf. Das ist Absicht, denn genau beim Gleichstand gehen einfache Lösungen schief.

Die Formel in zwei Schritten

Schritt 1 ist ein einfaches Measure für den Umsatz:

Gesamtumsatz = SUM ( Umsatz[Betrag] )

Schritt 2 liefert den Namen:

Top Verkäufer =
VAR Kandidaten =
    FILTER ( VALUES ( 'Verkäufer'[Name] ), NOT ISBLANK ( [Gesamtumsatz] ) )
VAR Beste =
    TOPN ( 1, Kandidaten, [Gesamtumsatz], DESC )
RETURN
    CONCATENATEX ( Beste, 'Verkäufer'[Name], ", ", 'Verkäufer'[Name], ASC )

Der Tabellenname enthält ein ä, deshalb steht er in DAX in einfachen Anführungszeichen. Ohne sie meldet Power BI einen Syntaxfehler. Mit einem Namen ohne Umlaut, zum Beispiel Verkaeufer, entfallen sie.

Legst du beide Measures in eine Matrix mit Region in den Zeilen, siehst du in Nord den Namen Anna, in Süd die Namen Dora und Emil und in der Gesamtzeile Anna. So kam es bei mir mit den fünf Beispielzeilen heraus.

Was die Formel Zeile für Zeile tut

Die Region in der Matrix kommt aus der Tabelle Umsatz und filtert die Tabelle Umsatz. Die Tabelle Verkäufer erreicht dieser Filter nicht, denn die Beziehung gibt den Filter nur von Verkäufer nach Umsatz weiter. VALUES liefert deshalb in jeder Zeile alle fünf Namen. FILTER behält davon nur die Zeilen, bei denen [Gesamtumsatz] nicht leer ist. In Nord bleiben dadurch Anna und Ben übrig, die anderen drei haben dort keinen Umsatz. Das Measure [Gesamtumsatz] wird dabei für jeden Namen einzeln berechnet, weil es innerhalb von FILTER im Zeilenkontext steht.

TOPN mit dem Wert 1 gibt aus dieser Tabelle die Zeile mit dem höchsten Umsatz zurück. DESC ist die Vorgabe und steht der Lesbarkeit halber da. Die Zeilen, die TOPN liefert, stehen nach Microsoft in keiner bestimmten Reihenfolge.

CONCATENATEX läuft über die Tabelle Beste und setzt je Zeile den Namen ein. Das dritte Argument ist das Trennzeichen, hier Komma mit Leerzeichen. Das vierte und fünfte Argument sortieren die Namen im Ergebnistext aufsteigend, damit die Reihenfolge nicht zufällig wechselt. Ohne Sortierangabe stehen die verketteten Werte laut Microsoft in keiner bestimmten Reihenfolge.

Gleichstand und leere Werte

Gibt es am letzten Platz einen Gleichstand, liefert TOPN alle gleichplatzierten Zeilen und damit unter Umständen mehr Zeilen als verlangt. Das steht in der Microsoft-Dokumentation. In Süd gewinnen deshalb beide, und das Measure zeigt „Dora, Emil“.

Ob bei Gleichstand beide Namen genannt werden oder eine zweite Regel entscheidet, zum Beispiel der frühere Abschluss, ist eine fachliche Entscheidung. Die Formel zeigt beide, bis jemand eine Regel festlegt. Klär das mit der Fachabteilung, bevor der Bericht in die Geschäftsführung geht.

Warum der Filter? Wenn für eine Region niemand Umsatz hat, sind alle Werte leer. Ob TOPN leere Werte als Gleichstand behandelt, beschreibt die Dokumentation nicht. Im Test ohne Filter lieferte TOPN bei lauter leeren Werten alle fünf Namen: „Anna, Ben, Dora, Emil, Finn“. Mit FILTER und NOT ISBLANK stellt sich die Frage gar nicht, denn Personen ohne Umsatz sind nie Kandidaten.

Und wenn gar kein Kandidat übrig bleibt? Das passiert zum Beispiel, wenn ein Datenschnitt auf Verkäufer nur Anna zulässt. Dann liefert CONCATENATEX BLANK, also einen leeren Wert und keinen leeren Text. Die Zeile Süd erscheint dann in der Abfrage gar nicht mehr, weil Anna dort keinen Umsatz hat. Ohne den FILTER-Schritt stünde dort „Anna“, obwohl sie in Süd nichts verkauft hat. Wie deine Matrix oder Karte BLANK darstellt, schaust du dir am besten selbst an.

Variante: die ersten drei

Willst du die besten drei, ändere den Wert in TOPN. Die Namen sortierst du nach Umsatz absteigend, bei gleichem Umsatz nach Name aufsteigend:

Top 3 Verkäufer =
VAR Kandidaten =
    FILTER ( VALUES ( 'Verkäufer'[Name] ), NOT ISBLANK ( [Gesamtumsatz] ) )
VAR Beste =
    TOPN ( 3, Kandidaten, [Gesamtumsatz], DESC )
RETURN
    CONCATENATEX (
        Beste,
        'Verkäufer'[Name],
        ", ",
        [Gesamtumsatz], DESC,
        'Verkäufer'[Name], ASC
    )

In der Gesamtzeile steht „Anna, Dora, Emil“, Dora also vor Emil. Auch hier gilt: Bei Gleichstand auf Platz drei können mehr als drei Namen erscheinen.

Einsatz und Grenzen

Das Measure rechnet im Kontext des Visuals, deshalb passt sich das Ergebnis an die Region und an einen Datenschnitt an. Laut Microsoft wird CONCATENATEX im DirectQuery-Modus in berechneten Spalten und in RLS-Regeln nicht unterstützt. Für ein Measure im Bericht spielt das keine Rolle.

Wer lieber mit einem Rang arbeitet, kann sich RANKX ansehen. Die Funktion liefert den Rang einer Zahl in einer Liste. Ob das Ergebnis kürzer oder besser lesbar wird, habe ich hier nicht untersucht.

So prüfst du das Ergebnis

Vergleiche mit den fünf Zeilen der Tabelle oben. Erwartet werden in Nord Anna, in Süd Dora und Emil, gesamt Anna. Ändere dann Finn auf 1.500, in Süd stehen jetzt drei Namen: Dora, Emil und Finn. Setze zusätzlich einen Datenschnitt auf Verkäufer und wähle nur Anna, um den Leerfall zu sehen.

Prüfe das Ergebnis immer gegen eine Handrechnung, bevor der Bericht weitergeht.🙂

Du brauchst Hilfe bei DAX oder beim Aufbau deines Berichtswesens? Schau dir meine Leistungen zu Power BI an.

Quellen
Phillip Seefeld
Der Autor

Hi, ich bin Phillip!

Power Platform Consultant und Controller aus Burglengenfeld bei Regensburg. In meinen Wissensartikeln zeige ich dir Tricks rund um Excel, Power Query und Power BI, die dir im Alltag Zeit sparen. Fast immer mit Beispieldatei zum Nachbauen.🙂

Jeden Artikel prüfe ich fachlich selbst und stehe mit meinem Namen dafür ein.

Lass uns den Datendschungel lichten 🪓

Ich freue mich auf dein Projekt!

Nimm gleich Kontakt mit mir auf und lass uns herausfinden, wie ich dir helfen kann! Ein Erstgespräch ist immer kostenlos. 🙂