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

Gdy po raz pierwszy dowiedziesz się, jak używać dodatku Power Pivot, większość użytkowników odkrywa prawdziwe potęgi, agregując lub obliczając wynik w jakiś sposób. Jeśli dane mają kolumnę zawierającą wartości liczbowe, możesz ją łatwo agregować, wybierając ją w tabeli przestawnej lub wybierając Power View pola. Z natury jest to wartość liczbowa, więc będzie ona automatycznie sumowana, uśredniona, zliczona lub niezależnie od wybranego typu agregacji. Jest to nazywane miarą niejawną. Niejawne miary są doskonałe do szybkiej i łatwej agregacji, ale mają limity, a te limity prawie zawsze można pokonać za pomocą jawnych miar ikolumn obliczeniowych.

Najpierw przyjrzyjmy się przykładowi, w którym za pomocą kolumny obliczeniowej dodaję nową wartość tekstową dla każdego wiersza w tabeli o nazwie Produkt. Każdy wiersz w tabeli Produkt zawiera wszelkiego rodzaju informacje na temat każdego sprzedanego przez nas produktu. Mamy kolumny Nazwa produktu, Kolor, Rozmiar, Cena itd. Mamy inną powiązaną tabelę o nazwie Kategoria produktu, która zawiera kolumnę NazwaKategorii_Produktu. Chcemy, aby każdy produkt w tabeli Produkt zawierał nazwę kategorii produktu z tabeli Kategoria produktu. W tabeli Produkt można utworzyć kolumnę obliczeniową o nazwie Kategoria produktu w ten sposób:

Nowa formuła Kategoria produktu używa funkcji RELATED JĘZYKA DAX w celu uzyskania wartości z kolumny NazwaKategoriiKategorii Produktów w powiązanej tabeli Kategoria produktu, a następnie wprowadza te wartości dla każdego produktu (każdego wiersza) w tabeli Produkt.

Jest to doskonały przykład tego, jak za pomocą kolumny obliczeniowej można dodać stałą wartość do każdego wiersza, a następnie użyć jej w obszarze WIERSZE, KOLUMNY lub FILTRY w tabeli przestawnej lub w raporcie Power View przestawnego.

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

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 ten sposób:

Teraz możemy 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 programie PowerPivot to pole na liście pól tabeli przestawnej). Wynikiem jest niejawna miara o nazwie Suma Zysk. Jest to zagregowana ilość wartości z kolumny zysku dla poszczególnych kategorii produktów. Wynik wygląda następująco:

W tym przypadku Zysk jest sensowny tylko jako pole w wartościach. Gdyby umieszczać tabelę przestawną Zysk w obszarze LICZBA.KOLUMN, tabela przestawna byłaby wyglądać tak:

Pole Zysk nie zawiera żadnych przydatnych informacji, gdy jest umieszczone w obszarach KOLUMNY, WIERSZE lub FILTRY. Sensowna jest tylko zagregowana wartość w obszarze WARTOŚCI.

Utworzyliśmy kolumnę o nazwie Zysk, która oblicza marżę zysku dla każdego wiersza w tabeli Sprzedaż. Następnie dodano zysk do obszaru WARTOŚCI naszej tabeli przestawnej, automatycznie tworząc miarę niejawną, gdzie wynik jest obliczany dla każdej kategorii produktu. Jeśli myślisz, że dwukrotnie obliczyliśmy zysk dla naszych kategorii produktów, poprawność jest poprawna. Najpierw obliczyliśmy zysk dla każdego wiersza w tabeli Sprzedaż, a następnie dodaliśmy Zysk do obszaru WARTOŚCI, gdzie był agregowany dla każdej kategorii produktów. Poprawne jest też to, jeśli myślisz, że nie trzeba tworzyć kolumny obliczeniowej Zysk. Jak jednak obliczyć zysk bez tworzenia kolumny obliczeniowej Zysk?

Zysk, w naprawdę lepiej będzie obliczany jako miara jawna.

Na razie pozostawimy kolumnę obliczeniową Zysk w tabeli Sprzedaż i Kategorię produktu w kolumnach i Zysk w WARTOŚCIach tabeli przestawnej, aby porównać nasze wyniki.

