Kiedy używać kolumn obliczeniowych i pól obliczeniowych

Większość użytkowników, którzy po raz pierwszy uczą się korzystać z dodatku Power Pivot, odkrywają, że prawdziwa moc tkwi w agregowaniu lub obliczaniu wyników w jakiś sposób. Jeśli dane zawierają kolumnę z wartościami liczbowymi, można je łatwo zagregować, wybierając je w tabeli przestawnej lub na liście pól programu Power View. Ponieważ jest to wartość liczbowa, wartość ta będzie automatycznie sumowana, obliczana średnia, zliczana lub obliczana niezależnie od wybranego typu agregacji. Nazywa się to miarą niejawną. Miary niejawne doskonale nadają się do szybkiej i łatwej agregacji, ale mają ograniczenia, które prawie zawsze można pokonać za pomocą miarjawnych i kolumn obliczeniowych.

Przyjrzyjmy się najpierw przykładowi, w którym za pomocą kolumny obliczeniowej dodaję nową wartość tekstową do każdego wiersza w tabeli o nazwie Produkt. Każdy wiersz w tabeli Produkty zawiera różnego rodzaju informacje na temat każdego sprzedawanego przez nas produktu. Mamy kolumny dla nazwy produktu, koloru, rozmiaru, ceny dealera itp. Mamy jeszcze jedną powiązaną tabelę o nazwie KategoriaProduktu, która zawiera kolumnę NazwaKategoriiProduktu. Chcemy, aby każdy produkt w tabeli Produkty zawierał nazwę kategorii produktu z tabeli Kategoria produktów. W tabeli Produkty możemy utworzyć kolumnę obliczeniową o nazwie Kategoria produktu w następujący sposób:

Nasza nowa formuła Kategoria produktu używa funkcji języka DAX RELATED w celu pobrania wartości z kolumny ProductCategoryName w powiązanej tabeli Product Category, a następnie wprowadza te wartości dla każdego produktu (w każdym wierszu) w tabeli Product.

Jest to doskonały przykład zastosowania kolumny obliczeniowej w celu dodania stałej wartości do każdego wiersza, której można użyć później w obszarze WIERSZE, KOLUMNY lub FILTRY tabeli przestawnej lub w raporcie programu Power View.

Utwórzmy kolejny przykład, w którym chcemy obliczyć marżę zysku dla naszych kategorii produktów. Jest to typowy scenariusz, nawet w wielu samouczkach. W modelu danych mamy tabelę Sprzedaż, która zawiera dane transakcji, oraz istnieje relacja między tabelą Sprzedaż i tabelą Kategoria produktów. W tabeli Sprzedaż mamy kolumnę zawierającą kwoty sprzedaży i drugą kolumnę zawierającą koszty.

Możemy utworzyć kolumnę obliczeniową, która oblicza kwotę zysku dla każdego wiersza, odejmując wartości w kolumnie COGS od wartości w kolumnie KwotaSprzedaży, w następujący sposób:

Teraz mogę utworzyć tabelę przestawną i przeciągnąć pole Kategoria produktu do obszaru KOLUMNY, a nasze nowe pole Zysk do obszaru WARTOŚCI (kolumna w tabeli w dodatku PowerPivot jest polem na liście pól tabeli przestawnej). Wynikiem jest miara niejawna o nazwie Suma zysku. Jest to zagregowana ilość wartości z kolumny zysku dla każdej z różnych kategorii produktów. Nasz wynik wygląda następująco:

W takim przypadku pole Zysk ma sens tylko jako pole w WARTOŚCIACH. Gdybyśmy umieścili pole Zysk w obszarze KOLUMNY, nasza tabela przestawna wyglądałaby następująco:

Pole Zysk nie dostarcza żadnych użytecznych informacji, gdy jest umieszczone w obszarach KOLUMNY, WIERSZE lub FILTRY. Ma sens tylko jako wartość zagregowana w obszarze WARTOŚCI.

Utworzyliśmy kolumnę o nazwie Zysk, która oblicza marżę zysku dla każdego wiersza w tabeli Sprzedaż. Następnie dodaliśmy pole Zysk do obszaru WARTOŚCI naszej tabeli przestawnej, automatycznie tworząc miarę niejawną, w której wynik jest obliczany dla każdej kategorii produktów. Jeśli myślisz, że naprawdę obliczyliśmy zysk dla naszych kategorii produktów dwa razy, masz rację. Najpierw obliczyliśmy zysk dla każdego wiersza w tabeli Sprzedaż, a następnie dodaliśmy zysk do obszaru WARTOŚCI, w którym został zagregowany dla każdej kategorii produktów. Jeśli również uważasz, że nie trzeba było tworzyć kolumny obliczeniowej Zysk, również masz rację. Ale jak w takim razie obliczyć nasz zysk bez tworzenia kolumny obliczeniowej Zysk?

Zysk byłby naprawdę lepiej obliczony jako wyraźna miara.

