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

Podczas pierwszej nauki korzystania z dodatku Power Pivot większość użytkowników odkrywa, że rzeczywista moc polega na agregowaniu lub obliczaniu wyniku w jakiś sposób. Jeśli dane zawierają kolumnę z wartościami liczbowymi, możesz je łatwo zagregować, wybierając ją w tabeli przestawnej lub na liście pól programu Power View. Z natury dane liczbowe są automatycznie sumowane, uśredniane, liczone lub uwzględniane niezależnie od wybranego typu agregacji. Jest to nazywane miarą pośrednią. Miary niejawne doskonale nadają się do szybkiego i łatwego agregacji, ale mają ograniczenia, a limity te można niemal zawsze przezwyciężyć za pomocą jawnych miar i kolumn obliczeniowych.

Przyjrzyjmy się najpierw przykładowi, w którym za pomocą kolumny obliczeniowej dodajemy nową wartość tekstową dla każdego wiersza w tabeli o nazwie Produkt. Każdy wiersz w tabeli Produkt zawiera różnego rodzaju informacje na temat każdego sprzedawanego produktu. Mamy kolumny nazwa produktu, kolor, rozmiar, cena dealera, itp. Mamy inną powiązaną tabelę o nazwie Kategoria produktu zawierającą kolumnę NazwaKategoriiProduktu. 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 następującym przykładzie:

Nowa formuła Kategoria produktu używa funkcji RELATED JĘZYKA DAX do uzyskiwania wartości z kolumny NazwaKategoriiProduktu 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ść dla każdego wiersza, którego można użyć później w obszarze WIERSZE, KOLUMNY lub FILTRY w 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ż zawierającą dane transakcji oraz relację 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 następującym przykładzie:

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

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

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

To, co zrobiliśmy, to utworzenie kolumny o nazwie Zysk, która oblicza marżę zysku dla każdego wiersza w tabeli Sprzedaż. Następnie dodaliśmy funkcję 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ę dwa razy obliczyliśmy zysk dla naszych kategorii produktów, masz rację. Najpierw obliczyliśmy zysk dla każdego wiersza w tabeli Sprzedaż, a następnie dodaliśmy pozycję Zysk do obszaru WARTOŚCI, w którym został on zagregowany dla każdej kategorii produktów. Jeśli myślisz też, że tak naprawdę nie trzeba tworzyć kolumny obliczeniowej Zysk, również masz rację. Ale jak wtedy obliczamy nasz zysk bez tworzenia kolumny obliczeniowej Zysk?

Zysk, naprawdę lepiej byłoby obliczyć jako wyraźny środek.

Na razie pozostawimy kolumnę obliczeniową Zysk w tabeli Sprzedaż i Kategorie produktów w kolumnach i zyskach w tabeli przestawnej, aby porównać wyniki.

W obszarze obliczeń tabeli Sprzedaż utworzymy miarę o nazwie Total Profit (aby uniknąć konfliktów nazw). Ostatecznie przyniesie to takie same wyniki jak wcześniej, ale bez kolumny obliczeniowej Zysk.

Najpierw w tabeli Sprzedaż zaznaczamy kolumnę KwotaSprzedaży, a następnie klikamy pozycję Autosumowanie, aby utworzyć jawną miarę Sum of SalesAmount. Pamiętaj, że miarą jawną jest taka, którą tworzymy w obszarze obliczeń tabeli w dodatku Power Pivot. Robimy to samo dla kolumny COGS. Zmienimy nazwy tych całkowitych wartości sprzedaży i sumy cogs, aby ułatwić ich identyfikację.

Następnie utworzymy kolejną miarę z tą formułą:

Zysk całkowity:=[ Łączna kwota sprzedaży] — [Łączna wartość COGS]

Po zmianie formatu miary łącznego zysku na walutowy możemy dodać go do tabeli przestawnej.

Nowa miara całkowitego zysku zwraca te same wyniki co utworzenie kolumny obliczeniowej Zysk, a następnie umieszczenie jej w kolumnie WARTOŚCI. Różnica polega na tym, że miara całkowitego zysku jest znacznie bardziej efektywna i sprawia, że nasz model danych jest bardziej przejrzysty i szczuplejszy, ponieważ w tym czasie obliczamy i tylko dla pól wybranych dla naszej tabeli przestawnej. W końcu nie potrzebujemy tej kolumny obliczeniowej Zysk.

Dlaczego ta ostatnia część jest ważna? Kolumny obliczeniowe dodają dane do modelu danych, a dane zajmują pamięć. W przypadku odświeżenia modelu danych do ponownego obliczenia wszystkich wartości w kolumnie Zysk są również potrzebne zasoby przetwarzania. Nie musimy zajmować takich zasobów, ponieważ naprawdę chcemy obliczyć zysk po wybraniu pól, dla których chcemy uzyskać zysk w tabeli przestawnej, takich jak kategorie produktów, regiony lub daty.