W obszarze obliczeń w tabeli Sprzedaż utworzymy miarę o nazwie Całkowityzysk (aby uniknąć konfliktów nazewnictwa). W końcu spowoduje uzyskanie tych samych wyników, co wcześniej, ale bez kolumny obliczeniowej Zysk.

Najpierw w tabeli Sprzedaż wybieramy kolumnę KwotaSprzedazy, a następnie klikamy przycisk Autosumowanie, aby utworzyć jawną sumę miary KwotaSprzedazy. Pamiętaj, że miarą jawną jest tworzymy w obszarze obliczeń tabeli w dodatku Power Pivot. W przypadku kolumny coGS zrobimy to samo. Zmienimy nazwy tych działów Total SalesAmount i Total COGS, aby ułatwić ich identyfikowanie.

Następnie utworzymy kolejną miarę za pomocą tej formuły:

Łączny zysk:=[ Łączna kwotaSprzedaży] - [Łączne COGS]

Po zmianie formatu nowej miary Zysk całkowity na walutę można dodać ten format do tabeli przestawnej.

Widać, że nowa miara Zysk całkowity zwraca te same wyniki, co utworzenie kolumny obliczeniowej Zysk i umieszczenie jej w wartościach. Różnica polega na tym, że miara Zysk całkowity jest o wiele bardziej wydajna i sprawia, że nasz model danych jest bardziej przejrzysty i uchuplejony, ponieważ dokonujemy obliczeń w czasie i tylko dla pól wybranych dla naszej tabeli przestawnej. Ta kolumna obliczeniowa Zysk nie jest w końcu potrzebna.

Dlaczego ta ostatnia część jest ważna? Kolumny obliczeniowe dodają dane do modelu danych, a dane pobierają pamięć. Jeśli odświeżymy model danych, konieczne jest również przetwarzanie zasobów w celu ponownego obliczenia wszystkich wartości w kolumnie Zysk. Nie potrzebujemy tak naprawdę zasobów, ponieważ naprawdę chcemy obliczyć nasz zysk, gdy wybierzemy w tabeli przestawnej pola, dla których chcemy mieć zysk, takie jak kategorie produktów, region lub daty.

Przyjrzyjmy się kolejnej przykładowi. Takie, 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 sprzedaży całkowitej. W tabeli Sprzedaż tworzymy kolumnę obliczeniową o nazwie % sprzedaży, która wygląda tak:

Nasza formuła stanowi: dla każdego wiersza w tabeli Sprzedaż podziel kwotę w kolumnie KwotaSprzedaż przez sumę wszystkich kwot w kolumnie KwotaSprzedaż.

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

Ok. Na razie wygląda to dobrze. Dodajmy jednak fragmentator. Dodaję Rok kalendarzowy, a następnie wybierzemy rok. W tym przypadku wybierzemy rok 2007. To nam się udało.

Na pierwszy rzut oka może się to wydawać poprawne. Ale nasze procenty powinny w sumie 100%, ponieważ chcemy poznać procent całkowitej sprzedaży w poszczególnych kategoriach produktów w 2007 roku. Co zatem poszło nie tak?

W kolumnie % sprzedaży obliczono procent dla każdego wiersza, który jest wartością w kolumnie KwotaSprzedaż, podzielonej przez sumę wszystkich wartości w kolumnie KwotaSprzedaż. Wartości w kolumnie obliczeniowej są stałe. Jest to niezmienialny wynik dla każdego wiersza w tabeli. Po dodaniu do tabeli przestawnej % sprzedaży został on zagregowany jako suma wszystkich wartości w kolumnie KwotaSprzedazy. Ta suma wszystkich wartości w kolumnie % sprzedaży będzie zawsze w wysokości 100%.

Możemy usunąć kolumnę obliczeniową % sprzedaży, ponieważ nie będzie ona dla nas pomocna. Zamiast tego utworzymy miarę, która poprawnie oblicza procent całkowitej sprzedaży, niezależnie od zastosowanych filtrów i fragmentatorów.

Pamiętasz wcześniej utworzoną miarę TotalSalesAmount, czyli sumę kolumny KwotaSprzedazy? Używaliśmy jej jako argumentu w ramach naszej miary Zysk całkowity i użyjemy jej ponownie jako argumentu w nowym polu obliczeniowym.

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

