Custom -özelleştirilmiş- matrislerle ilgili sıklıkla karşılaştığımız durumlardan biri, aynı sütun altında gözüken değerlerden bazılarını farklı formatlarda göstermekle ilgili: En sık karşılaştığımız durum ise nümerik değerlerin olduğu bir sütunda bazı değerleri yüzde formatıyla göstermek. Aynı sütunda farklı format göstermenin birden fazla yöntemi var Power BI ‘da.
Custom matris derken neyi kastediyorum önce bunu biraz açalım, matriste satıra sütuna koyduğumuz başlıklar, normalde master/dimension tablolarındaki sütun değerlerinden gelir. Fakat bazen bu sütunlarda olmayan başlıkların matriste gözükmesini isteriz. Misal finansal tablolar, P&L (kar-zarar) tabloları bu tür matrislere bir örnektir.
Yukarıdaki matrisin nesi “custom” açacak olursak, “Total Revenue” (gelir) , “Cost of Sales” (maliyet) gibi başlıklar sizin hesap planınızda -yani master/boyut tablolarınızda- vardır. Hesap master tablosundaki başlık bilgisi içeren sütunlardan gelir. (Chart of accounts ya da hesap planı, bazen tek bir master tabloda değil, birden fazla master tablo olarak da tasarlanabilir Power BI’da.)
Örnekteki GP % , EBITDA % gibi başlıklar ise hiçbir master tablo sütununda geçmez. Tamamen matrise özel bir hesaplama başlığıdır. Gelirden maliyeti çıkartır brüt karı bulursunuz, brüt karı gelire oranlarsınız brüt kar marjını bulursunuz. Custom matris kabaca bu demek!
Bu tür custom matris tasarımları için 2 farklı yöntem kullanabiliriz: Ya custom matrisin tüm satırlarını/sütunlarını -misal- bir Excel tablosu olarak hazırlar ve modele aynen alırız ve satırda gözüken başlığın ne olduğuna göre metriğimizi modifiye ederiz. Ya da her bir başlık için bir metrik yazarız.
Her başlık için bir metrik yazmak genellikle kolay bir çözümdür fakat raporlama ihtiyacımızı -genellikle- tam karşılamaz: Çünkü metriklerin altına drill-down yapamıyoruz. Oysa yukarıdaki matriste gelir-maliyet üst başlıklarının altında bir sürü hesap grubu, onların altında da hesaplar var. Dolayısıyla diğer yaklaşım, yani matrisin tüm başlıklarını ayrı bir tablo olarak modele almak ve satırda gözüken başlığın ne olduğuna göre metriklerimizi modifiye etmek daha efektif bir çözümdür ama bu bazen çok kolay olmayabilir. Matrisin mantığını nasıl tasarladığımıza bağlı!
Custom matrislerle ilgili birkaç yazı daha yazacağım ama önce en temel haliyle başlayalım: Aşağıdaki gibi bir matris elde etmek istiyoruz diyelim.
Her bir başlık için birer metrik yazdıysak yapmamız gereken şey matris görselinde “Show on Rows” opsiyonunu açmak. Aksi taktirde metrikler satırlarda değil sütunlarda gözükür.
Eğer bu yöntemi seçtiysek, her bir sütundaki değerleri -yani metrikleri- farklı formatlarda rahatlıkla gösterebiliriz: Yapmamız gereken tek şey her bir metriğin format ayarlarıyla oynamak.
Bir metriğin tek bir format seçimi olabilir, mevcut durum bu. Satışlar para birimi (currency) gözüksün, maliyetler tamsayı gözüksün, brüt kar yüzdesi % olarak gözüksün diyebiliriz rahatlıkla.
Eğer diğer yaklaşımı seçtiysek -yani matrisi bir tablo olarak modele aldıysak- farklı formatları aynı sütun altında göstermek biraz daha farklı şeyler gerektiriyor!
Tek bir metrik altında tüm başlıkları hesaplamak için kullanabileceğimiz en temel yöntem şu: Satırda gözüken başlığın ne olduğunu yakala, ki bunun için SELECTEDVALUE, HASONEVALUE gibi fonksiyonlar kullanılabilir, başlığın ne olduğuna göre metriği IF ya da SWITCH kalıplarının altına sok, her bir başlık için ne hesaplayacaksan hesapla ve bunu döndür!
Tabloyu modele alıyorum.
Tek bir metrik ile matristeki başlığı capture edip (yakalayıp), SWITCH altına sokalım.
Tek Metrik =
SWITCH(
TRUE() ,
SELECTEDVALUE( 'Matris'[Başlık] ) = "Satışlar" , 100 ,
SELECTEDVALUE( 'Matris'[Başlık] ) = "Maliyetler" , 80 ,
SELECTEDVALUE( 'Matris'[Başlık] ) = "Brüt Kar" , 20 ,
SELECTEDVALUE( 'Matris'[Başlık] ) = "Brüt Kar %" , DIVIDE( 20,100 ),
BLANK()
)
** Burada değişkenlerle ilgili bir önceki blog yazısına atıfta bulunacağım: Metrikleri değişken kullanarak yazmak -özellikle kompleks hesaplamalar yapacaksak- performans için önemli. Yukarıdaki kodun SELECTEDVALUE(…) kısımları, matriste hangi başlık satırında olduğumuza göre 4 defa ayrı ayrı hesaplanabilir! Oysa -satırdaki başlıktan bağımsız- sadece bir kez hesaplanmasını sağlayıp, bunu da bir değişkene atayıp gereksiz execution’dan kurtulabiliriz.
Daha doğru yazımı şöyle olabilir misal:
Tek Metrik Değişken =
VAR _SeciliBaslik = SELECTEDVALUE( 'Matris'[Başlık] )
VAR _BasligiHesapla =
SWITCH(
TRUE() ,
_SeciliBaslik = "Satışlar" , 100 ,
_SeciliBaslik = "Maliyetler" , 80 ,
_SeciliBaslik = "Brüt Kar" , 20 ,
_SeciliBaslik = "Brüt Kar %" , DIVIDE( 20 , 100 ) ,
BLANK()
)
RETURN
_BasligiHesapla
Ya da SWITCH TRUE kalıbı yerine şöyle de yazabiliriz.
Tek Metrik Değişken -2 =
VAR _SeciliBaslik = SELECTEDVALUE( 'Matris'[Başlık] )
VAR _BasligiHesapla =
SWITCH(
_SeciliBaslik,
"Satışlar" , 100 ,
"Maliyetler" , 80 ,
"Brüt Kar" , 20 ,
"Brüt Kar %" , DIVIDE( 20 , 100 ) ,
BLANK()
)
RETURN
_BasligiHesapla
** SWITCH kalıbındaki en sondaki BLANK() ‘i -bu örneğe göre- yazsak da olur yazmasak da. “Hiç biri değilse ne döndürelim” durumuna cevap. IF’te de aynı. Hiçbir koşula uymazsa zaten default olarak blank döner. Ama eklemek iyi bir alışkanlık bana sorarsanız!
Aynı matrisi oluşturduk tek metrik yazarak ama bir sorunumuz var!
Yazdığımız metriğe tek bir gösterim şekli, formatı atayabiliyoruz. % desek her şey yüzde gözükecek, tamsayı desek gene aynı, ne seçtiysek tüm değerler aynı formatta gözükecek.
Halbuki istediğimiz şey, bazı başlıkları tam sayı ya da para birimi olarak göstermek, % başlıklarını ise % olarak görmek. Excel kullanıyor olsaydık hücre formatını çevirirdik, burada da benzer bir özelliği kullanacağız, FORMAT fonksiyonundan faydalanacağız.
FORMAT, kendisine verdiğimiz değeri farklı rakamsal/şekilsel formatlara string olarak, yani text olarak çeviren bir fonksiyon.
İtalyanların Dax.Guide sitesinden bir resmi ekleyeyim.
Buradaki önemli detaylardan biri, FORMAT sonucu dönen şey -misal- bir tarih gibi gözükse de DAX’ın bu değeri bir string, bir text değeri olarak algılaması! Buna geleceğiz birazdan.
Yazdığımız metriğin % döndüren kısmını, FORMAT ile yüzde görünümüne çevirebiliriz.
Farklı Formatlar =
VAR _SeciliBaslik = SELECTEDVALUE( 'Matris'[Başlık] )
VAR _BasligiHesapla =
SWITCH(
TRUE() ,
_SeciliBaslik = "Satışlar" , 100 ,
_SeciliBaslik = "Maliyetler" , 80 ,
_SeciliBaslik = "Brüt Kar" , 20 ,
_SeciliBaslik = "Brüt Kar %" , FORMAT( DIVIDE(20,100) ,"0%"),
BLANK()
)
RETURN
_BasligiHesapla
** “0%” yerine “0.00%” diyerek ondalık rakam sayısını değiştirmek mümkün.
Eğer yazdığımız bu son metriği, başka metriklerde çağırmayacaksak yapmamız gereken bu kadar! Ama çağıracaksak -biraz önce ifade ettiğim- FORMAT sonucu dönen şey bir string/text cümlesi önem kazanıyor!
Diyelim son metriği 2 ile çarpacak yeni bir metriğe ihtiyacımız var!
2 ile Çarp = [Farklı Formatlar] * 2
Metriği yazarken hiç bir hata mesajı almasak da, bu metriği matrise düşürdüğümüzde hata mesajı alıyoruz!
DAX, basit bir aritmetik işlem yapmaya çalışıyor, Satışlar, Maliyetler, Brüt Kar başlıkları zaten nümerik, sorun yok, ama Brüt Kar % başlığı için işlem yapmaya kalktığında esasen “string” olan “Brüt Kar %” satırı için hata veriyor! – Text’i nümerik değere dönüştüremiyorum! DAX, bir çok nümerik gözüken değeri, esasen bir string olsa bile otomatikman çevrimini yapar. String olsa bile “5” + “4” sonucu 9’dur der. Ama % biraz farklı bir gösterim DAX için ve otomatik çevrimini yapamıyor.
Text–> nümerik çevrimini yapabilecek iki fonksiyon var DAX’ta: VALUE ve CONVERT. Fakat her ikisi de, konu % bir string değeri rakama dönüştürmeye geldiğinde işe yaramıyor mevcut durumda!
Daha işe yarar gözüken VALUE, text olarak gözüken değerin belli formatlarda olması durumunda çalışıyor, % bunların içinde değil!
Bu tür bir durumda benim kullandığım yöntem, FORMAT ile şeklini değiştirmediğim metriği çağırmak. Her şey zaten nümerik, ekstra birşey yapmaya gerek yok! Aynı konu için biri formatlı biri formatsız iki metrik yazmış oluyoruz ama mevcut durumda en uygun çözüm bu gibi.
Eğer isterseniz, matriste % başlığı varsa şöyle yap- böyle yap diye atraksiyonlara girmek başka fonksiyonlarla mümkün. VALUES -mevcut durumda- belki tam işimizi görmüyor ama bunun yerine kullanabileceğimiz başka foksiyonlar da var DAX’ta. LEFT ve LEN gibi fonksiyonlarla, sondaki % işaretini atıp, kalan string değeri nümeriğe çevirebiliriz misal!
LEFT&LEN ile % 'yi At, Nümeriğe Çevir =
VAR __YuzdeyiRakamaCevir = "10%"
RETURN
VALUE ( LEFT ( __YuzdeyiRakamaCevir, LEN ( __YuzdeyiRakamaCevir ) - 1 ) ) / 100
Power BI geliştirme ekibi, zaman zaman fonksiyonlarda iyileştirmeler, değişiklikler yapıyor. VALUE fonksiyonuna yüzde çevirmeyi de ekleseler güzel olur.
** Bu örnekteki durumu Calculations Group kullanarak da çözebiliriz, Tabular Editor ‘ün format string opsiyonları çok daha geniş ve esnek hatta. Lakin henüz calculations group ile ilgili hiç yazmadığım için bu çözümü sonraki yazılara bırakıyorum.
Yazıdaki modeli -bloga üyeyseniz- indirebilirsiniz.
Sadece üyeler görebilir. Hızlı üyelik için sosyal medya hesabınızla giriş yapabilirsiniz!