Modelowanie danych w języku DAX - krótki przewodnik

Decydenci we wszystkich organizacjach zidentyfikowali potrzebę analizy danych historycznych ich organizacji w szczególności i ogólnie branży. Z dnia na dzień staje się to kluczowe w dzisiejszym konkurencyjnym świecie, aby sprostać stale zmieniającym się wyzwaniom biznesowym.

Big Data i Business Intelligence stały się modnymi hasłami w świecie biznesu. Źródła danych stały się ogromne, a formaty danych stały się różnorodne. Potrzebą godziny jest posiadanie prostych w użyciu narzędzi do obsługi nieustannie napływających ogromnych danych w krótszym czasie, aby uzyskać wgląd i podejmować odpowiednie decyzje we właściwym czasie.

Analitycy danych nie mogą już czekać na przetworzenie wymaganych danych przez dział IT. Wymagają poręcznego narzędzia, które pozwoli im szybko zrozumieć wymagane dane i udostępnić je w formacie, który pomoże decydentom podjąć wymagane działania we właściwym czasie.

Program Microsoft Excel zawiera potężne narzędzie o nazwie Power Pivot, które było dostępne jako dodatek we wcześniejszych wersjach programu Excel i jest wbudowaną funkcją programu Excel 2016. Baza danych dodatku Power Pivot, nazywana modelem danych i działającym językiem formuł na modelu danych o nazwie DAX (Data Analysis Expressions) umożliwia użytkownikowi programu Excel wykonywanie zadań, takich jak modelowanie i analiza danych w mgnieniu oka.

W tym samouczku nauczysz się modelowania i analizy danych przy użyciu języka DAX w oparciu o model danych dodatku Power Pivot. Na ilustracjach w tym samouczku użyto przykładowej bazy danych zysków i analiz.

Koncepcje modelowania i analizy danych

Dane, które uzyskujesz z różnych źródeł, zwane danymi surowymi, wymagają przetworzenia, zanim będziesz mógł je wykorzystać do celów analitycznych. Dowiesz się o tym w rozdziale - Pojęcia dotyczące modelowania i analizy danych.

Modelowanie i analiza danych za pomocą dodatku Excel Power Pivot

Ponieważ narzędziem, które opanujesz w tym samouczku, jest dodatek Power Pivot dla programu Excel, musisz wiedzieć, jak etapy modelowania i analizy danych są wykonywane w dodatku Power Pivot. Dowiesz się ich na szerszym poziomie w rozdziale - Modelowanie i analiza danych za pomocą dodatku Excel Power Pivot.

W trakcie wykonywania kolejnych rozdziałów poznasz różne aspekty funkcji Power Pivot, DAX i DAX w modelowaniu i analizie danych.

Pod koniec samouczka będziesz w stanie przeprowadzić modelowanie i analizę danych w języku DAX w dowolnym dostępnym kontekście.

Business Intelligence (BI) zyskuje na znaczeniu w kilku dziedzinach i organizacjach. Podejmowanie decyzji i prognozowanie oparte na danych historycznych stały się kluczowe w stale rosnącym konkurencyjnym świecie. Istnieje ogromna ilość danych dostępnych zarówno wewnętrznie, jak i zewnętrznie ze zróżnicowanych źródeł do dowolnego rodzaju analizy danych.

Jednak wyzwaniem jest wyodrębnienie odpowiednich danych z dostępnych dużych zbiorów danych, zgodnie z aktualnymi wymaganiami, i przechowywanie ich w sposób przyjazny dla projekcji różnych spostrzeżeń z danych. Uzyskany w ten sposób model danych z wykorzystaniem kluczowych terminów biznesowych jest cennym narzędziem komunikacji. Model danych musi również zapewniać szybki sposób generowania raportów w razie potrzeby.

Modelowanie danych dla systemów BI pozwala sprostać wielu wyzwaniom związanym z danymi.

Wymagania wstępne dotyczące modelu danych dla BI

Model danych dla BI powinien spełniać wymagania biznesu, dla którego przeprowadzana jest analiza danych. Poniżej przedstawiono minimalne podstawy, które musi spełniać każdy model danych -

Model danych musi być specyficzny dla firmy

Model danych odpowiedni dla jednej linii biznesowej może nie być odpowiedni dla innej branży. W związku z tym model danych musi zostać opracowany w oparciu o konkretną firmę, stosowane terminy biznesowe, typy danych i ich relacje. Powinien być oparty na celach i rodzaju decyzji podejmowanych w organizacji.

Model danych musi mieć wbudowaną inteligencję

Model danych powinien zawierać wbudowaną inteligencję poprzez metadane, hierarchie i dziedziczenie, które ułatwiają sprawny i efektywny proces Business Intelligence. Dzięki temu będziesz w stanie zapewnić wspólną platformę dla różnych użytkowników, eliminując powtarzanie procesu.

Model danych musi być solidny

Model danych powinien precyzyjnie przedstawiać dane specyficzne dla biznesu. Powinien umożliwiać efektywne przechowywanie dysku i pamięci, aby ułatwić szybkie przetwarzanie i raportowanie.

Model danych musi być skalowalny

Model danych powinien być w stanie dostosować się do zmieniających się scenariuszy biznesowych w szybki i efektywny sposób. Może być konieczne uwzględnienie nowych danych lub nowych typów danych. Może zajść konieczność efektywnego odświeżenia danych.

Modelowanie danych dla BI

Modelowanie danych dla BI składa się z następujących kroków -

• Kształtowanie danych
• Ładowanie danych
• Definiowanie relacji między tabelami
• Definiowanie typów danych
• Tworzenie nowych statystyk dotyczących danych

Kształtowanie danych

Dane wymagane do zbudowania modelu danych mogą pochodzić z różnych źródeł i mieć różne formaty. Musisz określić, która część danych z każdego z tych źródeł danych jest wymagana do określonej analizy danych. Nazywa się to kształtowaniem danych.

Na przykład, jeśli pobierasz dane wszystkich pracowników w organizacji, musisz zdecydować, które szczegóły każdego pracownika są istotne w bieżącym kontekście. Innymi słowy, musisz określić, które kolumny tabeli pracowników mają zostać zaimportowane. Dzieje się tak, ponieważ im mniejsza liczba kolumn w tabeli w modelu danych, tym szybsze będą obliczenia w tabeli.

Ładowanie danych

Musisz załadować zidentyfikowane dane - tabele danych z wybranymi kolumnami w każdej z tabel.

Definiowanie relacji między tabelami

Następnie należy zdefiniować logiczne relacje między różnymi tabelami, które ułatwiają łączenie danych z tych tabel, tj. Jeśli masz tabelę - Produkty - zawierającą dane o produktach i tabelę - Sprzedaż - z różnymi transakcjami sprzedaży produktów, definiując relację między dwiema tabelami, można podsumować sprzedaż pod względem produktu.

Definiowanie typów danych

Identyfikacja odpowiednich typów danych dla danych w modelu danych ma kluczowe znaczenie dla dokładności obliczeń. Dla każdej kolumny w każdej importowanej tabeli musisz zdefiniować typ danych. Na przykład typ danych tekstowych, typ danych liczb rzeczywistych, typ danych całkowitoliczbowych itp.

Tworzenie nowych statystyk danych

Jest to kluczowy krok w modelowaniu dat dla BI. Utworzony model danych może wymagać udostępnienia kilku osobom, które muszą zrozumieć trendy danych i podjąć wymagane decyzje w bardzo krótkim czasie. W związku z tym tworzenie nowych spostrzeżeń na podstawie danych źródłowych będzie efektywne, co pozwoli uniknąć przeróbek związanych z analizą.

Nowe spostrzeżenia dotyczące danych mogą mieć postać metadanych, które mogą być łatwo zrozumiałe i używane przez określonych ludzi biznesu.

Analiza danych

Gdy model danych jest gotowy, dane można przeanalizować zgodnie z wymaganiami. Przedstawienie wyników analizy jest również ważnym krokiem, ponieważ decyzje będą podejmowane na podstawie raportów.

Microsoft Excel Power Pivot to doskonałe narzędzie do modelowania i analizy danych.

• Model danych to baza danych Power Pivot.

• DAX to język formuł, którego można używać do tworzenia metadanych z danymi w modelu danych za pomocą formuł języka DAX.

• Tabele Power Pivot w programie Excel utworzone na podstawie danych i metadanych w modelu danych umożliwiają analizowanie danych i prezentowanie wyników.

W tym samouczku nauczysz się modelowania danych za pomocą modelu danych dodatku Power Pivot oraz języka DAX i analizy danych za pomocą dodatku Power Pivot. Jeśli jesteś nowym użytkownikiem dodatku Power Pivot, zapoznaj się z samouczkiem dotyczącym dodatku Power Pivot programu Excel.

Poznałeś etapy procesu modelowania danych z poprzedniego rozdziału - Pojęcia dotyczące modelowania i analizy danych. W tym rozdziale dowiesz się, jak wykonać każdy z tych kroków za pomocą modelu danych dodatku Power Pivot i języka DAX.

W poniższych sekcjach poznasz każdy z tych kroków procesu zastosowany do modelu danych dodatku Power Pivot oraz sposób korzystania z języka DAX.

Kształtowanie danych

W dodatku Power Pivot programu Excel można importować dane z różnych typów źródeł danych, a podczas importowania można wyświetlać i wybierać tabele i kolumny, które mają zostać zaimportowane.

• Zidentyfikuj źródła danych.

• Znajdź typy źródeł danych. Na przykład baza danych lub usługa danych lub dowolne inne źródło danych.

• Zdecyduj, jakie dane są istotne w bieżącym kontekście.

• Wybierz odpowiednie typy danych. W modelu danych dodatku Power Pivot można mieć tylko jeden typ danych dla całej kolumny w tabeli.

• Określ, które z tabel są tabelami faktów, a które tabelami wymiarów.

• Zdecyduj się na odpowiednie relacje logiczne między tabelami.

Ładowanie danych do modelu danych

Możesz ładować dane do modelu danych za pomocą kilku opcji dostępnych w oknie Power Pivot na Wstążce. Te opcje można znaleźć w grupie Pobierz dane zewnętrzne.

Dowiesz się, jak ładować dane z bazy danych Access do modelu danych w rozdziale - Ładowanie danych do modelu danych.

W celach ilustracyjnych używana jest baza danych programu Access z danymi zysków i strat.

Definiowanie typów danych w modelu danych

Następnym krokiem procesu modelowania danych w dodatku Power Pivot jest zdefiniowanie typów danych kolumn w tabelach, które są ładowane do modelu danych.

Dowiesz się, jak definiować typy danych kolumn w tabelach w rozdziale - Definiowanie typów danych w modelu danych.

Tworzenie relacji między tabelami

Następnym krokiem procesu modelowania danych w dodatku Power Pivot jest utworzenie relacji między tabelami w modelu danych.

Dowiesz się, jak tworzyć relacje między tabelami w rozdziale - Rozszerzanie modelu danych.

Tworzenie nowych statystyk danych

W modelu danych można tworzyć metadane niezbędne do tworzenia nowych analiz danych poprzez -

• Tworzenie kolumn obliczeniowych
• Tworzenie tabeli dat
• Tworzenie miar

Następnie można analizować dane, tworząc dynamiczne tabele dodatku Power Pivot na podstawie kolumn w tabelach i miar, które są wyświetlane jako pola na liście pól tabeli przestawnej.

Dodawanie kolumn obliczeniowych

Kolumny obliczeniowe w tabeli to kolumny, które dodajesz do tabeli przy użyciu formuł języka DAX.

