KPI w Power Pivot to ciekawe narzędzie, które na pewno warto poznać i stosować. KPI to jeden z głównych elementów każdego dashbordu. Możemy je łatwo dodać do naszego modelu, ale zanim to zrobimy musimy dobrze zrozumieć, kiedy i jak je stosować.

Czym są KPI?

KPI to skrót, który często przewija się przez duże organizacje. Mówi się o nim zarówno na poziomie zarządu jak i na poziomie podstawowych procesów w organizacji. KPI to skrót od słów Key Performance Indicators, czyli Kluczowe Wskaźniki Efektywności. Jak nazwa wskazuje powinny to być wskaźniki, które wymagają szczególnego monitorowania, aby osiągać zamierzone cele i trzymać się określonych standardów. Sama nazwa wskazuje, że są to najważniejsze wskaźniki dla firmy. Nic bardziej mylnego… 😉

Oczywiście powinny być używane dla takich elementów jak osiągane zyski, poziom satysfakcji klientów, jakość produktów itd., ale nic nie stoi na przeszkodzie, aby KPI zastosować dla pojedynczych procesów funkcjonujących w organizacji. KPI możemy wyznaczyć np. dla % błędów na danym odcinku procesu produkcyjnego, skalę rotacji pracowników w danym obszarze, ocenę pracy telemarketerów itd. Dla KPI najważniejszą zasadą jest to, że mają być najważniejszymi wskaźnikami, które mają śledzić określone osoby, a niekoniecznie cała organizacja. Dlatego jeśli tworzysz raport dla handlowców to KPI muszą być wprost związane z ich wynikami np. poziom realizacji planów sprzedaży lub liczbę kontaktów z klientami. Nie ma sensu wprowadzać im KPI na poziom marży, jeśli nie mają wpływu na cenę produktów.

Druga charakterystyczna cecha wskaźników KPI to ich bardzo prosta i przejrzysta prezentacja. Czytelnik raportu ma w ciągu paru chwil określić czy osiągane wyniki są dobre czy złe. Zazwyczaj wyniki KPI w dashboardach prezentujemy za pomocą kolorowych symboli. Ponadto stosuje się znany wszystkim schemat kolorów tj. zielony – dobrze, żółty – średnio, czerwony – źle. Dzięki temu wyniki są zrozumiałe dla każdego, możemy je prezentować pracownikom produkcyjnym, telemarketerom, pracownikom administracyjnym. Nawet jeśli nie znają się na raportach i analizach to szybko określą czy obszar, w którym pracują ma się dobrze czy źle.

Jak dodać KPI w Power Pivot do modelu danych

W najprostszych słowach możemy powiedzieć, że KPI w Power Pivot to specjalny rodzaj formatowania warunkowego. Po części tak, ale zasady ich tworzenia są zupełnie odmienne. Poniżej krok po kroku jak dodać KPI w Power Pivot.

Przykładowe dane

Do prezentacji jak działają KPI w Power Pivot wystarczy bardzo prosty zestaw danych. Ze względu na to, że KPI mogą działać na dwa sposoby wykorzystamy dane dotyczące zysków ze sprzedaży oraz stanów magazynowych.

Aby dodać dane do modelu danych w Power Pivot muszą być one w formie zdefiniowanej tabeli. Możemy je dodać na dwa sposoby. Pierwszy to bezpośrednie dodanie do modelu przez kliknięcie przycisku Dodaj do modelu danych na karcie Power Pivot. Jest to wygodne i szybkie rozwiązanie, ale trzeba na nie uważać. Jeżeli posiadamy dane, które będziemy chcieli przekształcać lub filtrować albo zmieniać ich lokalizację to to nie jest to dobry sposób. Tu dodajemy dane w takiej formie jaką mamy i tylko z pliku, w którym znajduje się model.

Drugi sposób, który zawsze polecam to pobranie danych przez Power Query, a następnie załadowanie ich do modelu danych. Nawet jeżeli nie modyfikujemy danych to w przyszłości zawsze będziemy mogli to zrobić, a także wygodnie zmienimy źródło danych, jeżeli chcemy je przenieść do innego pliku. Dane do Power Query dodajemy z karty Dane przyciskiem Z tabeli/zakresu lub wybierając z listy pod przyciskiem Pobierz dane. Z Power Query wybieramy opcję utworzenia tylko połączenia i dodania danych do modelu danych.

Utworzenie miar w modelu Power Pivot

Wskaźniki KPI w Power Pivot możemy tworzyć tylko na miarach, nawet gdy mamy proste dane, na których od razu chcielibyśmy pokazać wskaźniki. Jest to jak najbardziej zasadne, bo przecież przy dodawaniu pól do obszaru wartości w tabeli przestawnej zawsze są tworzone niejawne miary.  Miary widoczne są w obszarze obliczeń który znajduje się pod tabelą danych. Jeśli go nie widać to należy kliknąć przycisk Obszar obliczeń na karcie Narzędzia główne.

