PBI - praktyczne wykorzystanie zmiennych (var) do tworzenia tabeli wirtualnych, jako ekwiwalent pętli (loop) na przykładzie wystarczalności zapasu
Zmienne w programie Power BI odgrywają bardzo ważną rolę. Mogą one służyć do optymalizacji miar co istotnie wpływa na wydajność programu. Jedną z możliwości jest także tworzenie tzw. tabeli wirtualnych, co może okazać się pomocne przy procedurach wymagających pewnej powtarzalności jak w klasycznych pętlach (loop).
Wyobraźmy sobie, że mamy tabelę z informacją o planowanym poziomie zapasu na początek miesiąca oraz zakładanym popycie w danym miesiącu. Chcąc odpowiedzieć na pytanie, na ile miesięcy wystarczy zapasu przy danej sprzedaży, aby np. sterować zakupami powinniśmy policzyć tzw. wystarczalność. Pokrycie historyczne liczy się bardzo prosto, zaś opcja "do przodu" jest troszkę bardziej skomplikowana. Poniżej pokazałem prostą formułę w excelu, pokazującą na ile miesięcy sprzedaży wystarczy nam zapasu.
Tak na prawdę w excelu mamy wiele możliwości uzyskania powyższych wyników. Możemy np. napisać makro, które "porcjuje" nam tabelę w zależności od miesiąca początkowego, a następnie dla każdego wiersza (tabeli) sprawdza zapas względem popytu skumulowanego.
Jak jednak stworzyć miarę z wystarczalnością w Power BI? Nie jest to wcale proste, ale poniższa miara, którą później objaśnię szczegółowo może zostać wykorzystana.
Kliknij, aby powiększyć
Ostateczne wyniki powinny być takie same jak w pliku excel.
Przeanalizujmy teraz poszczególne elementy miary "wystarczalność". Numery linijek ułatwią nam analizę.
W linijkach od 2 do 4 tworzymy zmienne dotyczące miesiąca, zapasu i popytu dla bieżącego rekordu. Istotą zmiennych jest to, że oddziałują one na każdy rekord osobno, zatem nie powinno nas zmylić zastosowanie funkcji "max". Nie dotyczy ona w tym przypadku danych z całej tabeli, co widać na poniższym rysunku.
Linijka 5 to utworzenie dla każdego rekordu osobno tabeli zawierającej tylko miesiące większe lub równe niż bieżący miesiąc. Np. dla rekordu z miesiącem nr 5 powstanie tabela jak w kolumnach F:H (kolorowe pola) poniższego rysunku.
Linijka 6 to dodanie zapasu początkowego do utworzonej tabeli wirtualnej, zaś linijka 7 to dodanie skumulowanego popytu (licząc od bieżącego rekordu) - jak na rysunku powyżej. W linijce 8 dodajemy analogiczną kolumnę z warunkiem, będącym różnicą między zapasem początkowym, a skumulowanym popytem.
W linijce 9 tworzymy kolejną tabelę wirtualną, tym razem z rekordów, dla których _warunek1 jest większy lub równy 0. Przykładowo ogranicza to dane na powyższym rysunku do 4 rekordów z obramowaniem w kolumnach F:K.
Linijka 10 to "uchwycenie" minimum z wartości pozytywnych dla warunku 1, co przyda się później. Linijka 11 zlicza rekordy w ostatnio utworzonej tabeli wirtualnej. Każdy rekord odpowiada jednemu miesiącowi. Ich suma zostanie wykorzystana w wyniku ostatecznym w wersji zmodyfikowanej, tak jak w linijce 13.
Linijka 12 pozwala wskazać wartość maksymalną dla rekordów z warunkiem 1 mniejszym od zera. W naszym przykładzie powyżej jest to -8.
Ostateczna wystarczalność to liczba rekordów z tabeli posiadającej pozytywny warunek 1 (_czas_mod) i extraczas, czyli minimum z pozytywnych warunków 1 / (minimum z pozytywnych warunków 1 + wartość bezwzględna dot. maksimum z negatywnych warunków 1). Dla naszego przykładu będzie to: 2 / (2+8) = 0,2. Suma 4 oraz 0,2 daje ostateczny wynik dla danego rekordu wynoszący 4,2 m-ce.
Zastosowanie linijki 13 pozwala poradzić sobie z sytuacją jak w miesiącu 9, kiedy zapas początkowy jest niższy niż popyt danego miesiąca. Tabele wirtualne będą wyglądać następująco:
W tej sytuacji nie mamy pozytywnych warunków 1 i musimy wprost podzielić zapas przez popyt.