Power Pivot #9 – CALCULATE funksiyası (DAX) ilə hesabatlarda sərhədsiz imkanlar

Power Analytics

Power Pivot-da ən önəmli 2 funksiya varsa, onlardan biri, heç şübhəsiz, CALCULATE funksiyasıdır. Excel-də olmayan bu funksiya, DAX-ın əsasını təşkil edən filter kontekstini “dəlməyə” yarayır. Belə ki, hər hansı bir measure üçün filter vəzifəsi görməkdə, və təyin edəcəyimiz kriteriyalara uyğun measure-ı şəkilləndirməkdədir. Sintaksı aşağıdakı kimidir:

Picture1.png

Funksiya əsas 2 parametrdən ibarətdir:

Expression: Demək olar ki, bütün hallarda bu, yazılan hər hansı bir Measure olacaq.

Filter1, Filter2, … FilterN: Measure-ı fitlerləməyə yarayan kriteriyalar.

Excel-dəki SUMIFS funksiyası kimi düşünə bilərsiniz. Ən başda cəmləyəcəyimiz sütunu seçirdik, daha sonra 250-dən çox kriteriya seçmə imkanımız olurdu. Eynilə burda da eyni məntiq işləyir. Öncə filterləyəcəyimiz Measure, daha sonra onu filterləyəcək kriteriyalar (DAX-da SUMIFS funksiyası, anlayacağımız üzərə, yoxdur).