Na razie zostawimy kolumnę obliczeniową Zysk w tabeli Sprzedaż, kolumnę Kategoria produktu w tabeli KOLUMNY oraz kolumnę Zysk w WARTOŚCIACH tabeli przestawnej, aby porównać wyniki.

W obszarze obliczeń tabeli Sprzedaż utworzymy miarę o nazwie Zysk całkowity (aby uniknąć konfliktów nazw). Ostatecznie zostaną wyświetlone te same wyniki, co poprzednio, ale bez kolumny obliczeniowej Zysk.

Najpierw w tabeli Sprzedaż zaznaczamy kolumnę KwotaSprzedaży, a następnie klikamy przycisk Autosumowanie, aby utworzyć jawną miarę Suma KwotaSprzedaży . Pamiętaj, że miara jawna to taka, którą tworzymy w obszarze obliczeń tabeli w dodatku Power Pivot. To samo robimy dla kolumny COGS. Zmienimy nazwy tych elementów na Łączna kwota sprzedaży i Łączna kwota sprzedaży , aby ułatwić ich identyfikację.

Następnie tworzymy kolejną miarę za pomocą następującej formuły:

Całkowity zysk:=[Łączna kwotaSprzedaży] - [Łączna kwota sprzedaży]

Po zmianie formatu naszej nowej miary Całkowity zysk na walutę możemy dodać ją do tabeli przestawnej.

Jak widać, nasza nowa miara Całkowity zysk zwraca te same wyniki, co w przypadku tworzenia kolumny obliczeniowej Zysk i umieszczania jej w WARTOŚCIACH. Różnica polega na tym, że nasza miara Całkowity zysk jest o wiele wydajniejsza i sprawia, że nasz model danych jest bardziej przejrzysty i szczuplejszy, ponieważ obliczenia są wykonywane w danym momencie i tylko dla pól wybranych dla naszej tabeli przestawnej. Ta kolumna obliczeniowa Zysk nie jest nam wcale potrzebna.

Dlaczego ta ostatnia część jest ważna? Kolumny obliczeniowe dodają dane do modelu danych, a dane zajmują pamięć. Jeśli odświeżymy model danych, zasoby przetwarzania będą również potrzebne do ponownego obliczenia wszystkich wartości w kolumnie Zysk. Tak naprawdę nie musimy zajmować się takimi zasobami, ponieważ naprawdę chcemy obliczyć nasz zysk, gdy wybieram pola, dla których oczekujemy Zysk w tabeli przestawnej, takie jak kategorie produktów, region lub według dat.

Spójrzmy na inny przykład. Taki, w którym kolumna obliczeniowa tworzy wyniki, które na pierwszy rzut oka wyglądają poprawnie, ale...

W tym przykładzie chcemy obliczyć kwoty sprzedaży jako procent łącznej sprzedaży. W tabeli Sprzedaż utworzono kolumnę obliczeniową o nazwie % sprzedaży , która wygląda tak:

Nasza formuła mówi: W każdym wierszu w tabeli Sprzedaż podziel kwotę w kolumnie KwotaSprzedaży przez sumę wszystkich kwot w kolumnie KwotaSprzedaży.

Jeśli utworzymy tabelę przestawną i dodam kategorię produktu do kolumny, a następnie wybierzemy naszą nową kolumnę % sprzedaży , aby umieścić ją w WARTOŚCIACH, otrzymamy sumę całkowitą % sprzedaży dla każdej z naszych kategorii produktów.

Dobrze. Jak na razie wygląda to dobrze. Dodajmy jednak fragmentator. Dodaję pozycję Rok w kanale Kalendarz, a następnie wybieram rok. W tym przypadku wybieramy rok 2007. Oto, co otrzymujemy.

Na pierwszy rzut oka może się wydawać, że to prawda. Ale nasze udziały procentowe powinny wynosić 100%, ponieważ chcemy poznać procent całkowitej sprzedaży dla każdej z naszych kategorii produktów w 2007 roku. Co więc poszło nie tak?

W naszej kolumnie % sprzedaży obliczono procent dla każdego wiersza będącego wartością w kolumnie KwotaSprzedaży podzielony przez sumę wszystkich wartości w kolumnie KwotaSprzedaży. Wartości w kolumnie obliczeniowej są stałe. Są one niezmiennym wynikiem dla każdego wiersza w tabeli. Wartość procentowa sprzedaży dodana do tabeli przestawnej została zagregowana jako suma wszystkich wartości w kolumnie KwotaSprzedaży. Suma wszystkich wartości w kolumnie % sprzedaży będzie zawsze równa 100%.

Możemy usunąć kolumnę obliczeniową % sprzedaży, ponieważ nie pomoże nam ona. Zamiast tego utworzymy miarę, która poprawnie oblicza nasz procent łącznej sprzedaży, niezależnie od zastosowanych filtrów i fragmentatorów.