Dowiesz się, jak dodać kolumny obliczeniowe w tabeli w modelu danych w rozdziale - Rozszerzanie modelu danych.

Tworzenie tabeli dat

Aby używać funkcji analizy czasowej w formułach języka DAX do tworzenia metadanych, wymagana jest tabela dat. Jeśli jesteś nowym użytkownikiem tabel dat, zapoznaj się z rozdziałem - Omówienie tabel dat.

Dowiesz się, jak utworzyć tabelę dat w modelu danych w rozdziale - Rozszerzanie modelu danych.

Tworzenie miar

W tabeli danych można tworzyć różne miary, używając funkcji języka DAX i formuł języka DAX do różnych obliczeń wymaganych do analizy danych w bieżącym kontekście.

To kluczowy etap modelowania danych w języku DAX.

Dowiesz się, jak tworzyć miary do różnych celów analizy zysków i strat w kolejnych rozdziałach.

Analizowanie danych za pomocą tabel Power Pivot

Możesz tworzyć tabele Power Pivot dla każdego aspektu analizy zysków i strat. W kolejnych rozdziałach dowiesz się, jak tworzyć miary przy użyciu języka DAX, a także dowiesz się, jak analizować dane za pomocą tych miar za pomocą tabel Power PivotTables.

Do modelu danych można ładować dane z różnych typów źródeł danych. W tym celu można znaleźć różne opcje w grupie Pobieranie danych zewnętrznych na Wstążce w oknie dodatku Power Pivot.

Jak widać, można ładować dane z baz danych, usług danych lub kilku innych typów źródeł danych.

Podczas ładowania danych ze źródła danych do modelu danych zostanie nawiązane połączenie ze źródłem danych. Umożliwia to odświeżanie danych, gdy zmieniają się dane źródłowe.

Inicjowanie z nowym modelem danych

W tej sekcji dowiesz się, jak modelować dane do analizy zysków i strat. Dane do analizy znajdują się w bazie danych Microsoft Access.

Możesz zainicjować nowy model danych w następujący sposób -

• Otwórz nowy skoroszyt programu Excel
• Kliknij kartę PowerPivot na Wstążce
• Kliknij Zarządzaj w grupie Model danych

Zostanie wyświetlone okno Power Pivot. Okno będzie puste, ponieważ nie załadowałeś jeszcze żadnych danych.

Ładowanie danych z bazy danych Access do modelu danych

Aby załadować dane z bazy danych Access, wykonaj następujące kroki -

• Kliknij opcję Z bazy danych w grupie Pobierz dane zewnętrzne na Wstążce.
• Kliknij opcję Od dostępu na liście rozwijanej.

Pojawi się okno dialogowe Kreatora importu tabeli.

• Przejdź do pliku Access.

• Podaj przyjazną nazwę dla połączenia.

• Kliknij przycisk Dalej. Pojawi się kolejna część Kreatora importu tabeli.

• W Kreatorze importu tabel wybierz opcję - Wybierz z listy tabel i widoków, aby wybrać dane do zaimportowania.

• Kliknij przycisk Dalej. Następna część Kreatora importu tabeli pojawi się, jak pokazano na poniższym zrzucie ekranu.

• Wybierz wszystkie tabele.

• Nadaj tabelom przyjazne nazwy. Jest to konieczne, ponieważ te nazwy pojawiają się w tabelach przestawnych dodatku Power Pivot i dlatego powinny być zrozumiałe dla wszystkich.

Wybór kolumn w tabelach

Możesz nie potrzebować wszystkich kolumn w wybranych tabelach do bieżącej analizy. Dlatego musisz wybrać tylko te kolumny, które wybrałeś podczas kształtowania danych.

• Kliknij przycisk Podgląd i filtr. Pojawi się kolejna część Kreatora importu tabeli - Podgląd wybranej tabeli.

• Jak widać na powyższym zrzucie ekranu, nagłówki kolumn mają pola wyboru. Wybierz kolumny, które chcesz zaimportować w wybranej tabeli.

• Kliknij OK. Powtórz to samo dla innych tabel.

Importowanie danych do modelu danych

Jesteś na ostatnim etapie ładowania danych do modelu danych. Kliknij przycisk Zakończ w Kreatorze importu tabeli. Pojawi się kolejna część Kreatora importu tabeli.

Zostanie wyświetlony stan importu. Status ostatecznie wyświetla sukces po zakończeniu ładowania danych.

Przeglądanie danych w modelu danych

Zaimportowane tabele pojawią się w oknie Power Pivot. To jest widok modelu danych

Możesz zaobserwować następujące -

• Każda z tabel pojawia się na osobnej karcie.
• Nazwy kart to nazwy odpowiednich tabel.
• Obszar poniżej danych służy do obliczeń.

Przeglądanie nazwy połączenia

Kliknij Istniejące połączenia w grupie Pobierz dane zewnętrzne. Pojawi się okno dialogowe Istniejące połączenia, jak pokazano na poniższym zrzucie ekranu.

Jak widać na powyższym zrzucie ekranu, podana nazwa połączenia pojawia się w obszarze Połączenia danych programu PowerPivot.

W modelu danych dodatku Power Pivot wszystkie dane w kolumnie muszą mieć ten sam typ danych. Aby wykonać dokładne obliczenia, należy upewnić się, że typ danych każdej kolumny w każdej tabeli w modelu danych jest zgodny z wymaganiami.

Tabele w modelu danych

W modelu danych utworzonym w poprzednim rozdziale znajdują się 3 tabele -

• Accounts
• Geografia Locn
• Dane finansowe

Zapewnienie odpowiednich typów danych

Aby upewnić się, że kolumny w tabelach są zgodne z wymaganiami, musisz sprawdzić ich typy danych w oknie dodatku Power Pivot.

• Kliknij kolumnę w tabeli.

• Zwróć uwagę na typ danych kolumny wyświetlany na Wstążce w grupie Formatowanie.

Jeśli typ danych wybranej kolumny nie jest odpowiedni, zmień typ danych w następujący sposób.

• Kliknij strzałkę w dół obok typu danych w grupie Formatowanie.

• Kliknij odpowiedni typ danych na liście rozwijanej.

• Powtórz te czynności dla każdej kolumny we wszystkich tabelach w modelu danych.

Kolumny w tabeli kont

W tabeli Accounts znajdują się następujące kolumny -

Sr.No	Kolumna i opis
1	Account Zawiera jeden numer konta dla każdego wiersza. Kolumna ma unikalne wartości i służy do definiowania relacji z tabelą danych finansowych.
2	Class Klasa powiązana z każdym kontem. Przykład - wydatki, przychody netto itp.
3	Sub Class Opisuje rodzaj wydatku lub przychodu. Przykład - ludzie.

Wszystkie kolumny w tabeli Rachunki mają charakter opisowy, a zatem mają typ danych Tekst.

Kolumny w tabeli Geography Locn

Tabela Geography Locn zawiera dane o każdym centrum zysków.

Kolumna Centrum zysku zawiera jedną tożsamość centrum zysku dla każdego wiersza. Ta kolumna ma unikalne wartości i jest używana do definiowania relacji z tabelą danych finansowych.

Kolumny w tabeli danych finansowych

W tabeli danych finansowych znajdują się następujące kolumny -

Kolumna	Opis	Typ danych
Miesiąc podatkowy	Miesiąc i rok	Tekst
Źródło zysku	Tożsamość centrum zysków	Tekst
Konto	Numer konta. Każde konto może mieć wiele centrów zysków.	Tekst
Budżet	Miesięczne kwoty budżetu dla każdego centrum zysków.	Waluta
Rzeczywisty	Miesięczne rzeczywiste kwoty dla każdego centrum zysków.	Waluta
Prognoza	Miesięczne prognozy kwot dla każdego centrum zysku.	Waluta
Prawdziwi ludzie	Rzeczywista liczba pracowników na koniec miesiąca dla każdego centrum zysków każdego konta osób.	Cały numer
Budżet Ludzie	Miesięczna liczba pracowników na koniec budżetu dla każdego centrum zysków dla każdego konta osób.	Cały numer
Prognozuj ludzi	Prognozowana liczba pracowników na koniec miesiąca dla każdego centrum zysków dla każdego konta osób.	Cały numer

Typy tabel w modelu danych

Zarówno tabele Accounts, jak i Geography Locn to tabele wymiarowe, zwane także lookup tables.

Finance Data tableto tabela faktów, nazywana także tabelą danych. Tabela danych finansowych zawiera dane wymagane do obliczeń zysków i analizy. Utworzysz również metadane w postaci miar i kolumn obliczeniowych w tej tabeli danych finansowych, aby modelować dane dla różnych typów obliczeń zysków i strat, postępując zgodnie z tym samouczkiem.

Analiza danych obejmuje przeglądanie danych w czasie i wykonywanie obliczeń w różnych okresach. Na przykład może być konieczne porównanie zysków z bieżącego roku z zyskami z poprzedniego roku. Podobnie, być może będziesz musiał prognozować wzrost i zyski w nadchodzących latach. W tym celu musisz użyć grupowania i agregacji w pewnym okresie.

Język DAX udostępnia kilka funkcji analizy czasowej, które ułatwiają wykonywanie większości takich obliczeń. Jednak te funkcje języka DAX wymagają tabeli dat do użycia z innymi tabelami w modelu danych.

Możesz zaimportować tabelę dat wraz z innymi danymi ze źródła danych lub możesz samodzielnie utworzyć tabelę dat w modelu danych.

W tym rozdziale poznasz różne aspekty tabel dat. Jeśli znasz tabele dat w modelu danych dodatku Power Pivot, możesz pominąć ten rozdział i przejść do kolejnych rozdziałów. W przeciwnym razie możesz zrozumieć tabele dat w modelu danych dodatku Power Pivot.

Co to jest tabela dat?

Tabela dat to tabela w modelu danych z co najmniej jedną kolumną z ciągłymi datami w wymaganym czasie. Może mieć dodatkowe kolumny reprezentujące różne okresy. Jednak niezbędna jest kolumna z ciągłymi datami, zgodnie z wymaganiami funkcji analizy czasu języka DAX.

Na przykład,

• Tabela dat może zawierać kolumny, takie jak data, miesiąc obrachunkowy, kwartał obrachunkowy i rok obrachunkowy.

• Tabela dat może zawierać kolumny, takie jak data, miesiąc, kwartał i rok.

Tabela dat z sąsiadującymi datami

Załóżmy, że musisz wykonać obliczenia w zakresie roku kalendarzowego. Następnie tabela Date musi mieć co najmniej jedną kolumnę z ciągłym zestawem dat, obejmującym wszystkie daty w tym konkretnym roku kalendarzowym.

Na przykład, załóżmy, że dane chcesz przeglądać ma termin od 1 kwietnia 2014 do 30 listopada ^th , 2016.

• Jeśli musisz zgłosić się na rok kalendarzowy, trzeba tabelę daty z kolumny - data, która zawiera wszystkie daty od 1 stycznia ^st 2014 do grudnia 31 ^st , 2016 w sekwencji.

• Jeśli masz do raportu w roku obrotowym, a koniec roku obrotowego jest 30 ^th czerwca, trzeba tabelę daty z kolumny - data, która zawiera wszystkie daty od 1 lipca ^st 2013 do czerwca 30 ^th , 2017 w sposób sekwencja.

• Jeśli musisz raportować zarówno lata kalendarzowe, jak i obrotowe, możesz mieć jedną tabelę dat obejmującą wymagany zakres dat.

