Xatırlayırsınızsa, Power Pivot layihəmizdə 3 cədvəl var idi: SalesData, Products və Customers. Bundan əlavə, Products cədvəlimizdə, məhsulların satış qiymətləri ilə, maya dəyərləri yer alan sütunlarımız var idi.
Bu dəfə, maya dəyərləri əsasında həm ümumi COGS (Cost of Goods Sold), həm də filter konteksti daxilində hər bir məhsulun gəlirliliyini hesablayacağıq. Bunu etmək üçün, öncə məsələni bəsit yoldan həll edəcək, daha sonra işi bir az qəlizləşdirib daha effektiv metodu görəcəyik.
Gəlirliliyin hesablanması – Calculated Column ilə
Məsələyə, hər bir məhsulun maya dəyəri sütununu hazırlamaqla başlayırıq. RELATED ilə bağlı mövzumuzda, bunun yolunu öyrənmişdik (Daha ətraflı – Power Pivot #6 – RELATED funksiyası (DAX)). Eyni məntiqlə, maya dəyərlərini də əsas cədvəlimizə əlavə edirik. SalesData cədvəlində yeni Calculated Column açdıqda sonra həmin sütuna aşağıdakı kimi bir formul yazırıq:
Bununla da, hər məhsulumuzun qarşısına, Products cədvəlindəki müvafiq [Cost per unit] dəyərlərini gətirmiş oluruq. Artıq, yeni measure yazma zamanı gəldi, və bu measure – Total COGS adlanacaq.
SalesData cədvəlindəki hər bir [Units sold] və [Cost per unit] sütunlarındakı dəyərləri bir-birinə vuraraq, SUMX ilə hər birini cəmləmiş oldu.
2 yeni measure yazaraq, Gross Profit və Gross Profit % hesablayaq:
Elementar çıxma əməliyyatı ilə Ümumi Gəlirdən Ümumi Maya Dəyərini çıxdıq. Yekunda isə, sadə bölmə əməliyyatı ilə Gross Profit-i Total Revenue-ya bölürük.
Yazdığımız bütün yeni measure-ları pivot cədvəlimizə əlavə edirik:
Müəyyən edilmiş 3 filter konteksti daxilində (məhsul, il və ay), müvafiq məhsullar üzrə müvafiq dəyərləri görürük. Filterlərimizi dəyişsək, dəyərlər də dinamik olaraq dəyişəcəkdir.
Gəlirliliyin hesablanması – Measure ilə
Bu dəfə, heç bir əlavə sütun qurmadan, ancaq Measure ilə məsələni həll edəcəyik. Qarşımızdakı məqsəd – Total COGS hesablamaqdır. Öncə yazmalı olduğumuz measure formulunu göstərib, daha sonra izahını paylaşacağam:
Yeni measure-ın adı Total COGS2-dir. DAX formulunun komponentlərinə baxaq:
Əslində, 2 fərqli formul yazmaq əvəzinə, SUMX ilə RELATED funksiyaları kombinə etdik və dedik ki,
- SalesData cədvəlindəki – [Units sold] sütunundakı dəyəri tap,
- SalesData cədvəlindəki Product dəyərinə müvafiq olan Products cədvəlindəki [Cost per unit] dəyərini tap,
- Bu iki dəyərini bir-birinə hər bir sətirdə vur,
- SUMX ilə vurulan bütün dəyərləri cəmlə.
Formulun fundamental komponenti – 2-ci maddədəki RELATED ilə işləməsidir. Bunun yaxşı şəkildə anlaşılması zəruridir, əks təqdirdə, növbəti mövzularda öyrənəcəyimiz RELATEDTABLE mövzusu heç anlaşılmayacaqdır.
Xatırlayırsınızsa, cədvəllərarası əlaqə qurarkən demişdik ki, Excel-dəki kimi VLOOKUP yazmaq əvəzinə, DAX-da cədvəllərarası əlaqələr (relationships) konsepsiyası mövcuddur (Daha ətraflı – Power Pivot #5 – Cədvəllərarası əlaqələrin qurulması). Bizim misalda da, bu əlaqə 2 cədvəl arasında mövcuddur: SalesData cədvəli ilə Products cədvəli arasında (Power Pivot >> Diagram View):
Bu əlaqə olduğuna görə, istənilən cədvəlin içində ikən, digər bir cədvələ istinad vermək mümkündür. Və bu istinad, one-to-many əlaqələr arasında RELATED funksiyası ilə tənzimlənir.
Biz “= RELATED ( Products[Cost per unit] )” formulunu yazdığımız anda, sistem həmin cədvəldəki, həmin sütundakı dəyəri axtarmağa başlayır. Hər şey Row konteksti daxilində baş verdiyi üçün də, SalesData cədvəlinin hər sətirindəki [Product ID] dəyəri ilə, RELATED ilə tapdığımız Products cədvəlimizin [Product ID] dəyərləri müqayisə edilərək, [Cost per unit] sütunundakı hər bir müvafiq dəyər SalesData cədvəlimizə “transfer” edilmiş olur. Eynilə, yuxarıda yazdığımız DAX formulunda da, [Product ID]-lər bir-birinə bağlı olduğuna görə, bir cədvəldən digərinə “atlayıb”, ordakı istədiyimiz dəyəri gətirmə imkanına sahibik. Bu əlaqə, fiziki olaraq qurulmamış olsaydı, formul xəta mesajı verəcəkdi.
Artıq, Calculated Column-u silə bilərik, çünki onu əvəz edən measure yazdıq. Çıxılmaz vəziyyət yaranmadığı müddətcə, Calculated Column yazmaqdan hər zaman imtina edin, həm faylımızın həcmini böyüdür, həm də DAX-ın effektiv hesablama qabiliyyətini yavaşladır. Faylımızın son halını burdan endirə bilərsiniz – 13. Power Pivot – SUMX & RELATED.
Calculated column-ları silmədim, işləyişinə baxa biləsiniz deyə.
Növbəti yazımızda, Excel-dəki SUMIF funksiyasının bir növ DAX ekvivalentlərindən olan RELATEDTABLE funksiyasından bəhs edəcəyik – Power Pivot #14 – SUMIF funksiyasının DAX-dakı ekvivalenti – RELATEDTABLE