Przyjrzyjmy się innemu przykładowi. 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 sprzedaży całkowitej. W tabeli Sprzedaż tworzymy kolumnę obliczeniową o nazwie % sprzedaży , w następującym przykładzie:

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

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

Ok. Do tej pory wygląda to dobrze. Dodajmy jednak fragmentator. Dodajemy rok kalendarza, a następnie wybieramy rok. W takim przypadku wybieramy rok 2007. To jest to, co dostajemy.

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

Nasza kolumna % sprzedaży obliczyła procent dla każdego wiersza, który jest wartością w kolumnie KwotaSprzedaży podzieloną przez sumę wszystkich wartości w kolumnie KwotaSprzedaży. Wartości w kolumnie obliczeniowej są stałe. Są one niezmienialnym wynikiem dla każdego wiersza w tabeli. Po dodaniu % sprzedaży do tabeli przestawnej została ona zagregowana jako suma wszystkich wartości w kolumnie KwotaSprzedaży. Suma wszystkich wartości w kolumnie % sprzedaży zawsze będzie wynosić 100%.

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

Pamiętasz utworzoną wcześniej miarę TotalSalesAmount, która po prostu sumuje kolumnę KwotaSprzedaży? Użyto go jako argumentu w miarę całkowitego zysku i użyjemy jej ponownie jako argumentu w nowym polu obliczeniowym.

Tworzymy nową miarę z następującą formułą:

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

Ta formuła zawiera następujące stany: Podziel wynik z sumy KwotaSprzedaży przez sumę wartości SalesAmount bez filtrów kolumn i wierszy innych niż zdefiniowane w tabeli przestawnej.

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

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 RokKalendarzowy, otrzymamy nowe wartości procentowe dla naszych kategorii produktów, ale suma końcowa nadal wynosi 100%. Możemy również dodać inne fragmentatory i filtry. Nasza miara % całkowitej sprzedaży zawsze spowoduje uzyskanie procentu całkowitej sprzedaży niezależnie od zastosowanych fragmentatorów lub filtrów. Przy użyciu miar wynik jest zawsze obliczany na podstawie kontekstu określonego przez pola w kolumnach i wierszach oraz na podstawie wszelkich zastosowanych filtrów lub fragmentatorów. To jest siła miar.

Poniżej przedstawiono kilka wskazówek ułatwiających określenie, czy kolumna obliczeniowa lub miara jest odpowiednia dla określonych potrzeb w obliczeniach:

Używanie kolumn obliczeniowych

• Aby nowe dane były wyświetlane w wierszach, kolumnach lub filtrach w tabeli przestawnej albo na osi, legendzie lub kafelku WEDŁUG w wizualizacji programu Power View, należy użyć kolumny obliczeniowej. Podobnie jak w przypadku zwykłych kolumn danych, kolumny obliczeniowe mogą być używane jako pole w dowolnym obszarze, a jeśli są liczbowe, mogą być agregowane również w wartościach.

• Jeśli chcesz, aby nowe dane były stałą wartością wiersza. 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 oblicza tylko liczbę miesięcy na podstawie dat w kolumnie Data. Na przykład =MIESIĄC('Data'[Data]).

• Jeśli chcesz dodać wartość tekstową dla każdego wiersza do tabeli, użyj kolumny obliczeniowej. Pól z wartościami tekstowymi nie można agregować w wartościach. Na przykład formuła =FORMAT('Date'[Date]"mmmm") podaje nazwę miesiąca dla każdej daty w kolumnie Data w tabeli Data.

Stosowanie miar

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

• Jeśli musisz wykonywać bardziej złożone obliczenia, takie jak obliczanie liczby na podstawie filtru sortowania, obliczanie rok do roku lub wariancja, użyj pola obliczeniowego.

• Jeśli chcesz jak najmniej zwiększyć rozmiar skoroszytu i zmaksymalizować jego wydajność, utwórz jak najwięcej obliczeń. 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 jej w tabeli przestawnej lub raporcie. Jest to naprawdę dobry i łatwy sposób na poznanie własnych obliczeń i utworzenie ich. Wraz ze wzrostem zrozumienia tych dwóch niezwykle zaawansowanych funkcji dodatku Power Pivot warto utworzyć najefektywniejszy i najdokładniejszy model danych. Mam nadzieję, że to, czego się tutaj nauczyłeś, pomoże. Są też inne naprawdę wspaniałe zasoby, które mogą Ci pomóc. Oto tylko kilka z nich: Kontekst formuł języka DAX, agregacji w dodatku Power Pivot i Centrum zasobów języka DAX. I choć jest nieco bardziej zaawansowana i jest skierowana do specjalistów w dziedzinie księgowości i finansów, przykładowe modelowanie i analizy danych zysków i strat z dodatkiem Microsoft Power Pivot w programie Excel jest ładowane z doskonałym modelowaniem danych i przykładami formuł.