Twoja tabela dat musi zawierać wszystkie dni z zakresu każdego roku w podanym okresie. W ten sposób otrzymasz ciągłe daty w tym okresie.

Jeśli regularnie odświeżasz swoje dane nowymi danymi, data zakończenia zostanie wydłużona o rok lub dwa, dzięki czemu nie będziesz musiał często aktualizować tabeli dat.

Tabela dat wygląda jak na poniższym zrzucie ekranu.

Dodawanie tabeli dat do modelu danych

Możesz dodać tabelę dat do modelu danych w jeden z następujących sposobów -

• Importowanie z relacyjnej bazy danych lub dowolnego innego źródła danych.

• Tworzenie tabeli dat w programie Excel, a następnie kopiowanie lub tworzenie łączy do nowej tabeli w dodatku Power Pivot.

• Importowanie z witryny Microsoft Azure Marketplace.

Tworzenie tabeli dat w programie Excel i kopiowanie do modelu danych

Tworzenie tabeli dat w programie Excel i kopiowanie do modelu danych to najłatwiejszy i najbardziej elastyczny sposób tworzenia tabeli danych w modelu danych.

• Otwórz nowy arkusz w programie Excel.

• Typ - data w pierwszym wierszu kolumny.

• Wpisz pierwszą datę z zakresu dat, który chcesz utworzyć, w drugim wierszu tej samej kolumny.

• Wybierz komórkę, kliknij uchwyt wypełniania i przeciągnij ją w dół, aby utworzyć kolumnę z ciągłymi datami w wymaganym zakresie dat.

Na przykład wpisz 01.01.2014, kliknij uchwyt wypełniania i przeciągnij w dół, aby wypełnić sąsiadujące daty do 31.12.2016.

• Kliknij kolumnę Data.
• Kliknij kartę WSTAW na Wstążce.
• Kliknij opcję Tabela.
• Sprawdź zakres tabeli.
• Kliknij OK.

Tabela z pojedynczą kolumną dat jest gotowa w Excelu.

• Wybierz tabelę.
• Kliknij Kopiuj na Wstążce.
• Kliknij okno Power Pivot.
• Kliknij Wklej na Wstążce.

Spowoduje to dodanie zawartości schowka do nowej tabeli w modelu danych. W związku z tym można użyć tej samej metody do utworzenia tabeli dat w istniejącym modelu danych.

Pojawi się okno dialogowe podglądu wklejania, jak pokazano na poniższym zrzucie ekranu.

• Wpisz Data w polu Nazwa tabeli.
• Wyświetl podgląd danych.

• Zaznacz pole - Użyj pierwszego wiersza jako nagłówków kolumn.
• Kliknij OK.

Spowoduje to skopiowanie zawartości schowka do nowej tabeli w modelu danych.

Teraz masz tabelę dat w modelu danych z jedną kolumną ciągłych dat. Nagłówek kolumny to Data, tak jak podano w tabeli programu Excel.

Dodawanie nowych kolumn dat do tabeli dat

Następnie możesz dodać kolumny obliczeniowe do tabeli dat zgodnie z wymaganiami obliczeń.

Na przykład możesz dodać kolumny - Dzień, Miesiąc, Rok i Kwartał w następujący sposób -

• Day

  =DAY('Date'[Date])

• Month

  =MONTH('Date'[Date])

• Year

  =YEAR('Date'[Date])

• Jedna czwarta

  = CONCATENATE ("QTR"; INT (('Data' [miesiąc] +2) / 3))

Wynikowa tabela dat w modelu danych wygląda jak na poniższym zrzucie ekranu.

W ten sposób można dodać dowolną liczbę kolumn obliczeniowych do tabeli Date. Ważne i wymagane jest to, że tabela Date musi mieć kolumnę z ciągłymi datami, obejmującą czas, w którym wykonujesz obliczenia.

Tworzenie tabeli dat dla roku kalendarzowego

Rok kalendarzowy zazwyczaj obejmuje daty od 1 stycznia do 31 grudnia danego roku, a także święta zaznaczone w tym konkretnym roku. Podczas wykonywania obliczeń może być konieczne uwzględnienie tylko dni roboczych, z wyłączeniem weekendów i świąt.

Załóżmy, że chcesz utworzyć tabelę dat na rok kalendarzowy 2017.

• Utwórz tabelę programu Excel z kolumny Data, składająca się z przylegających do siebie terminach od 1 ^st stycznia 2017 roku do 31 ^st grudnia 2017. (Patrz poprzedniej sekcji, aby wiedzieć, jak to zrobić).

• Skopiuj tabelę programu Excel i wklej ją do nowej tabeli w modelu danych. (Zapoznaj się z poprzednią sekcją, aby dowiedzieć się, jak to zrobić).

• Nazwij tabelę Kalendarz.

• Dodaj następujące kolumny obliczeniowe -

  • Dzień = DZIEŃ („Kalendarz” [data])

  • Miesiąc = MIESIĄC („Kalendarz” [data])

  • Rok = ROK ('Kalendarz' [data])

  • Dzień tygodnia = FORMAT („Kalendarz” [data], „DDD”)

  • Nazwa miesiąca = FORMAT („Kalendarz” [data]; „MMM”)

Dodawanie dni wolnych do tabeli kalendarza

Dodaj święta do tabeli kalendarza w następujący sposób -

• Uzyskaj listę zadeklarowanych świąt w roku.

• Na przykład w Stanach Zjednoczonych możesz uzyskać listę wakacji na dowolny wymagany rok, klikając poniższy link http://www.calendar-365.com/.

• Skopiuj i wklej je do arkusza programu Excel.

• Skopiuj tabelę programu Excel i wklej ją do nowej tabeli w modelu danych.

• Nazwij tabelę Święta.

• Następnie możesz dodać obliczoną kolumnę dni wolnych do tabeli kalendarza przy użyciu funkcji DAX LOOKUPVALUE.

=LOOKUPVALUE(Holidays[Holiday],Holidays[Date],'Calendar'[Date])

Funkcja DAX LOOKUPVALUE wyszukuje trzeci parametr, tj. Kalendarz [Data] w drugim parametrze, tj. Dni wolne [Data] i zwraca pierwszy parametr, tj. Dni wolne [Święta], jeśli występuje dopasowanie. Wynik będzie wyglądał tak, jak pokazano na poniższym zrzucie ekranu.

Dodawanie kolumn do roku obrotowego

Rok fiskalny zawiera zazwyczaj daty od 1 ^st miesiąca po zakończeniu roku podatkowego do końca następnego roku obrotowego. Na przykład, jeśli do końca roku obrotowego wynosi 31 ^st marca, a następnie rok obrotowy w zakresie od 1 ^st kwietnia do 31 ^st marcu.

Okresy obrachunkowe można uwzględnić w tabeli kalendarza za pomocą formuł języka DAX -

• Dodaj miarę dla FYE

  FYE:=3

• Dodaj następujące kolumny obliczeniowe -

  • Fiscal Year

    = JEŻELI ('Kalendarz' [Miesiąc] <= 'Kalendarz' [FYE], 'Kalendarz' [Rok], 'Kalendarz' [Rok] +1)

  • Fiscal Month

    = IF („Kalendarz” [miesiąc] <= „Kalendarz” [FYE], 12-„Kalendarz” [FYE] + „Kalendarz” [miesiąc], „Kalendarz” [miesiąc] - „Kalendarz” [FYE])

  • Fiscal Quarter

    = INT (('Kalendarz' [Miesiąc Obrachunkowy] +2) / 3)

Ustawianie właściwości tabeli dat

W przypadku korzystania z funkcji analizy czasowej języka DAX, takich jak TOTALYTD, PREVIOUSMONTH i DATESBETWEEN, do poprawnego działania wymagają one metadanych. Właściwość tabeli dat ustawia takie metadane.

Aby ustawić właściwość Tabela dat -

• Wybierz tabelę kalendarza w oknie dodatku Power Pivot.
• Kliknij kartę Projekt na Wstążce.
• Kliknij opcję Oznacz jako tabelę dat w grupie Kalendarze.
• Kliknij opcję Oznacz jako tabelę dat na liście rozwijanej.

Zostanie wyświetlone okno dialogowe Oznacz jako tabelę dat. Wybierz kolumnę Data w tabeli kalendarza. Musi to być kolumna typu danych Date i musi mieć unikalne wartości. Kliknij OK.

W tym rozdziale dowiesz się, jak rozszerzyć model danych utworzony w poprzednich rozdziałach. Rozszerzenie modelu danych obejmuje -

• Dodanie tabel
• Dodanie kolumn obliczeniowych w istniejącej tabeli
• Tworzenie miar w istniejącej tabeli

Spośród nich tworzenie miar jest kluczowe, ponieważ obejmuje dostarczanie nowych spostrzeżeń dotyczących danych w modelu danych, które pozwolą osobom korzystającym z modelu danych uniknąć przeróbek, a także zaoszczędzić czas podczas analizowania danych i podejmowania decyzji.

Ponieważ Analiza zysków i strat obejmuje pracę z przedziałami czasowymi i będziesz korzystać z funkcji analizy czasowej języka DAX, w modelu danych wymagana jest tabela dat.

Jeśli jesteś nowym użytkownikiem tabel dat, zapoznaj się z rozdziałem - Omówienie tabel dat.

Możesz rozszerzyć model danych w następujący sposób -

• Aby utworzyć relację między tabelą danych, tj. Tabelą danych finansowych i tabelą dat, musisz utworzyć kolumnę obliczeniową Data w tabeli Dane finansowe.

• Aby wykonać różne rodzaje obliczeń, należy utworzyć relacje między tabelą danych - Dane finansowe a tabelami przeglądowymi - Konta i Lokalizacje geograficzne.

• Musisz stworzyć różne miary, które pomogą Ci wykonać kilka obliczeń i przeprowadzić wymaganą analizę.

Te kroki zasadniczo stanowią etapy modelowania danych dla analizy zysków i strat przy użyciu modelu danych. Jest to jednak sekwencja kroków dla dowolnego typu analizy danych, które chcesz wykonać za pomocą modelu danych dodatku Power Pivot.

Ponadto dowiesz się, jak tworzyć miary i jak ich używać w tabelach Power Pivot w kolejnych rozdziałach. Zapewni to wystarczające zrozumienie modelowania danych za pomocą języka DAX i analizy danych za pomocą tabel Power Pivot.

Dodawanie tabeli dat do modelu danych

Utwórz tabelę dat dla okresów obejmujących lata obrachunkowe w następujący sposób -

• Utwórz tabelę z pojedynczą kolumną z nagłówkiem - data i ciągłe daty od 2011-07-01 do 30.06.2018 w nowym arkuszu programu Excel.

• Skopiuj tabelę z programu Excel i wklej ją do okna dodatku Power Pivot. Spowoduje to utworzenie nowej tabeli w modelu danych dodatku Power Pivot.

• Nazwij tabelę jako Data.

• Upewnij się, że kolumna Data w tabeli Data ma typ danych - Data (Data i godzina).

Następnie należy dodać kolumny obliczeniowe - rok obrachunkowy, kwartał obrachunkowy, miesiąc i miesiąc obrachunkowy do tabeli dat w następujący sposób -

Rok podatkowy

Załóżmy, że na koniec roku obrotowego jest 30 czerwca ^th . Następnie fiskalne przęsła roku od 1 ^st lipca do 30 ^-go czerwca. Na przykład, termin 01 lipca ^st 2011 (1.07.2011) do dnia 30 czerwca ^th , 2012 (30.06.2012) będzie rok obrotowy 2012.

