Excel Power Pivot - podstawy języka DAX
DAX (Data Analysis eXpression)język to język dodatku Power Pivot. Język DAX jest używany przez dodatek Power Pivot do modelowania danych i jest wygodny w użyciu do samoobsługowej analizy biznesowej. Język DAX jest oparty na tabelach danych i kolumnach w tabelach danych. Zauważ, że nie jest on oparty na pojedynczych komórkach w tabeli, jak ma to miejsce w przypadku formuł i funkcji w programie Excel.
W tym rozdziale poznasz dwa proste obliczenia, które istnieją w modelu danych - kolumna obliczeniowa i pole obliczeniowe.
Kolumna obliczeniowa
Kolumna obliczeniowa to kolumna w modelu danych, która jest zdefiniowana przez obliczenia i która rozszerza zawartość tabeli danych. Można ją wizualizować jako nową kolumnę w tabeli programu Excel zdefiniowanej przez formułę.
Rozszerzanie modelu danych za pomocą kolumn obliczeniowych
Załóżmy, że masz dane dotyczące sprzedaży produktów według regionu w tabelach danych, a także Katalog produktów w modelu danych.
Utwórz tabelę Power Pivot z tymi danymi.
Jak widać, w tabeli Power PivotTable podsumowano dane dotyczące sprzedaży ze wszystkich regionów. Załóżmy, że chcesz poznać zysk brutto osiągnięty na każdym z produktów. Znasz cenę każdego produktu, koszt jego sprzedaży i liczbę sprzedanych sztuk.
Jeśli jednak chcesz obliczyć zysk brutto, musisz mieć dwie dodatkowe kolumny w każdej z tabel danych regionów - Całkowita cena produktu i Zysk brutto. Dzieje się tak, ponieważ tabela przestawna wymaga kolumn w tabelach danych, aby podsumować wyniki.
Jak wiesz, całkowita cena produktu to cena produktu * liczba jednostek, a zysk brutto to całkowita kwota - całkowita cena produktu.
Należy użyć wyrażeń języka DAX, aby dodać kolumny obliczeniowe w następujący sposób -
Kliknij kartę East_Sales w widoku danych okna dodatku Power Pivot, aby wyświetlić tabelę danych East_Sales.
Kliknij kartę Projekt na Wstążce.
Kliknij Dodaj.
Podświetlona jest kolumna po prawej stronie z nagłówkiem - Dodaj kolumnę.
Wpisz = [Product Price] * [No. of Units] na pasku formuły i naciśnij Enter.
Nowa kolumna z nagłówkiem CalculatedColumn1 jest wstawiany z wartościami obliczonymi na podstawie wprowadzonej formuły.
Kliknij dwukrotnie nagłówek nowej kolumny obliczeniowej.
Zmień nazwę nagłówka na TotalProductPrice.
Dodaj jeszcze jedną kolumnę obliczeniową dla Zysk brutto w następujący sposób -
Kliknij kartę Projekt na Wstążce.
Kliknij Dodaj.
Podświetlona jest kolumna po prawej stronie z nagłówkiem - Dodaj kolumnę.
Wpisz = [TotalSalesAmount] − [TotaProductPrice] na pasku formuły.
Naciśnij enter.
Nowa kolumna z nagłówkiem CalculatedColumn1 jest wstawiany z wartościami obliczonymi na podstawie wprowadzonej formuły.
Kliknij dwukrotnie nagłówek nowej kolumny obliczeniowej.
Zmień nazwę nagłówka na Zysk brutto.
Dodaj obliczone kolumny w North_Salestabelę danych w podobny sposób. Konsolidując wszystkie kroki, wykonaj następujące czynności -
Kliknij kartę Projekt na Wstążce.
Kliknij Dodaj. Podświetlona jest kolumna po prawej stronie z nagłówkiem - Dodaj kolumnę.
Wpisz = [Product Price] * [No. of Units] na pasku formuły i naciśnij Enter.
Nowa kolumna z nagłówkiem CalculatedColumn1 zostanie wstawiona z wartościami obliczonymi według wprowadzonej formuły.
Kliknij dwukrotnie nagłówek nowej kolumny obliczeniowej.
Zmień nazwę nagłówka na TotalProductPrice.
Kliknij kartę Projekt na Wstążce.
Kliknij Dodaj. Podświetlona jest kolumna po prawej stronie z nagłówkiem - Dodaj kolumnę.
Wpisz = [TotalSalesAmount] − [TotaProductPrice]na pasku formuły i naciśnij Enter. Nowa kolumna z nagłówkiemCalculatedColumn1 zostanie wstawiony z wartościami obliczonymi według wprowadzonej formuły.
Kliknij dwukrotnie nagłówek nowej kolumny obliczeniowej.
Zmień nazwę nagłówka na Gross Profit.
Powtórz powyższe kroki dla tabeli danych South Sales i tabeli danych West Sales.
Masz niezbędne kolumny, aby podsumować zysk brutto. Teraz utwórz tabelę Power PivotTable.
Jesteś w stanie podsumować Gross Profit stało się to możliwe dzięki kolumnom obliczeniowym w dodatku Power Pivot, a wszystko to można wykonać w kilku bezbłędnych krokach.
Możesz podsumować to pod względem regionu dla produktów, jak podano poniżej również -
Pole obliczeniowe
Załóżmy, że chcesz obliczyć procent zysku osiągniętego przez każdy region z punktu widzenia produktu. Możesz to zrobić, dodając pole obliczeniowe do Tabeli danych.
Kliknij poniżej kolumny Zysk brutto w East_Sales tabela w oknie Power Pivot.
Rodzaj EastProfit: = SUM ([Gross Profit]) / sum ([TotalSalesAmount]) na pasku formuły.
Naciśnij enter.
Pole obliczeniowe EastProfit jest wstawiane poniżej kolumny Zysk brutto.
Kliknij prawym przyciskiem myszy pole obliczeniowe - EastProfit.
Wybierz Format z listy rozwijanej.
Pojawi się okno dialogowe Formatowanie.
Wybierz Number w kategorii.
W polu Format wybierz Procent i kliknij OK.
Pole obliczeniowe EastProfit jest sformatowane jako procent.
Powtórz kroki, aby wstawić następujące pola obliczeniowe -
NorthProfit w tabeli danych North_Sales.
SouthProfit w tabeli danych South_Sales.
WestProfit w tabeli danych West_Sales.
Note - Nie można zdefiniować więcej niż jednego pola obliczeniowego o podanej nazwie.
Kliknij tabelę Power PivotTable. Możesz zobaczyć, że pola obliczeniowe pojawiają się w tabelach.
Wybierz pola - EastProfit, NorthProfit, SouthProfit i WestProfit z tabel na liście PivotTable Fields.
Rozmieść pola w taki sposób, aby Zysk brutto i Zysk procentowy pojawiały się razem. Tabela Power Pivot wygląda następująco -
Note - The Calculate Fields zostali wezwani Measures we wcześniejszych wersjach programu Excel.