SUMMARIZE ve ADDCOLUMNS

Power BI ‘da -ya da DAX ‘ta diyelim- kompleks metrikleri kolay yazmanın yolu tablo fonksiyonlarını iyi anlamaktan geçiyor. Bugüne kadar yazdığım yazılarda  bahsi geçen VALUES gibi, DISTINCT gibi, FILTER gibi, ALL ve ekürileri gibi bir çok fonksiyon bu gruba dahil.

Tablo fonksiyonu demek -basitçe- bu fonksiyonları kullandığımızda, dönen “şeyin” bir tablo olması anlamına geliyor. Dönen tablo DISTINCT veya VALUES ‘ta olduğu gibi tek sütunluk bir tablo olabilir. ALL (‘Satışlar’) diyerek orijinal tablonun tüm sütun ve satırlarını da döndürebiliriz.  Ya da LASTDATE ‘in döndürdüğü tek sütun ve tek satırdan oluşan yapı da  bir tablodur.

SUMMARIZE bize en çok lazım olabilecek tablo fonksiyonlarından biri!

SQL ‘deki Group By mantığına benziyor. Yazımı basit:

SUMMARIZE( 
           'Tablo'; 
           Gruplama Sütunu 1 ; 
           Gruplama Sütunu ..N ; 
           "Hesap Sütunu Adı" ; Formül ya da Metrik
)

Gruplayabileceğimiz sütunlar, ilk parametrede verdiğimiz tablodan veya bu tablonun one tarafındaki tablolardan olabilir.

En basit haliyle başlayalım: Ürün kategorilerinin ortalama satışlarını bulmak istiyoruz diyelim. Orijinal satışlar tablosundaki kayıtlar Ürün ID seviyesinde -yani granularity ‘si ürün bazında-. Elimizde her bir kategoriyi ve ilgili kategorinin toplam satışını gösteren bir tablo olsaydı yapmamız gereken tek şey bu satışların ortalamasını almak olacaktı.

“New Table” diyerek ve SUMMARIZE kullanarak böyle bir tabloyu -değerleri görmek açısından-  oluşturabiliriz:

Kategori Satışları =
SUMMARIZE( 'Satışlar' ;
           'Kategoriler'[Kategori] ;
           "Kategori Satışları" ; SUM( 'Satışlar'[Tutar] )
)

Satışlar tablosunu, Kategori bazında grupladık ve her bir kategorinin satışlarını hesapladık!

Hemen altına gecikmeden not olarak ekleyeyim, bu şekilde SUMMARIZE altında hesaplama sütunu eklemek yukarıdaki örnekte de görüldüğü gibi mümkün. Fakat önerilen bir pratik değil! Eğer böyle bir ihtiyaç varsa birazdan göreceğimiz ADDCOLUMNS fonksiyonunu kullanmak -performans açısından- daha doğru.

Formülde dikkatinizi bir noktaya çekeceğim, kategori satışlarının formülü açık bir formül, başında da CALCULATE yok! Buna rağmen her bir kategorinin satışını bulabiliyor. SUMMARIZE row context yaratarak çalışır (yani her bir kategori için tek tek iterate eder) ve bunu yaparken de row context’in ilgili filter context’ini doğrudan görür. İterate ettiği (row context’teki) satırda hangi kategori varsa, bu kategorinin satışlarının olduğu tabloyu (eşleniği olan filter context’i) görür. Yani “context transition” SUMMARIZE’da otomatikman var.

Yeni tablo yaratmak sadece üretilen tablonun sonuçlarını gözümüzle görmek için elbette! Bu hesabı yapmak için tablo oluşturmaya gerek yok.

Doğrudan metriğimizi yarabiliriz:

Ortalama Kategori Satışları :=
AVERAGEX(
    SUMMARIZE( 'Satışlar' ;
               'Kategoriler'[Kategori] ;
               "Kategori Satışları" ; SUM( 'Satışlar'[Tutar] )
    ) ;
    [Kategori Satışları]
)

Tek bir sütun üzerinden gruplama örneği yaptık! Bunu VALUES ile de yazabilirdik.

Ortalama Kategori Satışları_VALUES :=
AVERAGEX( VALUES( 'Kategoriler'[Kategori ID] ) ;
    CALCULATE( SUM( 'Satışlar'[Tutar] ) )
)