Załóżmy, że w tabeli Data chcesz przedstawić to samo, co rok obrotowy 2012.

• Najpierw należy wyodrębnić część daty z roku budżetowego i dołączyć ją do FY.

  • Dla dat w miesiącach lipiec 2011 - grudzień 2011 rok obrotowy wynosi 1 + 2011.

  • Dla dat w miesiącach od stycznia 2012 r. Do czerwca 2012 r. Rok obrotowy wynosi 0 + 2012.

  • Aby uogólnić, jeśli miesiąc końca roku finansowego to rok finansowy, wykonaj następujące czynności -

    Integer Part of ((Month – 1)/FYE) + Year

  • Następnie weź 4 znaki z prawej strony, aby uzyskać Rok finansowy.

• W języku DAX możesz przedstawić to samo, co -

  PRAWO (INT ((MIESIĄC ('Data' [Data]) - 1) / 'Data' [FYE]) + ROK ('Data' [Data]), 4)

• Dodaj kolumnę obliczeniową Rok obrachunkowy w tabeli dat z formułą języka DAX -

  = "FY" & RIGHT (INT ((MONTH ('Date' [Date]) - 1) / 'Date' [FYE]) + YEAR ('Date' [Date]), 4)

Kwartał fiskalny

Jeśli FYE oznacza miesiąc końca roku finansowego, kwartał finansowy otrzymuje się jako

Integer Part of ((Remainder of ((Month+FYE-1)/12) + 3)/3)

• W języku DAX możesz przedstawić to samo, co -

  INT ((MOD (MIESIĄC ('Data' [Data]) + 'Data' [FYE] -1,12) +3) / 3)

• Dodaj obliczoną kolumnę Kwartał obrachunkowy w tabeli dat z formułą języka DAX -

  = „Data” [Rok obrachunkowy] & „- Q” & FORMAT ((MOD (MIESIĄC („Data” [Data])) + „Data” [FYE] -1,12) + 3) / 3), „0” )

Miesiąc podatkowy

Jeśli FYE oznacza koniec roku finansowego, okres miesiąca obrotowego otrzymuje się jako

(Remainder of (Month+FYE-1)/12) + 1

• W języku DAX możesz przedstawić to samo, co -

  MOD (MIESIĄC („Data” [Data]) + „Data” [FYE] -1,12) +1

• Dodaj kolumnę obliczeniową Miesiąc obrachunkowy w tabeli Data z formułą języka DAX -

  = „Data” [Rok obrachunkowy] & „- P” & FORMAT (MOD (MIESIĄC ([Data]) + [FYE] -1,12) +1, „00”)

Miesiąc

Na koniec dodaj obliczoną kolumnę Miesiąc, która reprezentuje numer miesiąca w roku finansowym w następujący sposób -

= FORMAT (MOD (MONTH ([Date]) + [FYE] -1,12) +1, „00”) & „-” & FORMAT ([Date], „mmm”)

Wynikowa tabela dat wygląda jak na poniższym zrzucie ekranu.

Zaznacz tabelę - Data jako Tabela dat z kolumną - Data jako kolumnę z unikalnymi wartościami, jak pokazano na poniższym zrzucie ekranu.

Dodawanie kolumn obliczeniowych

Aby utworzyć relację między tabelą danych finansowych a tabelą dat, w tabeli danych finansowych wymagana jest kolumna wartości dat.

• Dodaj kolumnę obliczeniową Data w tabeli Dane finansowe za pomocą formuły języka DAX -

  = DATEVALUE („Dane finansowe” [miesiąc obrachunkowy])

Definiowanie relacji między tabelami w modelu danych

W modelu danych masz następujące tabele -

• Tabela danych - dane finansowe
• Tabele wyszukiwania - konta i geografia Locn
• Tabela dat - data

Aby zdefiniować relacje między tabelami w modelu danych, wykonaj następujące czynności -

• Wyświetl tabele w widoku diagramu dodatku Power Pivot.

• Utwórz następujące relacje między tabelami -

  • Relacja między tabelą danych finansowych a tabelą Konta z kolumną Konto.

  • Związek między tabelą danych finansowych i tabelą lokalizacji geograficznej z kolumną Centrum zysków.

  • Relacja między tabelą danych finansowych a tabelą dat z kolumną Data.

Ukrywanie kolumn w narzędziach klienta

Jeśli w tabeli danych znajdują się kolumny, których nie będziesz używać jako pól w żadnej tabeli przestawnej, możesz je ukryć w modelu danych. Wówczas nie będą widoczne na liście pól tabeli przestawnej.

W tabeli danych finansowych masz 4 kolumny - miesiąc obrachunkowy, datę, konto i centrum zysków, których nie będziesz używać jako pól w żadnej tabeli przestawnej. Dlatego możesz je ukryć, aby nie pojawiały się na liście pól tabeli przestawnej.

• Wybierz kolumny - Miesiąc obrachunkowy, Data, Konto i Centrum zysków w tabeli Dane finansowe.

• Kliknij prawym przyciskiem myszy i wybierz z listy rozwijanej Ukryj w narzędziach klienta.

Tworzenie miar w tabelach

Wszystko jest gotowe do modelowania i analizy danych w języku DAX przy użyciu modelu danych i tabel Power Pivot.

W kolejnych rozdziałach dowiesz się, jak tworzyć miary i jak ich używać w tabelach Power Pivot. Utworzysz wszystkie miary w tabeli danych, tj. Tabeli danych finansowych.

Utworzysz miary za pomocą formuł języka DAX w tabeli danych - Dane finansowe, których możesz używać w dowolnej liczbie tabel przestawnych do analizy danych. Miary to zasadniczo metadane. Tworzenie miar w tabeli danych jest częścią modelowania danych, a podsumowywanie ich w tabelach Power Pivot jest częścią analizy danych.

W modelu danych można tworzyć różne miary, które będą używane w dowolnej liczbie tabel dodatku Power Pivot. Tworzy to proces modelowania i analizy danych z modelem danych używającym języka DAX.

Jak dowiedziałeś się wcześniej w poprzednich sekcjach, modelowanie i analiza danych zależy od konkretnego biznesu i kontekstu. W tym rozdziale nauczysz się modelowania i analizy danych w oparciu o przykładową bazę danych zysków i strat, aby zrozumieć, jak tworzyć wymagane miary i używać ich w różnych tabelach Power Pivot.

Tę samą metodę można zastosować do modelowania i analizy danych dla dowolnej firmy i kontekstu

Tworzenie miar na podstawie danych finansowych

Aby utworzyć dowolny raport finansowy, należy wykonać obliczenia kwot dla określonego okresu, organizacji, konta lub lokalizacji geograficznej. Musisz również wykonać obliczenia dotyczące zatrudnienia i kosztu zatrudnienia. W modelu danych można tworzyć miary podstawowe, które można ponownie wykorzystać przy tworzeniu innych miar. Jest to skuteczny sposób modelowania danych w języku DAX.

Aby wykonać obliczenia w celu analizy danych dotyczących zysków i strat, możesz tworzyć miary, takie jak suma, rok do roku, rok do początku, kwartał do początku, wariancja, zatrudnienie, koszt na zatrudnienie itp. użyj tych miar w tabelach Power Pivot do analizowania danych i raportowania wyników analizy.

W kolejnych sekcjach dowiesz się, jak tworzyć podstawowe miary finansowe i analizować dane za pomocą tych miar. Miary są określane jako miary bazowe, ponieważ mogą być używane do tworzenia innych miar finansowych. Dowiesz się również, jak tworzyć miary dla poprzednich okresów i wykorzystywać je w analizie.

Tworzenie podstawowych miar finansowych

W analizie danych finansowych budżet i prognoza odgrywają główną rolę.

Budżet

Budżet to oszacowanie przychodów i wydatków firmy na rok obrotowy. Budżet obliczany jest na początku roku finansowego mając na uwadze cele i zadania firmy. Środki budżetowe muszą być od czasu do czasu analizowane w trakcie roku finansowego, ponieważ warunki rynkowe mogą się zmieniać, a firma może być zmuszona do dostosowania swoich celów i zadań do aktualnych trendów w branży.

Prognoza

Prognoza finansowa to oszacowanie przyszłych wyników finansowych firmy poprzez zbadanie historycznych danych firmy dotyczących przychodów i kosztów. Prognozowania finansowego można używać do następujących celów -

• Aby określić, jak przeznaczyć budżet na przyszły okres.

• Śledzenie oczekiwanych wyników firmy.

• Podejmowanie na czas decyzji w celu wyeliminowania niedociągnięć w realizacji celów lub zmaksymalizowania pojawiających się możliwości.

Rzeczywiste

Aby wykonać obliczenia budżetowe i prognozujące, potrzebujesz rzeczywistych przychodów i wydatków w dowolnym momencie.

Można utworzyć następujące 3 podstawowe miary finansowe, które można wykorzystać przy tworzeniu innych miar finansowych w trybie danych -

• Suma budżetowa
• Rzeczywista suma
• Suma prognozy

Miary te to sumy agregacji w kolumnach - Budżet, Rzeczywiste i Prognoza w tabeli Dane finansowe.

Utwórz podstawowe miary finansowe w następujący sposób -

Budget Sum

Suma budżetu: = SUMA („Dane finansowe” [budżet])

Actual Sum

Rzeczywista suma: = SUMA („Dane finansowe” [rzeczywiste])

Forecast Sum

Suma prognozy: = SUMA („Dane finansowe” [prognoza])

Analiza danych za pomocą podstawowych miar finansowych

Dzięki podstawowym miarom finansowym i tabeli Data można przeprowadzić analizę w następujący sposób -

• Utwórz tabelę Power Pivot.
• Dodaj pole Rok obrachunkowy z tabeli dat do wierszy.
• Dodaj miary Suma budżetu, Suma rzeczywista i Suma prognozy (które pojawiają się jako pola na liście Pola tabeli przestawnej) do Wartości.

Tworzenie miar finansowych dla poprzednich okresów

Za pomocą trzech podstawowych miar finansowych i tabeli Data można tworzyć inne miary finansowe.

Załóżmy, że chcesz porównać rzeczywistą sumę kwartału z rzeczywistą sumą poprzedniego kwartału. Można utworzyć miarę - Suma rzeczywista z poprzedniego kwartału.

Suma rzeczywista z poprzedniego kwartału: = CALCULATE ([Suma rzeczywista], DATEADD ('Data' [Data], 1, KWARTAŁ))

Podobnie można utworzyć miarę - Rzeczywista suma poprzedniego roku.

Suma rzeczywista poprzedniego roku: = CALCULATE ([Suma rzeczywista], DATEADD ('Data' [Data], 1, ROK))

Analiza danych za pomocą miar finansowych z poprzednich okresów

Za pomocą miar podstawowych, miar z poprzednich okresów i tabeli dat można przeprowadzić analizę w następujący sposób -

• Utwórz tabelę Power Pivot.
• Dodaj pole Kwartał obrachunkowy z tabeli dat do wierszy.
• Dodaj miary Rzeczywista suma i Rzeczywista suma z poprzedniego kwartału do Wartości.
• Utwórz kolejną tabelę Power Pivot.
• Dodaj pole Rok obrachunkowy z tabeli dat do wierszy.
• Dodaj miary Rzeczywista suma i Rzeczywista suma z poprzedniego roku do Wartości.

Miarą wzrostu jest rok do roku (r / r). Otrzymuje się ją odejmując rzeczywistą sumę z poprzedniego roku od rzeczywistej sumy.