Miary też możemy tworzyć na dwa sposoby. Pierwszy szybki, prosty i niepolecany. Wystarczy zaznaczyć kolumnę, na której chcemy działać i wybieramy z karty Narzędzia główne Autosumowanie. Z listy możemy też wybrać pięć innych miar. Wadą tego rozwiązania jest automatyczna nazwa miary, którą zazwyczaj chcemy zmienić oraz ograniczenie tylko do najprostszych obliczeń. Dla przykładu możemy utworzyć tak sumę z kolumny Stan zapasów oraz Planowany stan zapasów. Aby zmienić ich nazwy na krótsze (Zapasy stan i Zapasy plan) wystarczy kliknąć na mierze dwa razy lub zaznaczyć ją i zmienić nazwę w pasku formuły.

Drugi sposób dodania miar to samodzielne jej napisanie. W ten sposób musimy działać zawsze, gdy chcemy wykonać bardziej skomplikowane obliczenia. Ponadto za każdym razem pisania zwiększamy swoją biegłość posługiwania się językiem DAX. W ten sposób utworzymy miarę, która wyliczy marżę procentową. Klikamy na dowolne puste pole w obszarze obliczeń i zaczynamy od podania nazwy Marża %, następnie dwukropek i znak równa się, dalej już formuły. Musimy zsumować zysk i podzielić go przez sumę przychodów. Miara ta będzie pokazywać odpowiednią marżę dla dowolnego zestawu miesięcy użytych w tabeli przestawnej.

.

Ustawienie formatowania miar – drobna rzecz, a warto o tym pamiętać

Kolejną rzeczą, o której należy zawsze pamiętać to, aby ustawić odpowiednie formatowanie wartości miar. Format jaki tu ustawimy będzie nadany w każdej tabeli przestawnej w której użyjemy miar. Jest to tym bardziej istotne im więcej tabel przestawnych chcemy tworzyć. Aby ustawić formatowanie zaznaczamy miary i wybieramy dla zapasów format liczb całkowitych i włączamy separator tysięcy. Dla marży ustawiamy format procentowy z jednym miejscem po przecinku.

Efekt ustawienia formatowania na miarach widać bezpośrednio w tabeli przestawnej po dodaniu ich do pola wartości.

Dodanie KPI w Power Pivot – 3 sposoby

KPI w Power Pivot możemy dodać na trzy sposoby. Dwa bezpośrednio w modelu danych, a jeden w Excelu. Mając otwarty model danych musimy zaznaczyć miarę, której ma dotyczyć KPI. Następnie możemy kliknąć na niej prawym przyciskiem myszy i wybrać polecenie Utwórz wskaźnik KPI lub takie samo polecenie wybrać na karcie Narzędzia główne.

Jeżeli KPI chcemy utworzyć na poziomie Excela to musimy przejść do karty Power Pivot, a następnie z pola Wskaźniki KPI wybrać opcję Nowy wskaźnik KPI lub Zarządzaj wskaźnikami KPI i wtedy wybrać polecenie Nowy.

Okno tworzenia wskaźnika KPI

Okno tworzenia wskaźnika KPI widoczne na poniższym screenie składa się z pięciu elementów:

1. Pod pojęciem Podstawowe pole wskaźnika KPI (wartość), kryje się miara, na której tworzymy KPI. W przypadku tworzenia KPI bezpośrednio w modelu danych nie możemy zmieniać tego parametru, bo na określonej mierze otworzyliśmy to okno. Dodając KPI z Excela możemy wybrać sobie odpowiednią miarę.
2. Drugie pole określa sposób określania wartości KPI. Możemy wybrać inną miarę, która ma być porównywana z naszą miarą lub wartość bezwzględną, czyli wprost podane wartości jakie nas interesują.
3. Wartości progowe to wartości procentowe lub bezwzględne przy których KPI ma wizualizować stan dobry, średni i zły. W tym miejscu określamy w jakiej kolejności mają być stany oraz czy odchylenia interesują nas w jedną stronę czy w obie. Na końcach paska prezentującego przedziały są strzałki, którymi możemy sobie zwiększać lub zmniejszać wyświetlany zakres.
4. Styl ikon to czysto wizualny aspekt KPI, jest dość ubogi, ale jak już pisałem na KPI mają działać tak, aby szybko każdy określił, czy wynik jest dobry czy zły. Nietypowe ikony i kolory na pewno by temu nie sprzyjały.
5. Ostatni element to opisy, przez wiele osób nawet niezauważany. Jak je stosować i jakie mają znaczenie opiszę w dalszej treści wpisu.

Tworzenie KPI w Power Pivot dla marży procentowej – wartości bezwzględne

Załóżmy, że naszą oczekiwaną marżą, z której menadżerowie będą zadowoleni to poziom 15%. Jest to wartość bezwzględna, dlatego w drugim polu KPI wybieramy Wartość bezwzględna i wpisujemy 0,15 (nie możemy tu używać %). Następnie w polu trzecim określamy przedziały. Możemy je wpisać bezpośrednio lub przesuwać suwaki. Ustawmy pierwszy przedział na 0,12 a drugi 0,15. Styl ikon możemy ustawić na drugi.