VALUES tek bir sütun üzerinden çalışır! SUMMARIZE ise birden fazla sütun üzerinde çalışabilir. SUMMARIZE ‘ın en büyük kullanım amacı da bu zaten: Birden fazla boyutun tüm değer kombinasyonlarını bulmak !

Başka bir örnek yapalım. Çeyrek bazında satışların ortalamasını bulmak istiyoruz diyelim. Bu sefer yeni tablo oluşturmak yerine örneği DAX Studio ile yapalım.

* DAX Studio Power BI ile birlikte özellikle model geliştirirken kullanabileceğimiz 3. parti bir araç. DAX’ı bir sorgu dili olarak kullanmak için ideal, yazdığımız formüllerin sorgu planlarını (query plan) anlamak için güzel bir araç. Yakın zamanda da Tabular Editor ile birlikte Power BI menülerinde yerini alacak yanlış bilmiyorsam.

DAX Studio’yu sorgu aracı olarak kullanırken yazacağımız formüllerin başına EVALUATE getirmek yeterli!

EVALUATE
SUMMARIZE( 'Tarih' ;
           'Tarih'[Yıl] ;
           'Tarih'[Çeyrek]
)

Tarih tablosunda bulunan tüm Yıl-Çeyrek kombinasyonlarını döndürdük. Ortalama çeyrek dönem satışlarını bu tablo üzerinden bulabiliriz artık.

Ortalama Çeyrek Satışlar :=
AVERAGEX(
    SUMMARIZE( 'Tarih' ;
               'Tarih'[Yıl] ;
               'Tarih'[Çeyrek]
    ) ;
    CALCULATE( SUM( 'Satışlar'[Tutar] ) )
)