Jeśli wynik jest dodatni, to odzwierciedla wzrost rzeczywistej, a jeśli jest ujemny, odzwierciedla spadek rzeczywistej, tj. Jeśli obliczymy rok do roku jako -

year-over-year = (actual sum –prior year actual sum)

• Jeśli rzeczywista suma> rzeczywista suma roku poprzedniego, rok do roku będzie dodatnia.
• Jeśli rzeczywista suma <rzeczywista suma roku poprzedniego, rok do roku będzie ujemna.

W danych finansowych konta, takie jak rachunki wydatków, będą miały kwoty debetowe (dodatnie), a konta przychodów będą miały kwoty kredytów (ujemne). Dlatego w przypadku rachunków wydatków powyższy wzór działa dobrze.

Jednak w przypadku rachunków dochodów powinno być odwrotnie, tj

• Jeżeli rzeczywista suma> rzeczywista suma roku poprzedniego, rok do roku powinna być ujemna.
• Jeżeli rzeczywista suma <rzeczywista suma roku poprzedniego, rok do roku powinna być dodatnia.

Dlatego w przypadku rachunków przychodów należy obliczyć rok do roku jako -

year-over-year = -(actual sum – prior year actual sum)

Tworzenie miary rok do roku

Miarę Rok do roku można utworzyć za pomocą następującej formuły języka DAX -

R / r: = JEŻELI (ZAWIERA (konta, rachunki [klasa], "przychody netto"), - ([suma rzeczywista] - [suma rzeczywista poprzedniego roku]), [suma rzeczywista] - [suma rzeczywista poprzedniego roku])

W powyższej formule DAX -

• Funkcja DAX CONTAINS zwraca wartość TRUE, jeśli wiersz zawiera „Net Revenue” w kolumnie Class w tabeli Accounts.

• Funkcja JEŻELI języka DAX zwraca następnie - ([Suma rzeczywista] - [Suma rzeczywista poprzedniego roku]).

• W przeciwnym razie funkcja JEŻELI języka DAX zwraca wartość [Suma rzeczywista] - [Suma rzeczywista poprzedniego roku].

Tworzenie miary procentowej rok do roku

Możesz przedstawić rok do roku jako wartość procentową ze stosunkiem -

(YoY) / (Prior Year Actual Sum)

Miarę procentu rok do roku można utworzyć za pomocą następującej formuły języka DAX -

R / R%: = JEŻELI ([Suma rzeczywista poprzedniego roku], [r / r] / ABS ([Suma rzeczywista poprzedniego roku]), PUSTE ())

Funkcja DAX IF jest używana w powyższym wzorze, aby zapewnić, że nie ma dzielenia przez zero.

Analiza danych za pomocą miar rocznych

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj pola Klasa i Podklasa z tabeli Konta do Wierszy.
• Dodaj miary - Suma rzeczywista, Suma rzeczywista z poprzedniego roku, r / r i r / r% do wartości.
• Wstaw fragmentator w polu Rok obrachunkowy z tabeli dat.
• Wybierz FY2016 we fragmentatorze.

Tworzenie miary budżetu rok do roku

Miarę budżetu rok do roku można utworzyć w następujący sposób -

Budżet r / r: = JEŻELI (ZAWIERA (konta, konta [klasa], "dochód netto"), - ([suma budżetu] - [suma rzeczywista poprzedniego roku]), [suma budżetu] - [suma rzeczywista poprzedniego roku])

Tworzenie miary procentowej budżetu rok do roku

Można utworzyć miarę procentową rocznego budżetu w następujący sposób -

Budżet r / r%: = JEŻELI ([Suma rzeczywista poprzedniego roku], [Budżet r / r] / ABS ([Suma rzeczywista poprzedniego roku]), PUSTE ())

Analiza danych za pomocą rocznych miar budżetowych

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj pola Klasa i Podklasa z tabeli Konta do Wierszy.
• Dodaj miary - Suma budżetu, Suma rzeczywista poprzedniego roku, Budżet r / r i Budżet r / r% do wartości.
• Wstaw fragmentator w polu Rok obrachunkowy z tabeli dat.
• Wybierz FY2016 we fragmentatorze.

Tworzenie prognozy rocznej miary

Miarę Prognoza rok do roku można utworzyć w następujący sposób -

Prognoza r / r: = JEŻELI (ZAWIERA (konta, rachunki [klasa], "przychód netto"), - ([suma prognozy] - [suma rzeczywista poprzedniego roku]), [suma prognozy] - [suma rzeczywista poprzedniego roku])

Tworzenie prognozy rocznej miary procentowej

Można utworzyć miarę prognozy rocznej wartości procentowej w następujący sposób -

Prognoza r / r%: = IF ([suma rzeczywista poprzedniego roku], [prognoza r / r] / ABS ([suma rzeczywista poprzedniego roku]), PUSTE ())

Analiza danych za pomocą prognoz rocznych miar

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj pola Klasa i Podklasa z tabeli Konta do Wierszy.
• Dodaj miary - prognozowaną sumę, rzeczywistą sumę poprzedniego roku, prognozę r / r i prognozę r / r% do wartości.
• Wstaw fragmentator w polu Rok obrachunkowy z tabeli danych.
• Wybierz FY2016 we fragmentatorze.

Można tworzyć miary odchylenia, takie jak odchylenie od budżetu, odchylenie od prognozy i odchylenie prognozy do budżetu. Możesz również analizować dane finansowe na podstawie tych miar.

Tworzenie odchylenia od miary sumy budżetu

Utwórz miarę odchylenia od sumy budżetu (suma VTB) w następujący sposób -

Suma VTB: = [Suma budżetu] - [Suma rzeczywista]

Tworzenie odchylenia od miary procentu budżetu

Utwórz miarę odchylenia od procentu budżetu (VTB%) w następujący sposób -

VTB%: = JEŻELI ([Suma budżetu], [Suma VTB] / ABS ([Suma budżetu]), PUSTE ())

Analiza danych z odchyleniami od miar budżetowych

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj rok obrachunkowy z tabeli dat do wierszy.
• Dodaj miary Suma rzeczywista, Suma budżetowa, Suma VTB, VTB% z tabeli danych finansowych do wartości.

Tworzenie wariancji dla miary sumy prognozy

Utwórz miarę wariancji do prognozy sumy (VTF Sum) w następujący sposób -

Suma VTF: = [Suma prognozy] - [Suma rzeczywista]

Tworzenie wariancji do miary procentowej prognozy

Utwórz miarę wariancji do prognozy procentowej (VTF%) w następujący sposób -

% VTF: = JEŻELI ([Suma prognozy], [Suma VTF] / ABS ([Suma prognozy]), PUSTE ())

Analiza danych z odchyleniami do miar prognoz

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj rok obrachunkowy z tabeli dat do wierszy.
• Dodaj miary rzeczywistą sumę, prognozowaną sumę, sumę VTF,% VTF z tabeli danych finansowych do wartości.

Tworzenie wariancji prognozy do miary sumy budżetu

Utwórz miarę prognozy odchylenia od sumy budżetu (prognozowana suma VTB) w następujący sposób -

Prognozowana suma VTB: = [Suma budżetu] - [Suma prognozy]

Tworzenie odchylenia prognozy do miary procentu budżetu

Utwórz miarę prognozy odchylenia od procentu budżetu (prognozowany procent VTB) w następujący sposób -

Prognozowany VTB%: = JEŻELI ([Suma budżetu], [Prognozowana suma VTB] / ABS ([Suma budżetu]), PUSTE ())

Analiza danych z odchyleniem prognozy względem środków budżetowych

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj rok obrachunkowy z tabeli dat do wierszy.
• Dodaj miary Suma budżetu, Suma prognozy, Suma prognozy VTB,% prognozy VTB z tabeli Dane finansowe do wartości.

Aby obliczyć wynik obejmujący saldo początkowe od początku okresu, takiego jak rok obrachunkowy, do określonego okresu w czasie, można użyć funkcji analizy czasowej języka DAX. Umożliwi to analizę danych na poziomie miesiąca.

W tym rozdziale dowiesz się, jak tworzyć miary od początku roku i jak przeprowadzać analizę danych na ich podstawie.

Tworzenie miary sumy rzeczywistej od początku roku

Utwórz miarę Suma rzeczywista od początku roku w następujący sposób -

Od początku roku Suma rzeczywista: = TOTALYTD ([Suma rzeczywista], „Data” [Data], WSZYSTKIE („Data”), „6/30”)

Tworzenie miary sumy budżetu od początku roku

Utwórz miarę Suma budżetu od początku roku w następujący sposób -

YTD Suma budżetu: = TOTALYTD ([Suma budżetu], „Data” [data], WSZYSTKIE („Data”), „6/30”)

Tworzenie miary sumy prognozy od początku roku

Utwórz miarę prognozy dla całego roku w następujący sposób -

Suma prognozy YTD: = TOTALYTD ([Suma prognozy], „Data” [Data], WSZYSTKIE („Data”), „30.06.”)

Tworzenie miary sumy rzeczywistej od początku roku do poprzedniego roku

Utwórz miarę Suma rzeczywista od początku roku w następujący sposób -

Suma rzeczywista z poprzedniego roku od początku roku: = TOTALYTD ([Suma rzeczywista z poprzedniego roku], „Data” [data], WSZYSTKIE („Data”), „30.06.”)

Analiza danych za pomocą pomiarów od początku roku

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj miesiąc z tabeli dat do wierszy.

• Dodaj miary Suma rzeczywista, Suma rzeczywista od początku roku, Suma budżetowa od początku roku i Suma prognozy od początku roku z tabeli Dane finansowe do wartości.

• Wstaw fragmentator w roku obrachunkowym z tabeli dat.

• Wybierz FY2016 we fragmentatorze.

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj miesiąc z tabeli dat do wierszy.

• Dodaj miary Suma rzeczywista, Suma rzeczywista od początku roku, Suma rzeczywista z poprzedniego roku i Suma rzeczywista z poprzedniego roku od początku roku z tabeli Dane finansowe do opcji Wartości.

• Wstaw fragmentator w roku obrachunkowym z tabeli dat.

• Wybierz FY2016 we fragmentatorze.

Aby obliczyć wynik obejmujący saldo początkowe od początku okresu, takiego jak kwartał obrachunkowy, do określonego okresu w czasie, można użyć funkcji analizy czasowej języka DAX. Umożliwi to analizę danych na poziomie miesiąca.

W tym rozdziale dowiesz się, jak tworzyć miary kwartalne i jak przeprowadzać analizę danych na ich podstawie.

Tworzenie miary sumy od początku kwartału

Utwórz miarę kwartalną do daty rzeczywistej sumy w następujący sposób -

Kwota rzeczywista QTD: = TOTALQTD ([Suma rzeczywista], 'Data' [Data], WSZYSTKIE ('Data'))

Tworzenie miary sumy budżetu od początku kwartału

Utwórz miarę „Kwartał od początku” w następujący sposób -

QTD Suma budżetu: = TOTALQTD ([Suma budżetu], 'Data' [Data], WSZYSTKO ('Data'))

Tworzenie miary sumy prognozy kwartalnej

Utwórz miarę „Kwartał od początku” w następujący sposób -

QTD Suma budżetu: = TOTALQTD ([Suma budżetu], 'Data' [Data], WSZYSTKO ('Data'))

Tworzenie miary sumy prognozy kwartalnej

Utwórz miarę kwartalną sumę prognozy w następujący sposób -

Suma prognozy QTD: = TOTALQTD ([Suma prognozy], 'Data' [Data], WSZYSTKIE ('Data'))

