Formatowanie warunkowe to doskonały sposób na to, aby zwrócić uwagę odbiorcy na określone wartości w tabeli. No i jest to całkiem proste w implementacji. Chyba, że… Potrzebujesz czegoś bardziej konkretnego jak na przykład formatowanie w obrębie kolumny czy wiersza.
Ostatnio jeden z moich czytelników zgłosił się do mnie właśnie z takim problemem. Podzielę się również z Tobą rozwiązaniem tego problemu.
Na początek ściągnij sobie zestaw danych, które przygotowałem dla tego wpisu. Znajdziesz w nim tabelę ze sprzedażą firmy Adidas w USA za lata 2020-2021.
Pobierz pliki do tego wpisu
Aby móc pobrać pliki musisz się zalogować
Proste formatowanie całej matrycy
Matrix w Power BI ma wbudowane funkcje służące do formatowania warunkowego wartości.
Po prostu wybierasz wizualizację, klikasz prawym przyciskiem myszy na polu, które chcesz formatować, wybierasz formatowanie warunkowe i ustawiasz warunki, jakich potrzebujesz.
Proste, szybkie i bezproblemowe, prawda?
Niestety w tej swojej prostocie również odrobinę upośledzone. Jak widzisz formatowanie zostało zastosowane dla wszystkich wartości w matrycy. Bez podziału na wiersze/kolumny a Damiana pytanie konkretnie dotyczyło podświetlenia najwyższej i najniższej wartości z tym podziałem.
Więc zabierzmy się do roboty.
Formatowanie warunkowe na podstawie wierszy
Zacznijmy od tego, że dla każdego Regionu sprzedaży chcielibyśmy zobaczyć w którym miesiącu była najwyższa a w którym najniższa sprzedaż.
Aby to zrobić stwórz nową miarę.
__kolor_max_min_wiersz =
VAR __TempTab =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( Sales, Sales[Region], Kalendarz[Rok], Kalendarz[Miesiąc] ),
"@Sprzedaz", [TS]
),
ALLSELECTED ( Kalendarz )
)
VAR __Min =
MINX ( __TempTab, [@Sprzedaz] )
VAR __Max =
MAXX ( __TempTab, [@Sprzedaz] )
RETURN
SWITCH (
TRUE (),
__Min = __Max, "",
[ts] = __Min, "#ff0000",
[ts] = __Max, "#00ff00"
)
Rozłóżmy tę miarę na kawałki:
VAR __TempTab =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( Sales, Sales[Region], Kalendarz[Rok], Kalendarz[Miesiąc] ),
"@Sprzedaz", [TS]
),
ALLSELECTED ( Kalendarz )
)
Tworzymy tymczasową tabelę, która będzie składała się z kolumn:
’Sales'[Region]
’Kalendarz'[Rok]
’Kalendarz'[Miesiąc]
i dodajemy do tego kolumnę kalkulowaną [@Sprzedaz] w której znajduje się suma wartości z 'Sales'[Total Sales] zagregowana na podstawie poprzednich kolumn.
Możesz w prosty sposób podejrzeć zawartość tej tabeli tworząc nową tabelę i wklejając do niej kod.
Dalej mamy ALLSELECTED(). Po co to? Na co to?
Musisz pamiętać o kontekście filtrowania. Wartości, które są pokazywane w matrycy są filtrowane przez Region oraz Kalendarz. Aby poznać, jaka jest najwyższa i najniższa wartość w danym regionie na przestrzeni całego wyświetlanego okresu musimy pozbyć filtrowania na poziomie kalendarza.
Czyli za pomocą tej funkcji mówimy:
Oblicz tabelę W której będą wszystkie widoczne lata/miesiące ale tylko dla wybranego regionu.
Dalej mamy kolejne dwie zmienne za pomocą których obliczysz minimalną i maksymalną wartość sprzedaży w danym regionie.
VAR __Min =
MINX ( __TempTab, [@Sprzedaz] )
VAR __Max =
MAXX ( __TempTab, [@Sprzedaz] )
Dzięki tym dwóm zmiennym poznamy najniższą i najwyższą wartość sprzedaży dla każdego z regionów we wszystkich wyświetlanych miesiącach.
I na sam koniec pozostaje nam napisanie jakiej wartości oczekujemy:
RETURN
SWITCH (
TRUE (),
__Min = __Max, "",
[ts] = __Min, "#ff0000",
[ts] = __Max, "#00ff00"
)
Za pomocą funkcji SWITCH() ustalamy reguły:
- jeżeli wartość zmiennej ’Min’ = wartość zmiennej ’ __Max’ oznacza to, że w danym okresie występuje tylko jedna taka wartość, czyli nie chcemy jej kolorować. Więc ustawiamy kolor formatowania na pusty, czyli tylko „”
- Jeżeli wyświetlana wartość [TS] jest równa zmiennej '__Min’, to oznacza to, że jest to najniższa wartość i chcemy dać jej kolor czerwony. Czyli posługując się notacją hex chcemy uzyskać wartość „#FF0000” (jeżeli chcesz dowiedzieć się więcej na temat tej notacji – zajrzyj do Wikipedii.
- Analogicznie jeżeli [TS] = '__Max’ to chcemy pokolorować na zielono, czyli dajemy tutaj wartość „#00FF00”
Teraz jeżeli chcesz wizualnie zobaczyć do których komórek zostały obliczone jakie wartości to możesz sobie tę miarę po prostu wrzucić do matrycy.
Uff. Największa część pracy już za nami. Teraz wystarczy nam zastosować formatowanie warunkowe na podstawie tej miary.
Formatowanie warunkowe na podstawie kolumn
A tutaj mam dla Ciebie niespodziankę – w zasadzie cała pracę już wykonaliśmy 🙂 Spójrzmy jeszcze raz na miarę, którą przed chwilą przygotowaliśmy i zmieńmy w niej jedną linijkę:
__kolor_max_min_wiersz =
VAR __TempTab =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( Sales, Sales[Region], Kalendarz[Rok], Kalendarz[Miesiąc] ),
"@Sprzedaz", [TS]
),
ALLSELECTED ( Sales[Region] )
)
VAR __Min =
MINX ( __TempTab, [@Sprzedaz] )
VAR __Max =
MAXX ( __TempTab, [@Sprzedaz] )
RETURN
SWITCH (
TRUE (),
__Min = __Max, "",
[ts] = __Min, "#ff0000",
[ts] = __Max, "#00ff00"
)
Widzisz co się zmieniło?
Zmieniliśmy ALLSELECTED ( Kalendarz ) na ALLSELECTED ( Sales[Region] ).
Ta zmiana wynika z tego, że interesuje nas inny kontekst znalezienia najwyższej/najniższej wartości. Nie szukamy we wszystkich miesiącach w jednym regionie, tylko we wszystkich regionach ale danego miesiąca.
Zobaczmy jak wygląda wynik tej zmiany
Widzisz? W każdej kolumnie oznaczona jest najwyższa i najniższa wartość.
Formatowanie warunkowe wierszy/kolumn gradientem
No to skoro z prostym oznaczaniem wartości poszło nam tak dobrze – zróbmy coś bardziej ambitnego.
Użytkownik lubi jak jest kolorowo i chciałby, aby wszystkie wartości w danej kolumnie/wierszu były oznaczone kolorami według ich wartości.
Aby to zrobić znowu będziemy bazować na stworzonej mierze. Ale tym razem będziemy musieli ją trochę bardziej zmodyfikować.
Przede wszystkim – dotychczas nadawaliśmy kolory zgodnie z modelem HEX. W związku z tym, że chcemy otrzymać formatowanie gradientowe, zmienimy to i będziemy operować w modelu HSLA, który da nam więcej możliwości sterowania barwami oraz zwyczajnie będzie prostszy w implementacji niż przeliczanie kolorów na HEX.
Więc zaczynamy.
__kolor_gradient_kolumna =
VAR __TempTable =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( Sales, Sales[Region], Kalendarz[Rok], Kalendarz[Miesiąc] ),
"@Sprzedaz", [TS]
),
ALLSELECTED ( Sales[Region] )
)
VAR __Min =
MINX ( __TempTable, [@Sprzedaz] )
VAR __Max =
MAXX ( __TempTable, [@Sprzedaz] )
VAR __Zakres = __Max - __Min
VAR __Odcien = ROUND( (DIVIDE( [TS] - __Min, __Zakres) * 120), 0)
RETURN
"hsla(" & __Odcien & ", 100%, 50%, 1)"
Zanim prześledzimy zmiany jeszcze napiszę kilka zdań o HSLA. Dzięki temu lepiej zrozumiesz co tam się dzieje.
W modelu HEX (czyli #FFFFFF) opisujemy kolor, który chcemy uzyskać w najprostszy z możliwych sposobów. Mieszając ze sobą trzy podstawowe kolory – Red/Czerwony, Green/Zielony i Blue/Niebieski. Każdy ze znaków po „#” opisuje intensywność każdego z nich w systemie heksadecymalnym według schematu #RR GG BB.
Co oznacza, że w systemie heksadecymalnym? To, że najniższa wartość oznaczona jest jako „00” a najwyższa jako „FF”.
Czyli na przykładach:
- #000000 – oznacza brak jakichkolwiek kolorów czyli biały.
- #FF0000 – tylko czerwony
- #00FF00 – tylko zielony
- #0000FF – tylko niebieski
- #FFFFFF – zmieszane wszystkie kolory podstawowe czyli czarny
HSLA czyli odcień, nasycenie, jasność i przezroczystość
Tutaj kolory opisujemy według wzorca (odcień, nasycenie, jasność, przezroczystość) na przykład (0, 100%, 50%, 1) to będzie czysty czerwony. Skąd to się bierze?
- odcień – czyli kolor „bazowy” o wartościach od 0 do 360 gdzie:
- 0 – czerwony
- 60 – żółty
- 120 – zielony
- 180 – cyjan
- 240 – niebieski
- 300 – magenta
i oczywiście wszystkie pozostałe odcienie pomiędzy nimi
- nasycenie – czyli jak dużo koloru jest w kolorze 🙂
- 0% – będzie to tylko odcień szarości
- 100% – pełen kolor
- jasność – czyli czy chcemy, aby nasz kolor był ciemniejszy czy jaśniejszy
- 0% – otrzymamy kolor czarny
- 100% – otrzymamy kolor biały
- 50% – najdokładniejsze odwzorowanie koloru, który chcemy
- przezroczystość – jak sama nazwa wskazuje
- 0 – kolor będzie niewidoczny
- 1 – brak przezroczystości
Uff. Przebrnęliśmy. Niestety ta kropelka wiedzy o modelach kolorów będzie bardzo przydatna do zrozumienia dalszej części wpisu. A jeżeli temat Cię zainteresował to właśnie tworzę szerszy wpis i modelach kolorów, których możemy używać w Power BI.
No dobra. Wracamy do naszej miary. Przejrzyjmy zmiany:
VAR __Zakres = __Max - __Min
Obliczamy po prostu różnicę między najwyższą a najniższą wartością w kolumnie. To obliczenie będzie nam potrzebne w kolejnej zmiennej, gdzie obliczymy wartość, którą zastosujemy jako odcień w konstruowaniu koloru.
VAR __Odcien = ROUND( (DIVIDE( [TS] - __Min, __Zakres) * 120), 0)
I tutaj przydaje nam się wcześniejsza teoria 🙂 W naszej wizualizacji interesują nas kolory od czerwonego do zielonego. Jak napisałem 0 oznacza czerwień a 120 zieleń na naszym kole barw. Dlatego wartość, której tutaj potrzebujemy musi mieścić się w tym zakresie.
Rozłóżmy tę zmienną na części:
[TS] – __Min – obliczamy różnicę pomiędzy wyświetlaną wartością sprzedaży a minimalną wartością sprzedaży. Dla minimum osiągniemy wynik 0, dla maksimum osiągniemy wartość najwyższą i dla wszystkich pozostałych będzie to stopniowanie. Ale nadal potrzebujemy wartości w przedziale 0-120. Więc liczymy dalej.
DIVIDE( [TS] – __Min, Zakres) – dzięki podzieleniu przez poprzednią zmienną czyli Zakres otrzymujemy w wyniku wartości mieszczące się w przedziale 0-1. Dlatego w kolejnym kroku…
DIVIDE( [TS] – __Min, __Zakres) * 120 – mnożymy ten wynik przez 120 dzięki czemu otrzymujemy wartości dokładnie w przedziale, którego potrzebujemy. Problem polega na tym, że otrzymujemy tutaj wartości dziesiętne a aby określić odcień potrzebujemy liczby całkowitej. Więc to wszystko jeszcze otaczamy…
ROUND( (DIVIDE( [TS] – __Min, __Zakres) * 120), 0) – kolejną funkcją, która zaokrągli wynik do liczby całkowitej.
I jesteśmy w domu. Mamy już odcienie, które możemy dalej wykorzystać.
RETURN
"hsla(" & __Odcien & ", 100%, 50%, 1)"
Do stworzenia całej wartości hsla zgodnie z podanym wcześniej schematem. Znowu rozłóżmy wynik na części pierwsze:
„hsla(„ – zaczynamy string od informacji, że następne znaki to będzie zakodowany kolor
__Odcien – wynik zmiennej
100% – nasycenie
50% – jasność
1 – przezroczystość
I nasza kolorowa miara jest gotowa. Możemy ją użyć w formatowaniu warunkowym dokładnie w taki sam sposób jak robiliśmy to poprzednio.
Pamiętaj o tym, że tutaj też robimy to w oparciu o wartość pola a nie gradient!
I gotowe! Jest kolorowo, użytkownicy są zadowoleni a Ty możesz spokojnie napić się kawy. A w wolnej chwili pobaw się wartościami nasycenia, jasności i przezroczystości i zobacz w jaki sposób będą one wpływały na wyświetlane kolory.