% sumy sprzedaży:=([Łączna kwotaSprzedadze]) / CALCULATE([Łączna kwotaSprzedazy], ALLSELECTED())

Ta formuła zawiera stan: Podziel wynik z kolumny KwotaSprzedaż łącznie przez sumę kolumny KwotaSprzedaż bez filtrów kolumn i wierszy innych niż te zdefiniowane w tabeli przestawnej.

Teraz, jeśli dodasz do tabeli przestawnej nowy % całkowitej sprzedaży, otrzymasz:

Wygląda to 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ż rok we fragmentatorze Rok Kalendarz, będziemy omylić nowe wartości procentowe dla naszych kategorii produktów, ale suma całkowita nadal wynosi 100%. Możemy również dodać inne fragmentatory i filtry. Naszym miarą % całkowitej sprzedaży będzie zawsze procentowy udział sprzedaży całkowitej niezależnie od zastosowanych fragmentatorów i filtrów. W przypadku miar wynik jest zawsze obliczany zgodnie z kontekstem określanym przez pola w kolumnach i WIERSZAch oraz na podstawie zastosowanych filtrów lub fragmentatorów. To jest potęga miar.

Oto kilka wskazówek pomocnych przy określaniu, czy kolumna obliczeniowa lub miara jest właściwa dla określonych potrzeb obliczeniowych:

Używanie kolumn obliczeniowych

• Aby nowe dane pojawiały się w wierszach, kolumnach lub filtrach w tabeli przestawnej albo na osi, legendzie lub kafelkach według w wizualizacji programu Power View, należy użyć kolumny obliczeniowej. Kolumny obliczeniowe, podobnie jak zwykłe kolumny danych, mogą być używane jako pola w dowolnym obszarze, a jeśli są liczbowe, mogą być również agregowane w wartościach.

• Jeśli nowe dane mają mieć stałą wartość w wierszu. Na przykład masz tabelę dat z kolumną dat i chcesz, aby inna kolumna zawierała tylko liczbę miesiąca. Możesz utworzyć kolumnę obliczeniową, która będzie obliczać tylko liczbę miesięcy na podstawie dat w kolumnie Data. Na przykład =MIESIĄC('Data'[Data]).

• Jeśli chcesz dodać do tabeli wartość tekstową dla każdego wiersza, użyj kolumny obliczeniowej. Pola z wartościami tekstowych nie mogą być nigdy agregowane w obszarze WARTOŚCI. Na przykład formuła =FORMAT('Data'[Data];"mmmm") podaje nazwę miesiąca dla każdej daty w kolumnie Data w tabeli Data.

Miary użycia

• Jeśli wynik obliczeń zawsze zależy od innych pól wybranych w tabeli przestawnej.

• Jeśli musisz wykonać bardziej złożone obliczenia, takie jak obliczanie liczby na podstawie filtru sortowania albo obliczanie roku jako roku lub wariancji, użyj pola obliczeniowego.

• Jeśli chcesz utrzymać minimalny rozmiar skoroszytu i zmaksymalizować jego wydajność, utwórz jak najwięcej obliczeń jako miary. W wielu przypadkach wszystkie obliczenia mogą być miarami, znacznie zmniejszając rozmiar skoroszytu i przyspieszając czas odświeżania.

Pamiętaj, że tworzenie kolumn obliczeniowych, tak jak w przypadku kolumny Zysk, i agregowanie ich w tabeli przestawnej lub raporcie, nie stanowi nic złego. To naprawdę dobry i łatwy sposób na naukę i tworzenie własnych obliczeń. Wraz ze zrozumieniem tych dwóch bardzo zaawansowanych funkcji dodatku Power Pivot będziesz chciał utworzyć najskuteczniejszy i najdokładniejszy model danych, jaki możesz. Mamy nadzieję, że to, czego się tutaj dowiedziałeś, pomoże. Są też inne świetne zasoby, które mogą pomóc. Oto tylko kilka z nich: kontekst w formułach języka DAX,agregacjachw dodatku Power Pivot i w Centrum zasobów języka DAX. Chociaż jest nieco bardziej zaawansowane i jest kierowane do informatyków, modelowanie i analiza danych zysków i strat przy użyciu dodatku Microsoft Power Pivot w programie Excel jest ładowane z doskonałym modelowaniem danych i przykładami formuł.