Kümülatif Toplam Alma

Şubat 18, 2020

Power BI ‘da farklı şekillerde kümülatif toplamlar almaya ihtiyaç duyuyoruz. Zaman bazında kümülatif almak için gerekli fonksiyonlar “Time Intelligence” grubu diye geçiyor. Hatırlamakta fayda var, her modelin istisnasız olmazsa olmaz tablosu olan tarih tablosu bu fonksiyon grubunun çalışması için elzem. Zaman bazlı yazılara göz atmak isterseniz buradan!

Zaman bazlı olmayan durumlar için de kümülatiflere ihtiyacımız var. Kategori bazında kümülatif, satış bölgesi bazında kümülatif vs. En basitinden bize bakiye hesaplamak için lazım, stok, finans vs, artık neyin bakiyesi lazımsa bize. Verinin yapısına göre de kümülatif formülleri yazmanın da birden fazla yolu var!

Birkaç örnek yapalım: fact tablosundaki “bir şeyi”, ki bu satış tutarı, depo giriş çıkış miktarı, hesap tutarı vs her şey olabilir, tarih bazında ilk tarihten itibaren toplayalım

Satışlar Kümülatif_Tarih Bazında =
CALCULATE( SUM( 'Satışlar'[Tutar] ) ,
    FILTER( ALL( 'Tarih'[Tarih] ) ,
        'Tarih'[Tarih] <= MAX( 'Tarih'[Tarih] )
    )
)

** Eğitimlerde -genelde- en az yarım saat anlattığım bir formül, Tarih[Tarih] sütununa bir sürü referans var, hangi Tarih[Tarih] hangisi? (Tepkiler de genelde ilk seferinde “beynimiz yandı” şeklinde oluyor!)

Nasıl çalıştığını tekrar anlatmaya gerek yok, bu yazıya göz atmak isteyebilirsiniz.

Aynı formülü değişkenle de yazabiliriz elbette.

Satışlar Kümülatif Tarih Bazında Değişken = 
Var _MatristekiSonTarih = MAX( 'Tarih'[Tarih] )
RETURN
CALCULATE( SUM( 'Satışlar'[Tutar] ) , 
    FILTER( ALL( 'Tarih'[Tarih] ) ,
        'Tarih'[Tarih] <= _MatristekiSonTarih
    )
)

Yukarıdaki formül bildiğimiz standart bakiye formülü, tarih tablosundaki tarih sütunu değil de fact tablosundaki tarih sütunları kullanılarak da yazılabilir, biraz tercihinize bağlı, arada ufak nüanslar olacaktır sadece.

Sık gelen “nasıl yaparız” sorularından biri olduğu için örnekleri biraz çeşitlendirelim.

Kümülatif alma tekniklerinden biri, neyin kümülatifini alacaksak, bununla ilgili bir indeks, yani sıralama sütunu oluşturmaktan geçiyor. Örneğin bizim modelimizdeki “Kategoriler” tablosu şöyle:

“Kategori ID” sütununu bir indeks sütunu gibi değerlendirecek olursak, bu sütunu kullanarak kategori bazında kümülatif almak mümkün.

Satışlar Kümülatif Kategori Bazında İndeks =
IF (
    HASONEFILTER ( 'Ürün Kategorileri'[Kategori] ),
    -- Matrisin Kategori satırındaysak bunu hesapla !
    CALCULATE (
        SUM ( 'Satışlar'[Tutar] ),
        FILTER (
            ALL ( 'Ürün Kategorileri' ),
            'Ürün Kategorileri'[Ürün Kategori ID]
                <= VALUES ( 'Ürün Kategorileri'[Ürün Kategori ID] )
        )
    ),
    -- Kategori tablosundaki filtreler üzerinden, 
    -- ilk indeksten satırda gözüken kategorinin
    -- indeksine kadar olan tüm kategori satışlarını topla 
    SUM ( 'Satışlar'[Tutar] ) 
    -- Toplam satırındaysak da toplam satışları göster
)

** Eğer seçili filtreler üzerinden değil de her şey üzerinden hesaplama yapacaksanız ALLSELECTED yerine ALL kullanmanız gerektiğini söylemeye gerek yok sanırım.
Bu çözümün tek kusuru -eğer buna kusur diyeceksek- indeks sütununa göre sıralamazsanız matrisi, rakamlar yanlış yerlerde -gibi- gözükebilir. Örneğin yukarıdaki matrisi satış büyüklüklerine göre sıraladığımızda görünüm “bozuluyor”!

Yukarıdaki görünümler için Kategori sütununu “Ürün Kategori ID” sütununa göre, “Sort By” özelliğini kullanarak sıraladım!

…

Başka bir yol da ISONORAFTER fonksiyonunu kullanmak:

Satışlar Kümülatif Kategori Bazında =
CALCULATE(
  SUM( 'Satışlar'[Tutar] ),
  FILTER(
    ALLSELECTED( 'Ürün Kategorileri'[Kategori] ),
    ISONORAFTER(
        'Ürün Kategorileri'[Kategori], MAX( 'Ürün Kategorileri'[Kategori] ), DESC
    )
  )
)