Daha öncə SUMX funksiyamız ilə ümumi satışları hesablayıb, pivot cədvəlimizə atmışdıq (Daha ətraflı – Power Pivot #7 – SUMX funksiyası (DAX)).

Picture4.png

Əlavə olaraq, illəri Slicer-a qoyub, 2018-ci ilə aid satış rəqəmlərini görə bilərik. Hər bir şirkətin satışının 2018-ci ildəki ümumi satış içindəki faiz nisbətini hesablamaq üçün CALCULATE bizə kömək edəcəkdir. Öncəliklə, ancaq 2018-ci ilə aid satış məbləğini tapmalıyıq, daha sonra yuxarıdakı individual satışları bu ümumi məbləğə bölərək, faiz nisbətlərini tapacağıq. Bunun üçün, Sales of 2018 adlı yeni bir measure yazmalıyıq:

Untitled Project3f67fe.autosave.gif

Yazdığımız formulu komponentlərinə bölək:

Picture15.png

Yuxarıdakı formulun sətir nömrələri ilə getsək, Power Pivot-a dedik ki,

  1. Yeni measure-ımın adı – “Sales of 2018“-dir.
  2. CALCULATE funksiyası ilə başlayıram,
  3. Filterlənəcək measure-ım – Total Revenue-dur.
  4. SalesData[Date] sütunundakı 2018-ci illəri axtarıram, və
  5. Bu ilə aid bütün sətirlərə ehtiyacım var.

Hər hansı bir funksiyanın “davranış şəkli”nə ingilis dilində behaviour deyilir, yəni müəyyən şərtlərə necə “reaksiya verməsi”ndən bəhs edilir. CALCULATE funksiyası, o qədər rəngarəng işlər görə bilir ki, onun davranış şəklinin anlaşılması çox vacibdir. Funksiyanın sintaksında gördüyümüz expressionfilter parametrlərini xatırlasaq, bilməliyik ki, təyin etdiyimiz bütün filterlər expression hesablanmadan öncə işləməyə başlayır. Bir başqa deyişlə, öncə filterlər işə düşür, daha sonra yazdığımız expression bu filterlər əsasında şəkillənir. Məsələn, Sales of 2018 measure-ımızda öncə Total Revenue hesablanıb, daha sonra filterlər işə düşmür. Tam əksinə, öncə illər tapılır, bütün sətirlər müəyən edilir və yalnız ondan sonra ümumi satışımız bu kriteriyaya əsasən “yonulur”.

Formulumuzu ingilis dilində cümlə kimi oxusaq, ortaya belə bir ifadə çıxacaq:

“Calculate total revenue, for all lines where the year is equal to 2018”.

Bizim dilimizdə:

“İli 2018 olan bütün sətirlərin gəlirlərini cəmlə”.

Yazdığımız yeni measure – pivot cədvəlimizdə anında əks olunacaqdır.

Picture7.png

Onu, pivot cədvəlimizə əlavə edib, nəyi necə hesabladığına baxaq:

Picture3.png

Slicer-da hər 3 il seçilməsinə baxmayaraq, Sales of 2018 sütunu ancaq 2018-ci ilə aid satışları göstərir. Slicer-dan 2017-ni seçsək, ikinci sütun yenə dəyişməyəcək.

Picture4.png

Bu, daha əvvəlki mövzularda toxunduğumuz kimi, filter kontekstinin müəyyən DAX filterləmə funksiyaları ilə (CALCULATE, ALL, ALLSELECTED, FILTER., CALCULATETABLE və s.) pivot cədvəl və slicer kriteriyalarından yayınmaqdır. Çünki, normalda yuxarıdakı pivot cədvəl və slicer-da 2 fərqli kriteriya var və məbləğlər bu kriteriyalar əsasında formalaşıb: il və şirkət. Lakin, CALCULATE funksiyası ilə biz hər 2 kriteriyanı yan keçdik.

Nəhayət, 2018-ci ilə aid şirkətlərin faiz nisbətlərini tapaq:

Untitled Project3f67fe.autosave.gif

% of total adlı yeni bir measure yazmış olduq. Pivot cədvəlimizə əlavə edək.

Picture5.png

CALCULATE ilə başqa işlər də görə bilərik. Məsələn, elə bir measure yaza bilərik ki, Slicer-da hansı il seçilirsə seçilsin, bizim measure həmin ilin cəmi satışını və həmin ilə aid faiz nisbətlərini hesablasın.

Bunu etmək üçün ALLSELECTED adlı yeni funksiya ilə tanış olmalıyıq.

Picture13.png

ALLSELECTED – mövcud bütün filter kontekstlərini silməklə bərabər, bizim vermiş olduğumuz filterləri saxlamağa yarayır. Funksiyanın daxilinə ya cədvəl adı, ya da sütun adı daxil edilməlidir. Ayrı-ayrılıqda hər hansı bir məna ifadə etmir, daha çox digər funksiyalarla birgə işləyir. Excel-dəki AND, OR funksiyaları kimi.

Picture14.png

Məsələn, yuxarıdakı kimi bir formul yazsaq, ALLSELECTED funksiyası pivot cədvəlin təyin etdiyi kriteriyaları yox saymaqla bərabər (çünki mövcud filter konteksti daxilindədir), slicer ilə müəyyən edilmiş bütün filterlərə tabe olacaqdır (çünki bizim vermiş olduğumuz əlavə filterdir).

Slicer-dakı illəri dəyişməklə, ancaq həmin ilə aid ümumi satış məbləğini hesabladığımızı görə bilərsiniz:

Picture17.png

Biz Slicer ilə hansısa ili seçdiyimiz üçün yeni filter konteksti yaranmışdır və ALLSELECTED SalesData cədvəlimizdə bu filterə uyğun bütün sətirləri aktivləşdirmişdir, bu filterə uymayan digər sətirlər isə iqnor edilmişdir.

Əslində ALLSELECTED-in bizə verdiyi mesaj qısaca belədir – nəzərdə tutulan bütün filterləri iqnor etməklə yanaşı, sənin seçdiyin filterlərə tabe olacağam.

Məsələn, Slicer-da 2 il eyni anda seçilmiş də olsa, ALLSELECTED, hər 2 ilin cəm satışını hesablayacaqdır, çünki nəzərdə tutulan filter kontekstinə yox, bizim müəyyən edəcəyimiz kontekstə özünü uyğunlaşdıracaqdır.

Faiz nisbətlərini hesablamaq isə, aşağıdakı formulla həyata keçəcəkdir:

Picture19.png

Növbəti yazımızda, ALLSELECTEDALL funksiyaları arasındakı fərqlərdən bəhs edəcək və pivot cədvəlimiz üzərində eyni filterləmə tətbiq etdiyimiz zaman bu 2 funksiyanın bir-birindən necə fərqli davranış sərgilədiyini müşahidə edəcəyik – Power Pivot #10 – Filter konteksti ilə necə oynaya bilərik?! – ALLSELECTED vs. ALL