Pamiętasz utworzoną wcześniej miarę TotalSalesAmount, która po prostu sumuje kolumnę SalesAmount? Użyliśmy go jako argumentu w naszej mierze Całkowity zysk i zamierzamy użyć go ponownie w naszym nowym polu obliczeniowym.

Utworzymy nową miarę za pomocą następującej formuły:

% łącznej sprzedaży:=([Łączna kwotaSprzedaży]) / CALCULATE([Łączna kwotaSprzedaży], ALLSELECTED())

Ta formuła podaje: Podziel wynik z tabeli Łączna KwotaSprzedaży przez sumę kolumny KwotaSprzedaży bez żadnych filtrów kolumn i wierszy innych niż zdefiniowane w tabeli przestawnej.

Jeśli dodamy nasz nowy % łącznej sprzedaży do tabeli przestawnej, otrzymamy:

To wygląda lepiej. Teraz nasz % całkowitej sprzedaży dla każdej kategorii produktów jest obliczany jako procent całkowitej sprzedaży w roku 2007. Jeśli wybierzemy inny rok lub więcej niż jeden rok we fragmentatorze CalendarYear, uzyskamy nowe wartości procentowe dla naszych kategorii produktów, ale nasza suma końcowa nadal wynosi 100%. Możemy również dodawać inne fragmentatory i filtry. Nasza miara % łącznej sprzedaży zawsze daje procent łącznej sprzedaży, niezależnie od zastosowanych fragmentatorów i filtrów. W przypadku miar wynik jest zawsze obliczany zgodnie z kontekstem określonym przez pola w kolumnach KOLUMNY i WIERSZE oraz przez zastosowane filtry i fragmentatory. Na tym polega siła środków.

Oto kilka wskazówek ułatwiających określenie, czy kolumna obliczeniowa, czy miara jest odpowiednia dla konkretnych potrzeb obliczeniowych:

Używanie kolumn obliczeniowych

• Jeśli chcesz, aby nowe dane były wyświetlane w tabeli przestawnej w WIERSZACH, KOLUMNACH lub w FILTRACH albo na osi, legendzie lub kafelku według wizualizacji programu Power View, musisz użyć kolumny obliczeniowej. Podobnie jak zwykłe kolumny danych, kolumny obliczeniowe mogą być używane jako pola w dowolnym obszarze, a jeśli są liczbowe, również mogą być agregowane w WARTOŚCI.
• Jeśli chcesz, aby nowe dane były stałą wartością wiersza. Załóżmy na przykład, że masz tabelę dat z kolumną dat, a w kolejnej chcesz, aby kolejna kolumna zawierała tylko numer miesiąca. Można utworzyć kolumnę obliczeniową, która oblicza tylko numer miesiąca na podstawie dat w kolumnie Data. Na przykład =MONTH('Date'[Date]).
• Aby dodać wartość tekstową do każdego wiersza tabeli, użyj kolumny obliczeniowej. Pól zawierających wartości tekstowe nie można agregować w WARTOŚCI. Na przykład funkcja =FORMAT('Date'[Date],"mmmm") zawiera nazwy miesięcy dla każdej daty w kolumnie Date w tabeli Date.

Korzystanie z miar

• Czy wynik obliczeń będzie zawsze zależny od innych pól wybranych w tabeli przestawnej.
• Jeśli musisz wykonać bardziej złożone obliczenia, na przykład obliczyć liczbę na podstawie pewnego rodzaju filtru lub obliczyć wariancję rok do roku lub wariancję, użyj pola obliczeniowego.
• Jeśli chcesz ograniczyć rozmiar skoroszytu do minimum i zmaksymalizować jego wydajność, utwórz jak najwięcej obliczeń jako miar. W wielu przypadkach wszystkie obliczenia mogą być miarami, co znacznie zmniejsza rozmiar skoroszytu i przyspiesza czas odświeżania.

Pamiętaj, że nie ma nic złego w tworzeniu kolumn obliczeniowych, tak jak w przypadku kolumny Zysk, a następnie agregowaniu ich w tabeli przestawnej lub raporcie. Jest to naprawdę dobry i łatwy sposób na poznanie i utworzenie własnych obliczeń. W miarę pogłębiania się wiedzy na temat tych dwóch niezwykle zaawansowanych funkcji dodatku Power Pivot warto utworzyć możliwie najwydajniejszy i najdokładniejszy model danych. Mam nadzieję, że to, czego się tutaj nauczyłeś, pomoże. Istnieje kilka innych naprawdę świetnych zasobów, które mogą Ci pomóc. Oto tylko kilka z nich: Kontekst w formułach języka DAX, Agregacje w dodatku Power Pivot i Centrum zasobów języka DAX. Przykład Modelowanie i analiza danych zysków i strat za pomocą dodatku Microsoft Power Pivot w programie Excel jest nieco bardziej zaawansowany i przeznaczony dla specjalistów w dziedzinie rachunkowości i finansów, ale zawiera mnóstwo przykładów znakomitych modelowania danych i formuł.