Power Pivot'un nasıl kullanılacağını ilk kez öğrenirken, kullanıcıların çoğu gerçek gücün bir şekilde toplama veya hesaplamada olduğunu keşfeder. Verilerinizde sayısal değerler içeren bir sütun varsa, PivotTable veya Power View Alan Listesi'nde seçerek kolayca toplayabilirsiniz. Doğası gereği, sayısal olduğundan otomatik olarak toplanır, ortalaması alınır, sayılır veya seçtiğiniz toplama türü hangisi olursa olsun. Bu örtük ölçü olarak bilinir. Örtük ölçüler hızlı ve kolay toplama için harikadır, ancak sınırları vardır ve bu sınırlar neredeyse her zaman açık ölçüler ve hesaplanmış sütunlar ile aşılabilir.
Öncelikle Product adlı tablodaki her satır için yeni bir metin değeri eklemek üzere hesaplanmış sütun kullandığımız bir örneğe bakalım. Product tablosundaki her satır, sattığımız her ürünle ilgili her türlü bilgiyi içerir. Ürün Adı, Renk, Boyut, Bayi Fiyatı gibi sütunlara sahibiz. ProductCategoryName sütununu içeren Product Category adlı başka bir ilişkili tablomuz var. Ürün tablosundaki her ürünün Ürün Kategorisi tablosundaki ürün kategorisi adını içermesini istiyoruz. Product tablomuzda, Aşağıdaki gibi Product Category adlı bir hesaplanmış sütun oluşturabiliriz:
Yeni Ürün Kategorisi formülümüz , ilgili Ürün Kategorisi tablosundaki ProductCategoryName sütunundaki değerleri almak için RELATED DAX işlevini kullanır ve ardından Product tablosundaki her ürün (her satır) için bu değerleri girer.
Bu, pivottable'ın SATIRSAY, SÜTUNLAR veya FILTRELER alanında veya Power View raporunda kullanabileceğimiz her satır için sabit bir değer eklemek için hesaplanmış sütunu nasıl kullanabileceğimizi gösteren harika bir örnektir.
Şimdi ürün kategorilerimiz için kar marjı hesaplamak istediğimiz başka bir örnek oluşturalım. Bu, birçok öğreticide bile yaygın bir senaryodur. Veri modelinde işlem verilerine sahip bir Satış tablomuz var ve Sales tablosu ile Ürün Kategorisi tablosu arasında bir ilişki var. Satış tablosunda, satış tutarlarına sahip bir sütun ve maliyetleri olan başka bir sütun vardır.
COGS sütunundaki değerleri SalesAmount sütunundaki değerlerden çıkararak her satır için kar tutarını hesaplayan bir hesaplanmış sütun oluşturabiliriz:
Artık bir PivotTable oluşturup Ürün Kategorisi alanını SÜTUNLAR'a ve yeni Kar alanımızı DEĞERLER alanına sürükleyebilirsiniz (PowerPivot'taki bir tablodaki sütun, PivotTable Alan Listesi'ndeki bir Alandır). Sonuç, Kar Toplamı adlı örtük bir ölçüdür. Farklı ürün kategorilerinin her biri için kar sütunundaki toplam değer miktarıdır. Sonucumuz şöyle görünür:
Bu durumda, Kar yalnızca DEĞERLER'deki bir alan olarak anlamlıdır. Sütunlar alanına Kar'ı koymamız gerekirse PivotTable'ımız şöyle görünür:
Kar alanımız SÜTUNLAR, SATIRLAR veya FILTRELER alanlarına yerleştirildiğinde yararlı bilgiler sağlamaz. Yalnızca DEĞERLER alanında toplanan bir değer olarak anlamlıdır.
Yaptığımız şey, Sales tablosundaki her satır için kar marjını hesaplayan Profit adlı bir sütun oluşturmaktır. Ardından PivotTable'ımızın VALUES alanına Profit ekledik ve otomatik olarak örtük bir ölçü oluşturduk ve burada ürün kategorilerinin her biri için bir sonuç hesaplandı. Ürün kategorilerimiz için gerçekten kârı iki kez hesapladığınızı düşünüyorsanız, haklısınız. Önce Sales tablosundaki her satır için bir kar hesapladık ve ardından Kar'ı ürün kategorilerinin her biri için toplandığı DEĞERLER alanına ekledik. Ayrıca Profit hesaplanan sütununu oluşturmamıza gerek olmadığını düşünüyorsanız, siz de haklısınız. Peki kar hesaplanmış sütunu oluşturmadan kârımızı nasıl hesaplayacağız?
Kar, açık bir ölçü olarak gerçekten daha iyi hesaplanabilir.
Şimdilik sonuçlarımızı karşılaştırmak için Sales tablosunda Profit hesaplanmış sütunumuzu, COLUMNS'da Ürün Kategorisi ve PivotTable'ımızın DEĞERLERİnde Kar sütunumuzu bırakacağız.
Satış tablomuzun hesaplama alanında Total Profit (adlandırma çakışmalarını önlemek için) adlı bir ölçü oluşturacağız. Sonunda, kar hesaplanmış sütunu olmadan daha önce yaptığımızla aynı sonuçları verir.
İlk olarak Sales tablosunda SalesAmount sütununu seçip Otomatik Toplam'a tıklayarak açık bir SalesAmounttoplamı ölçüsü oluştururuz. Açık ölçünün, Power Pivot'taki bir tablonun hesaplama alanında oluşturduğumuz ölçü olduğunu unutmayın. Aynı işlemi COGS sütunu için de yaparız. Tanımlarını kolaylaştırmak için bu Total SalesAmount ve Total COGS'leri yeniden adlandıracağız.
Ardından şu formülle başka bir ölçü oluştururuz:
Toplam Kar:=[ Total SalesAmount] - [Total COGS]
Not: Formülümüzü Toplam Kar:=TOPLA([SatışAmount]) - TOPLA([COGS]) olarak da yazabiliriz, ancak ayrı Total SalesAmount ve Total COGS ölçüleri oluşturarak bunları PivotTable'ımızda da kullanabiliriz ve bunları diğer ölçü formüllerinin her türünde bağımsız değişken olarak kullanabiliriz.
Yeni Toplam Kar ölçümümüzün biçimini para birimine değiştirdikten sonra, bunu PivotTable'ımıza ekleyebiliriz.
Yeni Toplam Kar ölçümümüzün, Kar hesaplanan sütunu oluşturmayla aynı sonuçları döndürdüğü ve ardından DEĞERLER'e yerleştirdiğini görebilirsiniz. Aradaki fark, Toplam Kar ölçümümüzün çok daha verimli olması ve veri modelimizi daha temiz ve daha yalın hale getirir çünkü o anda ve yalnızca PivotTable'ımız için seçtiğimiz alanlar için hesaplama yapıyoruz. Sonuçta bu Profit hesaplanan sütununa gerçekten ihtiyacımız yok.
Bu son bölüm neden önemli? Hesaplanmış sütunlar veri modeline veri ekler ve veriler belleği kaplar. Veri modelini yenilersek, Profit sütunundaki tüm değerlerin yeniden hesaplanması için işlem kaynakları da gerekir. PivotTable'da Kar istediğimiz alanları (ürün kategorileri, bölge veya tarihler gibi) seçtiğimizde kârımızı hesaplamak istediğimizden, bunun gibi kaynakları almamız gerekmez.
Şimdi başka bir örneğe bakalım. Hesaplanmış sütunun ilk bakışta doğru görünen ancak....
Bu örnekte, toplam satışların yüzdesi olarak satış tutarlarını hesaplamak istiyoruz. Sales tablomuzda Sales % adlı bir hesaplanmış sütun oluşturuyoruz, örneğin:
Formülümüz şunları belirtir: Sales tablosundaki her satır için SalesAmount sütunundaki tutarı SalesAmount sütunundaki tüm tutarların TOPLAM toplamına bölün.
PivotTable oluşturup COLUMNS'a Ürün Kategorisi ekleyip yeni Satış % sütunumuzu seçerek VALUES'e eklersek, ürün kategorilerimizin her biri için Sales 'in toplam %'sini elde ederiz.
Tamam. Şimdiye kadar iyi görünüyor. Ama bir dilimleyici ekleyelim. Takvim Yılı ekleyip bir yıl seçiyoruz. Bu durumda 2007'yi seçiyoruz. Bu bizim elde ettiğimiz şey.
İlk bakışta, bu yine de doğru görünebilir. Ancak, 2007 için ürün kategorilerimizin her biri için toplam satış yüzdesini bilmek istediğimiz için yüzdelerimiz gerçekten %100 olmalıdır. Peki ne yanlış gitti?
Satış yüzdesi sütunumuz, SalesAmount sütunundaki değerin SalesAmount sütunundaki tüm değerlerin toplamına bölünmesi halinde her satır için bir yüzde hesaplandı. Hesaplanmış sütundaki değerler sabittir. Bunlar tablodaki her satır için sabit bir sonuç. PivotTable'ımıza Satış %' sini eklediğimizde, SalesAmount sütunundaki tüm değerlerin toplamı olarak toplanmıştır. Satış yüzdesi sütunundaki tüm değerlerin toplamı her zaman %100 olacaktır.
İpucu: DAX Formüllerinde Bağlam'ı okuduğunuzdan emin olun. Burada açıkladığımız satır düzeyi bağlamı ve filtre bağlamı hakkında iyi bir anlayış sağlar.
Satış yüzdesi hesaplanmış sütunumuzu silebiliriz çünkü bu sütun bize yardımcı olmayacaktır. Bunun yerine, uygulanan filtrelere veya dilimleyicilere bakılmaksızın toplam satış yüzdemizi doğru hesaplayan bir ölçü oluşturacağız.
Daha önce oluşturduğumuz SalesAmount sütununu toplayan TotalSalesAmount ölçüsünü hatırlıyor musunuz? Bunu Total Profit ölçüsünde bağımsız değişken olarak kullandık ve yeni hesaplanan alanımızda bağımsız değişken olarak yeniden kullanacağız.
İpucu: Total SalesAmount ve Total COGS gibi açık ölçüler oluşturmak yalnızca PivotTable veya raporda yararlı olmakla kalmaz, aynı zamanda bağımsız değişken olarak sonuca ihtiyaç duyduğunuzda diğer ölçülerde bağımsız değişken olarak da yararlıdır. Bu, formüllerinizi daha verimli ve okunmasını kolaylaştırır. Bu iyi bir veri modelleme uygulamasıdır.
Aşağıdaki formülle yeni bir ölçü oluşturuyoruz:
Toplam Satış yüzdesi:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())
Bu formülde şu ifadeler yer alır: Total SalesAmount'tan elde edilen sonucu, PivotTable'da tanımlananlar dışında herhangi bir sütun veya satır filtresi olmadan SalesAmount toplamına bölün.
İpucu: DAX Başvurusu'ndaki CALCULATE ve ALLSELECTED işlevleri hakkında bilgi edinmeye özen gösterin.
Şimdi, PivotTable'a Yeni Toplam Satış % 'mizi eklersek şunları elde ederiz:
Bu daha iyi görünüyor. Artık her ürün kategorisi için Toplam Satış % değerimiz 2007 yılı toplam satış yüzdesi olarak hesaplanır. CalendarYear dilimleyicisinde farklı bir yıl veya birden fazla yıl seçersek, ürün kategorilerimiz için yeni yüzdeler elde ederiz, ancak genel toplamımız hala %100'dür. Başka dilimleyiciler ve filtreler de ekleyebiliriz. Toplam Satış yüzdesi ölçümüz, uygulanan dilimleyicilere veya filtrelere bakılmaksızın her zaman toplam satışların yüzdesini üretir. Ölçülerle sonuç her zaman SÜTUNLAR ve SATIRSAY içindeki alanlar ve uygulanan filtreler veya dilimleyiciler tarafından belirlenen bağlama göre hesaplanır. Bu ölçülerin gücüdür.
Hesaplanmış sütun veya ölçünün belirli bir hesaplama gereksinimi için uygun olup olmadığına karar verirken size yardımcı olacak birkaç yönerge aşağıdadır:
Hesaplanmış sütunları kullanma
-
Yeni verilerinizin PivotTable'daki SATIRLAR, SÜTUNLAR veya FİlTRELER'de ya da Power View görselleştirmesinde AXIS, LEGEND veya TILE BY üzerinde görünmesini istiyorsanız, hesaplanmış bir sütun kullanmanız gerekir. Normal veri sütunlarında olduğu gibi hesaplanan sütunlar da herhangi bir alanda alan olarak kullanılabilir ve sayısalsa DEĞERLER'de de toplanabilir.
-
Yeni verilerinizin satır için sabit bir değer olmasını istiyorsanız. Örneğin, tarih sütunu içeren bir tarih tablonuz var ve yalnızca ayın sayısını içeren başka bir sütun istiyorsunuz. Tarih sütunundaki tarihlerden yalnızca ay numarasını hesaplayan bir hesaplanmış sütun oluşturabilirsiniz. Örneğin, =AY('Tarih'[Tarih]).
-
Tabloya her satır için bir metin değeri eklemek istiyorsanız, hesaplanmış sütun kullanın. Metin değerlerine sahip alanlar ASLA DEĞERLER'de toplanamaz. Örneğin, =FORMAT('Date'[Date],"mmmm") bize Date tablosundaki Date sütunundaki her tarihin ay adını verir.
Ölçüleri kullanma
-
Hesaplamanızın sonucu her zaman PivotTable'da seçtiğiniz diğer alanlara bağımlı olacaksa.
-
Bir tür filtreye göre sayım hesaplama veya yıldan yıla veya varyans hesaplama gibi daha karmaşık hesaplamalar yapmanız gerekiyorsa, hesaplanan bir alan kullanın.
-
Çalışma kitabınızın boyutunu en düşük düzeyde tutmak ve performansını en üst düzeye çıkarmak istiyorsanız, ölçülerinizin mümkün olduğunca çoğunu oluşturun. Çoğu durumda tüm hesaplamalarınız ölçüler olabilir ve çalışma kitabı boyutunu önemli ölçüde azaltır ve yenileme süresini hızlandırabilir.
Profit sütunumuzda yaptığımız gibi hesaplanmış sütunlar oluşturmanın ve bunu pivottable veya raporda toplamanın yanlış bir yanı olmadığını unutmayın. Aslında bu, kendi hesaplamalarınızı öğrenmek ve oluşturmak için gerçekten iyi ve kolay bir yoldur. Power Pivot'un bu iki son derece güçlü özelliğini kavradığınızda, yapabileceğiniz en verimli ve doğru veri modelini oluşturmak isteyeceksiniz. Umarım burada öğrendiklerin yardımcı olur. Dışarıda size de yardımcı olabilecek başka harika kaynaklar da var. Burada yalnızca birkaç tane vardır: DAX Formülleri bağlamı, Power Pivot'taki toplamalar ve DAX Kaynak Merkezi. Biraz daha gelişmiş ve muhasebe ve finans uzmanlarına yönelik olsa da, Excel'de Microsoft Power Pivot ile Kar ve Zarar Verileri Modelleme ve Analiz örneği harika veri modelleme ve formül örnekleriyle yüklenir.