Eğer daha öncesinde SUM (‘Satışlar'[Tutar]) formülünü yazdığımız bir metriğimiz varsa -ki var- doğrudan bu metriği de çağırabiliriz. Aynı kapıya çıkacaktır.

Ortalama Çeyrek Satışlar Metrik Çağırarak :=
AVERAGEX(
    SUMMARIZE( 'Tarih' ;
               'Tarih'[Yıl] ;
               'Tarih'[Çeyrek]
    ) ;
    [Satışlar]
)

Bu cümleyi de son kez tekrarlamış olayım, blogu takip edenlerin artık ezberlediğine eminim : Herhangi bir formülde bir metrik çağırdığımızda, başına CALCULATE eklesek de eklemesek de başında CALCULATE varmış gibi davranır DAX.  Ve eğer çağırdığımız metrik bir row context altında çalışıyorsa (ki bu örnekte row context’i yaratan en dıştaki AVERAGEX) context transition gerçekleşir. Context transition ‘ın gerçekleşmesini istemiyorsak, formülün açık halini başına CALCULATE eklemeden yazmak gerekir.

Üzerinde çalışacağımız tabloyu benzer şekillerde oluşturduktan sonra formüllerdeki yaratıcılık/ihtiyaç artık size kalmış.

Ortalama günlük ürün satışlarını bulalım mesela:

Ortalama Günlük Ürün Satışları :=
VAR _SatisYapilanGunlerVeUrunler =
SUMMARIZE( 'Satışlar' ;
           'Ürünler'[Ürün ID] ;
           'Tarih'[Tarih]
)
RETURN
AVERAGEX( _SatisYapilanGunlerVeUrunler ;
        [Satışlar]
)

SUMMARIZE ile “Satışlar” tablosunda satışın olduğu her bir günün ve ürünün kombinasyonlarının olduğu tabloyu bulduk, AVERAGEX ile de bu tablonun her bir satırındaki kombinasyonun satışlarını bulduk, en son bulduğumuz rakamların ortalamasını alıp çıktık!

SUMMARIZE ‘a işlev olarak benzeyen -ama farklılıkları da olan- bir başka fonksiyon da ADDCOLUMNS. Yazımı şu şekilde:

ADDCOLUMNS ( 'Tablo' ; 
   "Hesap Sütunu 1"; formül ya da metrik;...; 
   "Hesap Sütunu N" ; formül ya da metrik 
)

Fonksiyona tablo olarak doğrudan bir tablo adı verilebileceği gibi sonucu tablo olan bir ifade de kullanılabilir. Yani herhangi bir tablo fonksiyonunu ilk parametrede kullanabiliriz!

ADDCOLUMNS bir iterator. Row context yarattığında satırdaki varlığın eşleniği filter context’i doğrudan SUMMARIZE ‘da olduğu gibi görmez!. Context transition’un gerçekleşebilmesi işin açık formül yazdıysak başına CALCULATE eklememiz gerekir. Yok [Metrik] çağırdıysak ayrıyeten CALCULATE ‘e gerek yok.

Kategoriler tablosu üzerinden bir örnek yapalım:

Kategori Satışları ADDCOLUMNS =
ADDCOLUMNS (
    'Kategoriler';
    "Kategori Satışları"; CALCULATE ( SUM ( 'Satışlar'[Tutar] ) );
    -- Ya da [Satışlar] metriğini çağırabiliriz
    "Kategori Satışları Metrik Çağırarak"; [Satışlar];
    "Kategorideki Ürün Sayısı"; CALCULATE ( COUNTROWS ( 'Ürünler' ) );
    "Kaç Farklı Ürün Satıldı"; CALCULATE ( COUNTROWS ( VALUES ( 'Satışlar'[Ürün ID] ) ) );
    -- Formülün başına CALCULATE yazmazsak context transition
    -- gerçekleşmeyeceğinden Satışlar tablosundaki TÜM tekil Ürün ID'yi sayacaktır!
    "Kaç Farklı Ürün Satıldı Yanlış"; COUNTROWS ( VALUES ( 'Satışlar'[Ürün ID] ) )
)

SUMMARIZE ‘a benzerliği verdiğimiz tabloya ek hesaplama sütunları ekleyebilmesi. Fakat bir gruplama mekanizması yok! Verdiğimiz tablonun orijinal tüm sütunlarına ek olarak geliyor tüm hesaplanan sütunlar. Ve otomatik context transition’da yok!

ADDCOLUMNS ‘ta gruplama mekanizmasının olmaması durumunu, fonksiyona tablo olarak SUMMARIZE altında çalışan bir ifade vererek çözebiliriz!

Yazının en başındaki SUMMARIZE ile kategori satışlarını bulduğumuz örneğe dönecek olursak : Her ne kadar SUMMARIZE ile ek hesaplama sütunu yapabiliyor olsak da, hesaplama sütunu eklemek istediğimizde, önce SUMMARIZE ile gruplama yapıp, hesaplanacak sütunu ADDCOLUMNS ile eklemeliyiz her zaman!

Bir başka deyişle, gruplamak için önce SUMMARIZE ‘ı kullanalım, bu gruplara ek hesaplama sütunu gerekiyorsa bunu da ADDCOLUMNS ile ekleyelim!

Formüllerden gidecek olursak :

Kategori Satışları_SUMMARIZE =
SUMMARIZE( 'Satışlar' ;
           'Kategoriler'[Kategori ID] ;
           'Kategoriler'[Kategori] ;
           "Kategori Satışları" ; SUM( 'Satışlar'[Tutar] )
)

ifadesini her zaman için

Kategori Satışları_ADDCOLUMNS_SUMMARIZE =
ADDCOLUMNS(
    SUMMARIZE( 'Satışlar' ;
               'Kategoriler'[Kategori ID] ;
               'Kategoriler'[Kategori]
    ) ;
    "Kategori Satışları" ; CALCULATE( SUM( 'Satışlar'[Tutar] ) )
)

şeklinde yazmak performans açısından daha doğru!

Yazıyı bitirmeden önce bir not daha ekleyeyim. Özellikle SUMMARIZE kullanımı çok önerilmiyor performans açısından ve bunun yerine SUMMARIZECOLUMNS diye başka bir fonksiyon da eklendi DAX’a sonradan. Bu fonksiyonla SUMMARIZE/ADDCOLUMNS kalıbına gerek kalmadan daha performans dostu formüller yazabiliriz. Lakin eğitim ve öğrenmek açısından güzel fonksiyonlar. SQL ‘deki karşılıkları anlamak açısından önemli buluyorum. Şimdiden bakmak isterseniz Marco Russo’nun yazısını önereceğim.

Tablo fonksiyonlarını yazmaya devam edeceğim.

Yazıdaki modeli -bloga üyeyseniz- indirebilirsiniz.

Sadece üyeler görebilir. Hızlı üyelik için sosyal medya hesabınızla giriş yapabilirsiniz!