Tworzenie wcześniejszej miary sumy rzeczywistej od początku kwartału

Utwórz miarę Suma rzeczywista z poprzedniego kwartału w następujący sposób -

Suma rzeczywista z poprzedniego kwartału QTD: = TOTALQTD ([Suma rzeczywista z poprzedniego kwartału], „Data” [Data], WSZYSTKIE („Data”))

Analiza danych za pomocą miar kwartalnych

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj miesiąc obrachunkowy z tabeli dat do wierszy.

• Dodaj miary Suma rzeczywista, Suma rzeczywista QTD, Suma budżetowa QTD i Suma prognozy QTD z tabeli danych finansowych do wartości.

• Wstaw fragmentator w kwartale obrachunkowym z tabeli dat.

• Wybierz FY2016-Q2 w Slicer.

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj miesiąc obrachunkowy z tabeli dat do wierszy.

• Dodaj miary Suma rzeczywista, Suma rzeczywista QTD, Suma rzeczywista z poprzedniego kwartału i Suma rzeczywista z poprzedniego kwartału z tabeli Dane finansowe do Wartości.

• Wstaw fragmentator w tabeli kwartał obrachunkowy z tabeli dat.

• Wybierz FY2016-Q1 we fragmentatorze.

Budżetowanie obejmuje szacowanie przepływów pieniężnych firmy w ciągu roku finansowego. Sytuacja finansowa firmy, jej cele, spodziewane przychody i wydatki są uwzględniane w budżetowaniu.

Jednak warunki rynkowe mogą ulec zmianie w ciągu roku obrotowego i spółka może być zmuszona do zresetowania swoich celów. Wymaga to analizy danych finansowych z budżetem szacowanym na początku roku budżetowego (suma budżetowa) oraz faktycznie wydatkowaną sumą od początku bieżącego roku budżetowego (suma rzeczywista od początku roku).

W dowolnym momencie roku budżetowego możesz obliczyć:

Niewykorzystane saldo

Niewykorzystane saldo to pozostały budżet po faktycznych wydatkach, tj

Unexpended Balance = YTD Budget Sum – YTD Actual Sum

Wykonanie budżetu%

Osiągnięcie budżetu% to procent dotychczasowego wydatku budżetu, tj

Budget Attainment % = YTD Actual Sum/YTD Budget Sum

Obliczenia te pomagają firmom, które wykorzystują budżetowanie do podejmowania decyzji.

Tworzenie niewykorzystanej miary salda

Można utworzyć miarę niewykorzystanego salda w następujący sposób -

Niewykorzystane saldo: = CALCULATE ([Suma budżetu od początku roku], WSZYSTKIE („Dane finansowe” [data])) - [Suma rzeczywista YTD]

Tworzenie miary procentu osiągnięcia budżetu

Miarę procentu osiągnięcia budżetu można utworzyć w następujący sposób -

Realizacja budżetu%: = JEŻELI ([Suma budżetu od początku roku], [Suma rzeczywista od początku roku] / OBLICZ ([Suma budżetu od początku roku], WSZYSTKIE („Dane finansowe” [data])), PUSTE ())

Analiza danych za pomocą środków budżetowych

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj miesiąc z tabeli dat do wierszy.

• Dodaj miary Suma budżetu, Suma budżetu od początku roku, Suma rzeczywista od początku roku,% osiągnięcia budżetu i saldo niewykorzystane z tabeli Dane finansowe do Wartości.

• Wstaw fragmentator w polu Rok obrachunkowy.

• Wybierz FY2016 we fragmentatorze.

Za pomocą miar prognozy można analizować dane finansowe i pomagać organizacji w dokonywaniu niezbędnych korekt w jej celach i założeniach na dany rok, aby dostosować wyniki firmy do zmieniających się wymagań biznesowych.

Aby nadążyć za zmianami, musisz regularnie aktualizować prognozy. Następnie można porównać najnowszą prognozę z budżetem na pozostałą część okresu roku obrotowego, tak aby firma mogła wprowadzić wymagane korekty w celu dostosowania się do zmian biznesowych.

W dowolnym momencie roku budżetowego możesz obliczyć:

Osiągnięcie prognozy%

Osiągnięcie prognozy% to procent przewidywanej sumy, którą do tej pory wydałeś, tj

Forecast Attainment % = YTD Actual Sum/YTD Forecast Sum

Prognozuj niewykorzystane saldo

Prognozowane niewykorzystane saldo to Prognozowana suma pozostała po rzeczywistych wydatkach, tj

Forecast Unexpended Balance = YTD Forecast Sum – YTD Actual Sum

Korekta budżetu

Korekta budżetu to korekta sumy budżetu, jaką organizacja musi dokonać (zwiększyć lub zmniejszyć) na podstawie prognozy.

Budget Adjustment = Forecast Unexpended Balance - Unexpended Balance

Budżet należy zwiększyć, jeśli wynikowa wartość jest dodatnia. W przeciwnym razie można go dostosować do innego celu.

Tworzenie miary procentowej osiągnięcia prognozy

Miarę procentu osiągnięcia prognozy można utworzyć w następujący sposób -

Procent osiągnięcia prognozy: = JEŻELI ([suma prognozy od początku roku], [suma rzeczywista od początku roku] / [suma prognozy od początku roku], PUSTE ())

Tworzenie prognozy niewykorzystanej miary salda

Miarę salda niewykorzystanego prognozy można utworzyć w następujący sposób -

Prognozowane niewykorzystane saldo: = [Suma prognozy YTD] - [Suma rzeczywista YTD]

Tworzenie miary korekty budżetu

Można utworzyć miarę dostosowania budżetu w następujący sposób -

Korekta budżetu: = [przewidywane niewykorzystane saldo] - [niewykorzystane saldo]

Analiza danych za pomocą miar prognozowanych

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj miesiąc z tabeli dat do wierszy.

• Dodaj miary Suma budżetu, Suma budżetu od początku roku, Suma rzeczywista od początku roku,% osiągnięcia budżetu i saldo niewykorzystane z tabeli Dane finansowe do Wartości.

• Wstaw fragmentator w roku obrachunkowym.

• Wybierz FY2016 we fragmentatorze.

Można utworzyć miary Count of Months, które mogą być używane podczas tworzenia miar Zatrudnienia i Kosztu na głowę. Miary te zliczają różne wartości kolumny Miesiąc obrachunkowy, w której kolumna Rzeczywista / Budżet / Prognoza ma wartości niezerowe w tabeli Dane finansowe. Jest to wymagane, ponieważ tabela danych finansowych zawiera wartości zerowe w kolumnie Rzeczywiste i te wiersze mają zostać wykluczone podczas obliczania liczby pracowników i kosztu na głowę.

Tworzenie miary liczby rzeczywistych miesięcy

Miarę Liczba rzeczywistych miesięcy można utworzyć w następujący sposób -

CountOfActualMonths: = CALCULATE (DISTINCTCOUNT ('FinanceData' [miesiąc obrachunkowy]), 'Finance Data' [Actual] <> 0)

Tworzenie miary liczby miesięcy budżetowych

Miarę Liczba miesięcy budżetowych można utworzyć w następujący sposób -

CountOfBudgetMonths: = CALCULATE (DISTINCTCOUNT ('FinanceData' [miesiąc obrachunkowy]), 'Dane finansowe' [budżet] <> 0)

Tworzenie miary liczby miesięcy prognozy

Miarę Count of Forecast Months można utworzyć w następujący sposób -

CountOfForecastMonths: = CALCULATE (DISTINCTCOUNT ('FinanceData' [Fiskalny miesiąc]), 'Finance Data' [Forecast] <> 0)

Możesz utworzyć miary Kończące się zatrudnienie na określony czas. Kończące zatrudnienie to suma osób na ostatni dzień w podanym okresie, dla którego mamy niepustą sumę osób.

Końcową liczbę pracowników uzyskuje się w następujący sposób -

• Przez miesiąc - suma osób na koniec określonego miesiąca.

• Za kwartał - suma osób na koniec ostatniego miesiąca danego kwartału.

• Przez rok - suma osób na koniec ostatniego miesiąca danego roku.

Tworzenie rzeczywistej miary końcowego zatrudnienia

Możesz utworzyć miarę rzeczywistego końcowego zatrudnienia w następujący sposób -

Rzeczywista liczba pracowników końcowych: = OBLICZ (SUMA („Dane finansowe” [rzeczywiste osoby]), OSTATNIE NIEPUSTE („Dane finansowe” [Data], JEŻELI (OBLICZ (SUMA („Dane finansowe” [Faktyczne osoby]), WSZYSTKIE (Konta)) ) = 0, PUSTE (), OBLICZ (SUMA („Dane finansowe” [Faktyczne osoby]), WSZYSTKIE (Konta)))), WSZYSTKIE (Konta))

Funkcja DAX LASTNONBLANK, jak użyta powyżej, zwraca ostatnią datę, dla której masz niepustą sumę osób, aby można było obliczyć sumę osób w tym dniu.

Tworzenie miary stanu zatrudnienia na koniec budżetu

Miarę liczby pracowników kończących budżet można utworzyć w następujący sposób -

Liczba osób na koniec budżetu: = CALCULATE (SUM („Dane finansowe” [Osoby budżetowe]), OSTATNIE NIEPUSTE („Dane finansowe” [Data], JEŻELI (OBLICZ (SUMA („Dane finansowe” [Osoby budżetowe]), WSZYSTKIE (Konta)) ) = 0, PUSTE (), OBLICZ (SUMA („Dane finansowe” [Osoby budżetowe]), WSZYSTKIE (Konta)))), WSZYSTKIE (Konta))

Tworzenie miary stanu zatrudnienia na koniec prognozy

Miarę Końcowego poziomu zatrudnienia można utworzyć w następujący sposób -

Licznik pracowników końcowych prognozy: = OBLICZ (SUMA („Dane finansowe” [prognoza osób]), OSTATNIE NIEPUSTE („Dane finansowe” [Data], JEŻELI (OBLICZ (SUMA („Dane finansowe” [Prognoza osób])), WSZYSTKIE (Konta) ) = 0, PUSTE (), OBLICZ (SUMA („Dane finansowe” [prognozy osób]), WSZYSTKIE (konta)))), WSZYSTKIE (konta))

Tworzenie rzeczywistego licznika zatrudnienia na koniec poprzedniego roku

Miarę rzeczywistego zatrudnienia na koniec poprzedniego roku można utworzyć w następujący sposób -

Rzeczywiste końcowe zatrudnienie w poprzednim roku: = CALCULATE („Dane finansowe” [Rzeczywista liczba pracowników na końcu], DATEADD („Data” [Data], - 1, YEAR))

Analiza danych z kończącymi się miarami zatrudnienia

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj pola Rok obrachunkowy i miesiąc z tabeli Data do Wiersze.

• Dodaj miary Rzeczywiste końcowe zatrudnienie, Końcowe zatrudnienie w budżecie, Prognozowane zatrudnienie na koniec roku, Rzeczywiste końcowe zatrudnienie z poprzedniego roku z tabeli Dane finansowe do Wartości.

• Wstaw fragmentator w polu Rok obrachunkowy.

• Wybierz FY2016 we fragmentatorze.

W poprzednim rozdziale nauczyłeś się, jak obliczyć końcowe zatrudnienie za określony okres. Podobnie możesz utworzyć średni miesięczny stan zatrudnienia dla dowolnego wybranego okresu miesięcy.

Średni miesięczny stan zatrudnienia to suma miesięcznego zatrudnienia podzielona przez liczbę wybranych miesięcy.

