Ich wurde letztens von einem Seminarteilnehmer gefragt, wie er ein Trend- bzw. ein Ampelsystem in Power BI integrieren könne. In den Visualisierungen Matrix oder Tabelle verwendete er dafür die Bedingte Formatierung. Nun wollte er wissen, wie er diese auch in anderen Visualisierungen nutzen könne. Konkret ging es ihm darum, eine Abweichung vom Durchschnitt in den Ampelfarben grün, gelb und rot zu visualisieren.
Die Beispieldaten
Gehen wir einmal von folgenden Daten aus:
| Kunde | Wert |
| A | 200 € |
| A | 800 € |
| B | 600 € |
| B | 500 € |
| B | 300 € |
| C | 500 € |
| C | 400 € |
| D | 200 € |
| D | 900 € |
Hier können Sie die Excel-Datei Ampelfarben.xlsx herunterladen:
Wir zeigen die Daten jetzt in einem Säulendiagramm:
Wir wollen nun ein Ampelsystem integrieren, welches den Säulen entsprechend der Abweichung vom Durchschnittsumsatz eine bestimmte Farbe zuweist.
Wir wollen Umsätze über dem Durchschnitt grün, Umsätze bis zu 10 % unter dem Durchschnitt gelb und alle anderen Umsätze rot einfärben.
So berechnen wir den Durchschnittsumsatz und die Abweichung
Zuerst einmal legen wir das Measure Umsatz an:
Umsatz = SUM(Bestellungen[Wert])
Wir summieren hier mit der Funktion SUM einfach alle Werte aus der Tabelle.
Anschließend ermitteln wir die Anzahl der Kunden. Achtung, hier können wir keine der Standardfunktionen zum Zählen benutzen, da wir jeden Kunden natürlich nur einmal zählen dürfen. Wir nutzen daher die Funktion DISTINCTCOUNT.
Anzahl Kunden = DISTINCTCOUNT(Bestellungen[Kunde])
Mit diesen beiden Kennzahlen können wir nun den Durchschnittsumsatz berechnen. Dies tun wir, indem wir den Umsatz durch die Anzahl der Kunden teilen. Wichtig ist, dass wir die Berechnung immer für die gesamte Tabelle durchführen. Standardmäßig würde der Abfragekontext der Visualisierung ja dazu führen, dass jede Säule nur die eigenen Umsätze aus der Modelltabelle sieht. Dies verhindern wir, indem wir die Funktionen CALCULATE und ALL mit in die Berechnung aufnehmen.
Durchschnittsumsatz = CALCULATE(DIVIDE([Umsatz],[Anzahl Kunden]),ALL(Bestellungen))
Die Funktion DIVIDE verhindert hierbei einen Fehler, falls der Nenner der Division einmal 0 sein sollte (auch wenn das im aktuellen Beispiel zugegebenermaßen eher unwahrscheinlich ist).
Nun haben wir alles, um die Abweichung vom Durchschnittsumsatz zu berechnen. Wir subtrahieren vom Umsatz den Durchschnittsumsatz und setzen die Differenz ins Verhältnis zu diesem.
Abweichung = DIVIDE([Umsatz]-[Durchschnittsumsatz],[Durchschnittsumsatz])
Hier sehen Sie alle vier Measures einmal testweise in einer Tabellenvisualisierung:
So stellen wir die Ampelfarben im Diagramm ein
Jetzt haben wir alles, um im Säulendiagramm das Ampelsystem zu integrieren. Dazu wechseln wir in den Format-Bereich (Pinselrolle) und wählen den Menüpunkt Datenfarben.
Über die Schaltfläche fx können wir eine bedingte Formatierung hinterlegen. Standardmäßig benutzt Power BI dabei eine Farbskala.
Wir brauchen für unsere Zwecke jedoch Regeln und keine Farbskala. Wir stellen daher oben links im Auswahlmenü unter Formatieren nach den Punkt Regeln ein.
Die Regeln müssen sich auf die Abweichung beziehen. Wir suchen daher in dem Auswahlmenü Basierend auf Feld das Measure Abweichung heraus.
Jetzt können wir die Bedingungen für das Ampelsystem definieren:
1. Regel für Ampelfarbe Grün:
Alle Säulen, die über dem Durchschnittsumsatz liegen, sollen eine grüne Farbe erhalten. Dies ist erfüllt, wenn die Abweichung größer als 0 ist. In Power BI müssen wir übrigens immer auch eine Obergrenze der Regel festlegen. Kleiner Trick: Wir löschen einfach die Zahl hinter ist kleiner als, damit Power BI als Obergrenze die größte Zahl (Maximum) verwendet.
Darüber hinaus müssen wir darauf achten, die Einheit hinter ist größer als auf Zahl umzuschalten! Hier die fertige Regel:
2. Regel für Ampelfarbe Gelb
Wir klicken auf die Schaltfläche + Neue Regel, um eine weitere Bedingung zu definieren. Wenn der Umsatz bis zu 10 % unter dem Durchschnittsumsatz liegt, dann soll die Säule eine gelbe Farbe erhalten. Wir müssen darauf achten, in Power BI als Dezimaltrennzeichen den Punkt zu verwenden!
3. Regel für Ampelfarbe Rot
Nun kommt die letzte Bedingung. Alle Umsätze, die mehr als 10 % unter dem Durchschnittsumsatz liegen, sollen rot werden. Dazu definieren wir folgende Regel:
Hier sehen Sie einmal den gesamten Regeldialog im Überblick:
Unser Diagramm sieht nun wie folgt aus:
So zeigen wir den Durchschnittsumsatz im Diagramm an
Zu guter Letzt wollen wir im Diagramm natürlich noch den Durchschnittsumsatz als Linie zeigen. Dazu haben wir sogar zwei Möglichkeiten:
Entweder gehen wir in den Analyse-Bereich (Lupensymbol) und aktivieren dort die Durchschnittslinie.
Das Diagramm zeigt eine schöne Durchschnittslinie, die sich einmal komplett über den gesamten Diagrammbereich erstreckt. Die Linie können wir beliebig formatieren und auch die Datenbeschriftung aktivieren.
Oder wie ändern den Diagrammtyp um in Linien- und gruppiertes Säulendiagramm. Dann können wir auch das Measure Durchschnittsumsatz im Diagramm verwenden. Wir ziehen es in den Bereich Zeilenwerte.
Hier wird jedoch die Durchschnittslinie leider nicht ganz durchgezogen angezeigt, weshalb ich persönlich die erste Variante bevorzuge.
Hier laden Sie die fertige Power BI Desktop-Datei kostenlos herunter:
Fazit
Die Bedingte Formatierung finden Sie im Säulendiagramm über die fx-Schaltfläche im Bereich Datenfarben. Sie können dort Regeln definieren, in denen Sie auf jedes Measures des Datenmodells beziehen können. Und mit einigen wenigen DAX-Funktionen können Sie auch komplexere Berechnungen, wie etwa die Abweichung vom Durchschnittsumsatz berechnen.