Bu fonksiyon SQL jargonundaki “Start At” gibi. Buradan başla –> Buraya Kadar! Artan ya da azalan olarak, bir parametresi de bu, DESC ya da ASC, yani artan-azalan şeklinde.

Mantık aslında bir önceki indeksten çok farklı değil, hep bir indekse referans var, burada da harf sırasına göre, DESC yerine ASC desek (ascending, artan ) tersinden bir kümülatif çıkacak karşımıza!

Satışlar Kümülatif Kategori Bazında ASC =
CALCULATE (
    SUM ( 'Satışlar'[Tutar] ),
    FILTER (
        ALLSELECTED ( 'Ürün Kategorileri'[Kategori] ),
        ISONORAFTER (
                'Ürün Kategorileri'[Kategori], MAX ( 'Ürün Kategorileri'[Kategori] ), ASC
        )
    )
)

Buradaki “sorun da” bir önceki indeks mantığına göre yazdığımız metrikle aynı. Satış büyüklüklerine göre sıralarsak rakamların yeri şaşıracak, kategoriler harf sırasına göre olursa sorun yok.

Sıralamayı kendimiz belirleyebildiğimiz için ben indeks mantığını tercih ediyorum genellikle, ama kullanıcı sıralamayı değiştirirse yapacak bir şey -şu an itibariyle- yok!

Bir kaç soruya da -tekrar- cevap niyetine bir örnek daha yapalım: Matriste kümülatif gösterirken satırda gözüken neyse onun kümülatifi gözüksün istiyoruz. Daha önce kullandığımız ISINSCOPE bunun için en ideal yöntem. Hatta şöyle değiştirelim bu cümleyi, matriste nerede olduğumuzu anlamanın en kullanışlı yolu ISINSCOPE fonksiyonunu kullanmak.

Neredeyiz ? :=
SWITCH( TRUE() ;
ISINSCOPE('Ürün Alt Kategorileri'[Alt Kategori] );"Alt Kategori Satırı";
ISINSCOPE('Ürün Kategorileri'[Kategori] );"Kategori Satırı ";
"Toplam Satırı"
)

Dikkat edilmesi ya da hatırlamamız gereken temel kurallardan biri şu, içiçe IF ‘ler gibi, SWITCH gibi birden fazla condition testine soktuğumuz formüllerde, ilk TRUE dönen condition’dan sonrakiler test edilmez, execution durur! Bu yüzden sıralamaya dikkat etmeniz lazım!

Neredeyiz Yanlış ? :=
SWITCH( TRUE() ;
ISINSCOPE( 'Ürün Kategorileri'[Kategori] ) ; "Kategori" ;
ISINSCOPE( 'Ürün Alt Kategorileri'[Alt Kategori] ) ; "Alt Kategori" ;
"Toplam Satırı"
)

“Kategori” sütunu “Alt Kategori” ‘nin bir üst scope’u! Parent’i. Ya da Türkçe, Kategori Alt Kategorileri içeriyor ama tersi doğru değil -modelimize göre elbette-.

Buna göre Kategori-Alt Kategori kırılımında kümülatifleri görelim istiyoruz diyelim.

Yukarıdaki formüllere benzer şekilde Alt Kategori bazında kümülatif ekliyorum.

Satışlar Kümülatif Alt Kategori Bazında İndeks =
IF (
    HASONEFILTER ( 'Ürün Alt Kategorileri'[Alt Kategori] ),
    CALCULATE (
        SUM ( 'Satışlar'[Tutar] ),
        FILTER (
            ALLSELECTED ( 'Ürün Alt Kategorileri' ),
            'Ürün Alt Kategorileri'[Ürün Alt Kategori ID]
                <= VALUES ( 'Ürünler'[Ürün Alt Kategori ID] )
        )
    ),
    SUM ( 'Satışlar'[Tutar] )
)

ISINSCOPE ile satırda gözüken alt kategoriyse alt kategori kümülatifini, kategoriyse kategori kümülatifini göstermeye çalışalım.

Satışlar Kümülatif :=
SWITCH( TRUE() ;
ISINSCOPE( 'Ürün Alt Kategorileri'[Alt Kategori] ) ; [Satışlar Kümülatif Alt Kategori Bazında İndeks] ;
ISINSCOPE( 'Ürün Kategorileri'[Kategori] ) ; [Satışlar Kümülatif Kategori Bazında İndeks] ;
[Satışlar]
)

Bu matris bu haliyle doğru çalışıyor! Çünkü gözüken rakamlar alt kategori kümülatifleriyle tutuyor.

Eğer istediğimiz matris görünümünde alt kategori kümülatifinin ilgili kategoriye göre hesaplanmasını istiyorsak, formülümüzü “Kategori” değiştiğinde bunu dikkate al şeklinde revize etmemiz gerekir. Bunu da en kolay VALUES ile yaparız.

Satışlar Kümülatif VALUES =
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Ürün Alt Kategorileri'[Alt Kategori] ),
        CALCULATE (
            [Satışlar Kümülatif Alt Kategori Bazında İndeks],
            VALUES ( 'Ürün Kategorileri'[Kategori] )
        ),
    ISINSCOPE ( 'Ürün Kategorileri'[Kategori] ), [Satışlar Kümülatif Kategori Bazında İndeks],
    [Satışlar]
)