Po utworzeniu wskaźnika KPI widzimy charakterystyczny symbol KPI w polu miary w obszarze obliczeń, w tabeli danych w widoku diagramu modelu danych oraz jako nowy zestaw pól w tabeli przestawnej.

Użycie KPI w tabeli przestawnej

Po utworzeniu KPI w tabeli przestawnej mamy trzy zgrupowane wartości. Pierwsza to nasza utworzona miara licząca procentową marżę. Druga zawiera ustalony przez nas cel, czyli 0,15 (warto w tabeli zmienić styl na 15,0%). Trzecia wartość to Stan, czyli wizualna prezentacja wyników. I właśnie to jest sedno całej tej pracy, wystarczy zaprezentować same symbole by określić poziom osiąganych wyników. Umieszczenie ich w dashboardzie zajmie mało cennego miejsce, a po wydrukowaniu będą nadal bardzo czytelne. Same symbole wystarczą do podjęcia decyzji czy określone dane warto w ogóle analizować, jeśli wszystko jest ok to zawsze warto skupić się na innych parametrach.

Tworzenie KPI w Power Pivot dla stanów magazynowych – KPI zależne od innej miary

Stany magazynowe nie są wartościami jak marża, im większa tym lepiej. Tu zarówno zbyt mały stan jak i zbyt duży nie jest sytuacją dobrą. Albo mamy ryzyko, że zabraknie nam towaru albo nadmierne koszty magazynowania, ryzyko przeterminowania itd. Ponadto oczekiwany stan magazynowy może być różny w różnych miesiącach, szczególnie gdy w tym obszarze występuje duża sezonowość.

KPI tworzymy na mierze Zapasy stan. W drugim polu w oknie tworzenia KPI wybieramy miarę Zapasy plan, to do wartości tej miary będziemy porównywać nasz stan magazynowy. W trzecim obszarze okna wybieramy układ z odchyleniami po obu stronach. Jako że niedobór zapasów jest gorszy niż jego nadmiar możemy ustawić takie przedziały 90%, 95%, 110% i 115%.

Analogicznie jak dla marży możemy zaprezentować ikony KPI w tabeli przestawnej. Warto dodatkowo zmienić tu nazwę pola z Zapasy stan Stan na np. KPI zapasy.

Edycja KPI w Power Pivot

Edytować KPI możemy na dwa sposoby. Pierwszy bezpośrednio w obszarze obliczeń klikając prawym przyciskiem myszy. Drugi w Excelu klikając wspomniane polecenie Zarządzaj wskaźnikami KPI.

Przy drugim sposobie pojawia się okno z listą wszystkich utworzonych KPI.

Usuwanie KPI

Sposoby usuwania KPI są takie same jak w przypadku ich edycji. Możemy w obszarze obliczeń kliknąć prawym przyciskiem myszy na daną miarę i wybrać opcję Usuń wskaźnik KPI lub usunąć miarę w oknie zarządzania wskaźnikami KPI.

Opisy wskaźników KPI

Pozostało nam jeszcze jedno zagadnienie, o którym wspominałem. W oknie tworzenia KPI mamy przycisk Opisy. Po jego kliknięciu pojawia się nam okno, w którym możemy wprowadzić opis wskaźnika KPI, opis wyliczanych wartości, opis prezentowanych stanów oraz celu jaki został ustawiony. Dla nas jako twórców, jest to ważne, aby po jakimś czasie szybko określić co, jak i dlaczego liczymy i prezentujemy.

Ciekawostka

Wszyscy zazwyczaj posługujemy się określeniem wskaźnik KPI. Z punktu widzenia poprawności językowej jest to błędne, bo to tak jakbyśmy powiedzieli „wskaźnik kluczowego wskaźnika wydajności”. Osoby fanatycznie dbające o poprawną polszczyznę mogą się do tego przyczepić, ale nie ma co się tym przejmować. Pojęcie wskaźnik KPI w języku polskim jest tak bardzo powszechne, że nawet osoby z Microsoftu przygotowujące polską wersję Excela wszędzie posługują się takim właśnie określeniem. Tak więc KPI i wskaźnik KPI możemy uznać za formę poprawną ?

Podsumowanie

Wskaźniki KPI w Power Pivot to proste i wygodne narzędzie. Tym właśnie powinny być wskaźniki KPI. Główne założenie łatwej czytelności wyników jest tu jak najbardziej spełnione. Stosowanie Power Pivota jak zwykle upraszcza wiele pracy. Nie musimy na każdej tabeli ustawiać osobno formatowania, kombinować jak pokazać odpowiednie ikonki, a przede wszystkim możemy użyć dowolnych formuł, aby wyliczyć odpowiednie miary. Wszystko bez jakichkolwiek obliczeń w samym arkuszu Excela. Miary działają dla wolnej agregacji w tabeli przestawnej, elastycznie dostosują się do tabeli i pamiętajmy, że tak samo łatwo zastosujemy je w dużych modelach danych z wieloma tabelami i obliczeniami. Taka forma KPI to idealne narzędzie do dashboardów, minimum miejsca, maksimum czytelności.

Pobierz plik z prezentowanymi przykładami