Miary te można tworzyć za pomocą funkcji DAX AVERAGEX.

Tworzenie miary rzeczywistego średniego zatrudnienia

Miarę rzeczywistego średniego zatrudnienia można utworzyć w następujący sposób -

Rzeczywiste średnie zatrudnienie: = ŚREDNIA (VALUES („Dane finansowe” [miesiąc obrachunkowy]), [rzeczywista liczba pracowników końcowych])

Tworzenie miary średniego zatrudnienia w budżecie

Miarę rzeczywistego średniego zatrudnienia można utworzyć w następujący sposób -

Budżetowe średnie zatrudnienie: = ŚREDNIA (WARTOŚCI („Dane finansowe” [miesiąc budżetowy]), [liczba osób na koniec budżetu])

Tworzenie prognozy miary średniego zatrudnienia

Możesz utworzyć miarę prognozy średniego zatrudnienia w następujący sposób -

Prognozowany średni stan zatrudnienia: = ŚREDNIA (VALUES („Dane finansowe” [miesiąc obrachunkowy]), [rzeczywista liczba pracowników końcowych])

Tworzenie miary rzeczywistego średniego zatrudnienia w poprzednim roku

Miarę rzeczywistego średniego zatrudnienia w poprzednim roku można utworzyć w następujący sposób -

Rzeczywiste średnie zatrudnienie w poprzednim roku: = CALCULATE („Dane finansowe” [Rzeczywiste średnie zatrudnienie], DATEADD („Data” [Data], -1, ROK))

Analiza danych za pomocą miar średniego zatrudnienia

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj pola Rok obrachunkowy i miesiąc z tabeli Data do Wiersze.

• Dodaj miary Rzeczywiste średnie zatrudnienie, Średnie zatrudnienie w budżecie, Prognozowane średnie zatrudnienie, Rzeczywiste średnie zatrudnienie w poprzednim roku z tabeli Dane finansowe do Wartości.

• Wstaw fragmentator w polu Rok obrachunkowy.

• Wybierz FY2016 we fragmentatorze.

W poprzednich rozdziałach opisano, jak tworzyć miary Liczba miesięcy i Średnie zatrudnienie. Możesz użyć tych miar do obliczenia podstawowych miar zatrudnienia -

• Rzeczywista całkowita liczba pracowników
• Całkowite zatrudnienie w budżecie
• Prognozuj całkowite zatrudnienie

W kolejnych rozdziałach dowiesz się, jak korzystać z tych podstawowych miar Zatrudnienia w innych obliczeniach, takich jak Liczba pracowników w ujęciu rocznym i miary wariancji.

Tworzenie miary rzeczywistego całkowitego zatrudnienia

Miarę rzeczywistego całkowitego zatrudnienia można utworzyć w następujący sposób -

Rzeczywista łączna liczba pracowników: = „Dane finansowe” [Rzeczywista średnia liczba pracowników] * „Dane finansowe” [CountOfActualMonths]

Tworzenie miary całkowitego zatrudnienia w budżecie

Miarę całkowitego zatrudnienia w budżecie można utworzyć w następujący sposób -

Całkowite zatrudnienie w budżecie: = „Dane finansowe” [Średnia liczba pracowników w budżecie] * „Dane finansowe” [CountOfBudgetMonths]

Tworzenie miary prognozy całkowitego zatrudnienia

Miarę całkowitego zatrudnienia według prognozy można utworzyć w następujący sposób -

Prognozowane całkowite zatrudnienie: = „Dane finansowe” [Średnia prognozowana liczba pracowników] * „Dane finansowe” [CountOfForecastMonths]

W poprzednim rozdziale nauczyłeś się, jak tworzyć podstawowe miary zatrudnienia - tj. Rzeczywiste całkowite zatrudnienie, Całkowity budżet w budżecie i Prognozowany całkowity poziom zatrudnienia.

W tym rozdziale dowiesz się, jak tworzyć miary zatrudnienia w ujęciu rocznym i jak analizować dane za pomocą tych miar.

Tworzenie miary rzeczywistego zatrudnienia na koniec roku w ujęciu rocznym

Możesz utworzyć miarę rzeczywistego kończącego się zatrudnienia w ujęciu rocznym w następujący sposób -

Rzeczywiste końcowe zatrudnienie w ujęciu rocznym: = [rzeczywista końcowa liczba pracowników] - [rzeczywiste końcowe zatrudnienie w poprzednim roku]

Tworzenie miary rzeczywistego średniego zatrudnienia w ujęciu rocznym

Miarę rzeczywistego średniego zatrudnienia w ujęciu rocznym można utworzyć w następujący sposób -

Rzeczywiste średnie zatrudnienie w ujęciu rocznym: = [rzeczywiste średnie zatrudnienie] - [rzeczywiste średnie zatrudnienie w poprzednim roku]

Tworzenie miary rzeczywistego całkowitego zatrudnienia w ujęciu rocznym

Miarę rzeczywistego całkowitego zatrudnienia w ujęciu rocznym można utworzyć w następujący sposób -

R / r Rzeczywista łączna liczba pracowników: = [rzeczywista łączna liczba pracowników] - [rzeczywista łączna liczba pracowników w poprzednim roku]

Analiza danych na podstawie rzeczywistych miar zatrudnienia w ujęciu rocznym

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj pola Kwartał obrachunkowy i Miesiąc z tabeli Data do Wiersze.

• Dodaj miary - Rzeczywista liczba końcowych gotówki w poprzednim roku, Rzeczywista liczba końcowa w poprzednim roku, Rzeczywista liczba końcowa w ujęciu rocznym do wartości.

• Wstaw fragmentator w polu Rok obrachunkowy.

• Wybierz FY2016 we fragmentatorze.

Utwórz kolejną tabelę Power Pivot w tym samym arkuszu w następujący sposób -

• Dodaj pola Kwartał obrachunkowy i Miesiąc z tabeli Data do Wiersze.

• Dodaj miary - rzeczywisty średni licznik zapasów, rzeczywisty średni licznik zapasów w poprzednim roku, rzeczywisty średni licznik zapasów rok do roku do wartości.

Połącz fragmentator z tą tabelą przestawną w następujący sposób -

• Kliknij fragmentator.
• Kliknij kartę Opcje w obszarze Narzędzia fragmentatora na Wstążce.
• Kliknij opcję Zgłoś połączenia.

Pojawi się okno dialogowe Report Connections.

• Wybierz dwie powyższe tabele przestawne.
• Kliknij OK.

Tworzenie miary poziomu zatrudnienia na koniec roku budżetowego

Miarę stanu zatrudnienia na koniec roku można utworzyć w następujący sposób -

Rokrocznie liczba pracowników kończących budżet: = [liczba pracowników kończących budżet] - [rzeczywista liczba pracowników kończących budżet w poprzednim roku]

Tworzenie miary średniego zatrudnienia w budżecie rocznym

Miarę średniego zatrudnienia w budżecie rocznym można utworzyć w następujący sposób -

Rokrocznie Budżet Średnie zatrudnienie: = [Średni budżet w budżecie] - [Rzeczywiste średnie zatrudnienie w poprzednim roku]

Tworzenie miary całkowitego zatrudnienia rocznego budżetu

Miarę całkowitego zatrudnienia w budżecie rocznym można utworzyć w następujący sposób -

Łączne zatrudnienie w budżecie r / r: = [Całkowity budżet w budżecie] - [Rzeczywista całkowita liczba pracowników w poprzednim roku]

Tworzenie prognozy rocznej kończącej miarę zatrudnienia

Miarę zatrudnienia na koniec roku można utworzyć w następujący sposób -

R / R Końcowa liczba pracowników na koniec prognozy: = [Liczba pracowników na koniec prognozy] - [Rzeczywista liczba pracowników na koniec poprzedniego roku]

Tworzenie prognozy rocznej średniej miary zatrudnienia

Miarę średniego zatrudnienia w prognozie rocznej można utworzyć w następujący sposób -

Średnia prognozowana liczba pracowników w ujęciu rocznym: = [Średnia prognozowana liczba pracowników] - [Rzeczywista średnia liczba pracowników w poprzednim roku]

Tworzenie prognozy rocznej Miara całkowitego zatrudnienia

Miarę całkowitego zatrudnienia w ramach prognozy rocznej można utworzyć w następujący sposób -

Łączne zatrudnienie w ujęciu rocznym: = [łączne szacowane zatrudnienie] - [rzeczywiste łączne zatrudnienie w poprzednim roku]

Można utworzyć miary Odchylenie zatrudnienia na podstawie miar stanu zatrudnienia, które zostały utworzone do tej pory.

Tworzenie odchylenia od miary stanu zatrudnienia na koniec budżetu

Możesz utworzyć odchylenie miary stanu zatrudnienia na koniec budżetu w następujący sposób -

Licznik klientów końcowych VTB: = 'Dane finansowe' [Licznik osób kończących budżet] - 'Dane finansowe' [Rzeczywista liczba osób kończących]

Tworzenie odchylenia od miary średniego zatrudnienia w budżecie

Możesz utworzyć miara odchylenia od średniego zatrudnienia w budżecie w następujący sposób -

Średnia liczba zatrudnionych w VTB: = „Dane finansowe” [Średnia liczba pracowników w budżecie] - „Dane finansowe” [Rzeczywista średnia liczba pracowników

Tworzenie odchylenia od miary całkowitego zatrudnienia w budżecie

Możesz utworzyć Miarę odchylenia od całkowitego zatrudnienia w budżecie w następujący sposób -

VTB Total Head Count: = 'Finance Data' [Budget Total Headcount] - 'Finance Data' [Actual Total Head Count]

Tworzenie wariancji dla miary końcowego zatrudnienia prognozy

Możesz utworzyć odchylenie miary końcowego zatrudnienia w prognozie w następujący sposób -

VTF Ending Head Count: = 'Finance Data' [Forecast Ending Head Count] - 'Finance Data' [Actual Ending Head Count]

Tworzenie wariancji do prognozy miary średniego zatrudnienia

Możesz utworzyć odchylenie do miary średniej wielkości zatrudnienia w prognozie w następujący sposób -

Średnia liczba pracowników VTF: = „Dane finansowe” [Średnia prognozowana liczba pracowników] - „Dane finansowe” [Rzeczywista średnia liczba pracowników]

Tworzenie wariancji do prognozy miary całkowitego zatrudnienia

Możesz utworzyć odchylenie miary całkowitego zatrudnienia w prognozie w następujący sposób -

Całkowita liczba pracowników VTF: = „Dane finansowe” [Prognozowane całkowite zatrudnienie] - „Dane finansowe” [Rzeczywista całkowita liczba pracowników]

Tworzenie odchylenia prognozy dla miary stanu zatrudnienia na koniec budżetu

Można utworzyć odchylenie prognozy dla miary stanu zatrudnienia na koniec budżetu w następujący sposób -

Prognoza VTB Ending Head Count: = 'Finance Data' [Budget Ending Head Count] - 'Finance Data' [Forecast Ending Head Count]

Tworzenie odchylenia prognozy do miary średniego zatrudnienia w budżecie

Możesz utworzyć Odchylenie prognozy do miary średniego zatrudnienia w budżecie w następujący sposób -

Prognozowana średnia liczba pracowników VTB: = „Dane finansowe” [Średnia liczba pracowników w budżecie] - „Dane finansowe” [Średnia liczba pracowników w prognozie]

Tworzenie odchylenia prognozy dla miary całkowitego zatrudnienia w budżecie

Możesz utworzyć odchylenie prognozy dla miary całkowitego zatrudnienia w budżecie w następujący sposób -

Prognozowany VTB Total Head Count: = „Dane finansowe” [Budżet - całkowite zatrudnienie] - „Dane finansowe” [Prognozowane całkowite zatrudnienie

Dowiedziałeś się o dwóch głównych kategoriach miar -

• Środki finansowe.
• Środki dotyczące zatrudnienia.

Trzecią główną kategorią miar, których się nauczysz, są miary kosztów osobowych. Każda organizacja będzie zainteresowana znajomością rocznego kosztu na głowę. Roczny koszt na głowę stanowi koszt dla firmy związany z zatrudnieniem jednego pracownika na cały rok.

Aby utworzyć miary kosztu na głowę, należy najpierw utworzyć pewne wstępne miary kosztów osobowych. W tabeli Accounts znajduje się kolumna - Sub Class, która zawiera People jako jedną z wartości. W związku z tym można zastosować filtr w tabeli Konta w kolumnie Podklasa, aby uzyskać kontekst filtru w tabeli Dane finansowe w celu uzyskania kosztu osobistego.

W ten sposób można użyć miar Get People Cost i Count of Months, aby utworzyć miary Annualized People Cost. Na koniec można utworzyć miary Annualized Cost Per Head na podstawie miar Annualized People Cost i Average Head Count.

Tworzenie miary rzeczywistych kosztów osobowych

Miarę rzeczywistego kosztu ludzi można utworzyć w następujący sposób -

Rzeczywisty koszt osobowy: = OBLICZ ('Dane finansowe' [Suma rzeczywista], FILTR ('Dane finansowe', POWIĄZANE (Konta [podklasa]) = "Osoby"))

Tworzenie miary kosztów budżetowych

Miarę Budżetowego kosztu ludzi można utworzyć w następujący sposób -

Budżetowy koszt ludzi: = OBLICZ ('Dane finansowe' [Suma budżetu], FILTR ('Dane finansowe', POWIĄZANE (Konta [podklasa]) = "Osoby"))

Tworzenie prognozy miary kosztów osobowych

Miarę Prognozy kosztów osobowych można utworzyć w następujący sposób -

Prognozowany koszt osobowy: = OBLICZ ('Dane finansowe' [Suma prognozy], FILTR ('Dane finansowe', POWIĄZANE (Konta [podklasa]) = "Osoby"))

Tworzenie rocznej miary rzeczywistych kosztów osobowych

Miarę rzeczywistego rocznego kosztu ludzi można utworzyć w następujący sposób -

Roczny rzeczywisty koszt ludzi: = IF ([CountOfActualMonths], [Actual People Cost] * 12 / [CountOfActualMonths], BLANK ())

Tworzenie miary rocznych kosztów budżetowych

Miarę Annualized Budget People Cost można utworzyć w następujący sposób -

Roczny budżetowy koszt osób: = IF ([CountOfBudgetMonths], [budżetowy koszt osób] * 12 / [CountOfBudgetMonths], BLANK ())

Tworzenie prognozy rocznej miary kosztów ludzkich

Miarę Annualized Forecast People Cost można utworzyć w następujący sposób -

Roczny prognozowany koszt ludzi: = IF ([CountOfForecastMonths], [prognozowany koszt ludzi] * 12 / [CountOfForecastMonths], BLANK ())

Tworzenie rzeczywistego rocznego kosztu na jednostkę miary

Miarę rzeczywistego rocznego kosztu na głowę (CPH) można utworzyć w następujący sposób -

Rzeczywisty roczny CPH: = JEŚLI ([Rzeczywiste średnie zatrudnienie], [Roczny rzeczywisty koszt ludzi] / [Rzeczywiste średnie zatrudnienie], PUSTE ())

Tworzenie rocznego kosztu budżetu na środek

Miarę budżetowego rocznego kosztu na głowę (CPH) można utworzyć w następujący sposób -

Budżet roczny CPH: = JEŚLI ([Średni budżet w budżecie], [Roczny budżetowy koszt osób] / [Średni budżet w budżecie], PUSTE ())

Tworzenie prognozy rocznego kosztu na jednostkę miary

Można utworzyć miarę prognozowanego rocznego kosztu na głowę (CPH) w następujący sposób -

Prognozowany roczny CPH: = JEŻELI ([prognozowany średni poziom zatrudnienia], [prognozowany roczny koszt osób] / [prognozowany średni poziom zatrudnienia], PUSTE ())

Tworzenie rzeczywistego rocznego kosztu na jednostkę miary z poprzedniego roku

Można utworzyć miarę rzeczywistego rocznego kosztu na głowę (CPH) z poprzedniego roku w następujący sposób -

Rzeczywisty roczny CPH w poprzednim roku: = CALCULATE ([Rzeczywisty roczny CPH], DATEADD („Data” [Data], - 1, YEAR))

Analiza danych z miarami kosztu na głowę

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj pola Kwartał obrachunkowy i Miesiąc obrachunkowy z tabeli dat do wierszy.

• Dodaj miary rzeczywisty roczny CPH, budżetowy roczny CPH i prognozowany roczny CPH do kolumn.

• Dodaj pole Rok obrachunkowy z tabeli dat do filtrów.

• Wybierz FY2016 w filtrze.

Utwórz kolejną tabelę Power Pivot w następujący sposób -

• Dodaj pole Kwartał obrachunkowy z tabeli dat do wierszy.

• Dodaj miary rzeczywisty roczny CPH i rzeczywisty roczny CPH z poprzedniego roku do kolumn.

• Wstaw fragmentator w polu Rok obrachunkowy z tabeli dat.

• Wybierz FY2015 i FY2016 na Slicer.

Dowiedziałeś się, jak tworzyć miary dla rocznego kosztu na głowę i całkowitego zatrudnienia. Za pomocą tych miar można tworzyć miary Rate Variance i Volume Variance.

• Miary wariancji kursu obliczają, jaka część wariancji walutowej jest spowodowana różnicami w koszcie na głowę.

• Miary odchylenia wolumenu obliczają, jaka część wariancji walutowej jest spowodowana wahaniami liczby pracowników.

Tworzenie odchylenia od miary stopy budżetu

Miarę Odchylenie do stawki budżetu można utworzyć w następujący sposób -

Stawka VTB: = ([budżet roczny CPH] / 12- [rzeczywisty roczny CPH] / 12) * [rzeczywista całkowita liczba osób]

Tworzenie odchylenia od miary wielkości budżetu

Miarę Odchylenie do wielkości budżetu można utworzyć w następujący sposób -

Wolumen VTB: = [Całkowita liczba osób w VTB] * [Budżet roczny CPH] / 12

Analiza danych z odchyleniami od miar budżetowych

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj pola Kwartał obrachunkowy i Miesiąc obrachunkowy z tabeli dat do wierszy.
• Dodaj miary rzeczywisty roczny CPH, roczny budżetowy CPH, stawkę VTB, wolumen VTB, sumę VTB do wartości.
• Dodaj pola Rok obrachunkowy z tabeli dat i Podklasa z tabeli Konta do filtrów.
• Wybierz FY2016 w filtrze roku obrachunkowego.
• Wybierz osoby w filtrze podklasy.
• Filtruj etykiety wierszy dla wartości kwartałów fiskalnych FY2016-Q1 i FY2016-Q2.

W powyższej tabeli przestawnej można zaobserwować:

• Wyświetlana suma VTB dotyczy tylko podklasy - osoby.

• Dla kwartału fiskalnego FY2016-Q1 suma VTB wynosi $4,705,568, VTB Rate is $970 506 297, a wolumen VTB wynosi -965 800 727 USD.

• Miara VTB Rate oblicza, że ​​970 506 297 USD odchylenia od budżetu (suma VTB) jest spowodowane różnicą w koszcie na głowę, a -965 800 727 USD jest spowodowane różnicą w liczbie pracowników.

• Jeśli dodasz stawkę VTB i wolumen VTB, otrzymasz 4.705.568 USD, taką samą wartość, jaką zwraca suma VTB dla osób z podklasy.

• Podobnie, dla kwartału fiskalnego FY2016-Q2, VTB Rate wynosi $1,281,467,662, and VTB Volume is $-1,210,710,978. Jeśli dodasz stawkę VTB i wolumen VTB, otrzymasz 70 756 678 USD, co jest wartością sumy VTB pokazaną w tabeli przestawnej.

Tworzenie miary rocznej stopy procentowej

Miarę wskaźnika rocznego można utworzyć w następujący sposób -

Stawka r / r: = ([rzeczywisty roczny CPH] / 12- [rzeczywisty roczny CPH w poprzednim roku] / 12) * [rzeczywista łączna liczba pracowników]

Tworzenie miary wolumenu rok do roku

Miarę wolumenu rok do roku można utworzyć w następujący sposób -

Wolumen r / r: = [r / r rzeczywiste całkowite zatrudnienie] * [rzeczywisty roczny CPH w poprzednim roku] / 12

Tworzenie wariancji do miary stopy prognozy

Miarę Odchylenie do prognozy można utworzyć w następujący sposób -

Stawka VTF: = ([Prognozowany roczny CPH] / 12- [Rzeczywisty roczny CPH] / 12) * [Rzeczywista całkowita liczba pracowników]

Tworzenie wariancji do prognozowanej miary wielkości

Możesz utworzyć miarę wariancji do prognozy wolumenu w następujący sposób -

Wolumen VTF: = [Całkowita liczba pracowników VTF] * [Prognozowany roczny CPH] / 12

Analiza danych z odchyleniami do miar prognoz

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj pola Kwartał obrachunkowy i Miesiąc obrachunkowy z tabeli dat do wierszy.

• Dodaj miary rzeczywisty roczny CPH, prognozowany roczny CPH, stopa VTF, wolumen VTF, suma VTF do wartości.

• Dodaj pola Rok obrachunkowy z tabeli dat i Podklasa z tabeli Konta do filtrów.

• Wybierz FY2016 w filtrze roku obrachunkowego.

• Wybierz osoby w filtrze podklasy.

• Filtruj etykiety wierszy dla wartości kwartałów fiskalnych FY2016-Q1 i FY2016-Q2.

Tworzenie wariancji prognozy do miary stopy budżetu

Można utworzyć miarę Odchylenie prognozy do stopy budżetu w następujący sposób -

Prognozowany wskaźnik VTB: = ([budżet roczny CPH] / 12- [prognozowany roczny CPH] / 12) * [prognozowane całkowite zatrudnienie]

Tworzenie prognozy odchylenia od miary wielkości budżetu

Można utworzyć miarę Odchylenie prognozy do wielkości budżetu w następujący sposób -

Prognozowany wolumen VTB: = [Prognozowany VTB Total Head Count] * [Budżet Roczny CPH] / 12

Analiza danych z odchyleniem prognozy względem środków budżetowych

Utwórz tabelę Power Pivot w następujący sposób -

• Dodaj pola Kwartał obrachunkowy i Miesiąc obrachunkowy z tabeli dat do wierszy.

• Dodaj miary Budżet Roczny CPH, Prognozowany Roczny CPH, Prognozowany kurs VTB, Prognozowany wolumen VTB, Prognozowana suma VTB do wartości.

• Dodaj pola Rok obrachunkowy z tabeli dat i Podklasa z tabeli Konta do filtrów.

• Wybierz FY2016 w filtrze roku obrachunkowego.

• Wybierz osoby w filtrze podklasy.

• Filtruj etykiety wierszy dla wartości kwartałów fiskalnych FY2016-Q1 i FY2016-Q2.