Hesaplanan Sütunlar ve Hesaplanan Alanlar ne zaman kullanılır?

Çoğu kullanıcı Power Pivot'un nasıl kullanıldığını ilk öğrendiğinde, asıl gücün sonuçları bir şekilde toplamada veya hesaplamada olduğunu keşfeder. Verilerinizde sayısal değerler içeren bir sütun varsa, bunu bir PivotTable veya Power View Alan Listesi'nde seçerek kolayca toplayabilirsiniz. Doğası gereği, sayısal olduğu için otomatik olarak toplanır, ortalaması alınır, sayılır veya seçtiğiniz toplama türü gösterilir. 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 hemen her zaman açık ölçüler ve hesaplanan sütunlarla aşılabilir.

Önce, Ürün adlı tablodaki her satıra yeni bir metin değeri eklemek için hesaplanan sütun kullandığımız bir örneğe bakalım. Ürün tablosundaki her satır, sattığımız her ürünle ilgili her türlü bilgiyi içerir. Ürün Adı, Renk, Boyut, Bayi Fiyatı vb. için sütunlarımız var. ÜrünKategorisiAdı adlı bir sütun içeren Ürün Kategorisi adlı başka bir ilişkili tablomuz vardır. İstediğimiz şey, Ürün tablosundaki her ürünün, Ürün Kategorisi tablosundaki ürün kategorisi adını eklemesidir. Ürün tablomuzda aşağıdaki gibi Ürün Kategorisi adında bir hesaplanan sütun oluşturabiliriz:

Yeni Ürün Kategorisi formülümüz, ilgili Ürün Kategorisi tablosundaki ÜrünKategorisiAdı sütunundan değerleri almak için RELATED DAX işlevini kullanır ve Ürün tablosundaki her ürün (her satır) için bu değerleri girer.

Bu, daha sonra PivotTable'ın SATIRLAR, SÜTUNLAR veya FİLTRELER alanında ya da bir Power View raporunda kullanabileceğimiz, her satıra sabit bir değer eklemek için hesaplanmış sütunu nasıl kullanabileceğimize harika bir örnektir.

Ü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 modelimizde işlem verilerini içeren bir Satış tablomuz var ve Satış tablosuyla Ürün Kategorisi tablosu arasında bir ilişki var. Satış tablosunda, satış tutarlarını içeren bir sütunumuz ve maliyetleri içeren bir başka sütunumuz var.

SMM sütunundaki değerleri SatışTutarı sütunundaki değerlerden çıkararak her satır için kar tutarını hesaplayan bir hesaplanmış sütun oluşturabiliriz, şöyle:

Şimdi bir PivotTable oluşturup Ürün Kategorisi alanını SÜTUNLAR'a ve yeni Kar alanımızı DEĞERLER alanına sürükleyebiliriz (PowerPivot'taki bir tablodaki sütun, PivotTable Alan Listesi'ndeki bir Alandır). Sonuç, Kar Toplamı adlı örtük bir ölçüdür. Bu, farklı ürün kategorilerinin her biri için kar sütunundan değerlerin toplanmış miktarıdır. Sonucumuz şöyle görünür:

Bu durumda, Kar yalnızca DEĞERLER'in içinde bir alan olarak anlamlıdır. SÜTUNLAR alanına Kar'ı koyarsak, PivotTable'ımız şöyle görünür:

Kar alanımız SÜTUNLAR, SATIRLAR veya FİLTRELER alanlarına yerleştirildiğinde yararlı bilgiler sağlamaz. Yalnızca DEĞERLER alanında toplanmış değer olarak anlam kazanır.

Satışlar tablosundaki her satır için bir kar marjı hesaplayan Kar adlı bir sütun oluşturduk. Ardından PivotTable'ımızın DEĞERLER alanına Profit ekledik ve böylece her bir ürün kategorisi için bir sonucun hesaplandığı örtük bir ölçü oluşturduk. Ürün kategorilerimiz için kârı gerçekten iki kez hesapladığımızı düşünüyorsanız, haklısınız. Önce Satış tablosundaki her satır için bir Kar hesapladık ve ardından her bir ürün kategorisi için toplandığı DEĞERLER alanına Kar'ı ekledik. Ayrıca, Kar hesaplanan sütununu oluşturmamız gerekmediğini düşünüyorsanız, haklısınız. Peki, Kâr hesaplanan sütunu oluşturmadan kârımızı nasıl hesaplarız?

Kâr, açık bir ölçü olarak gerçekten daha iyi hesaplanacaktır.

Şimdilik, sonuçlarımızı karşılaştırmak için PivotTable'ımızın Satış tablosunda Kar hesaplanan sütununu ve SÜTUNSAY olarak Ürün Kategorisini ve DEĞERLERDE Kar değerini bırakacağız.

Satış tablomuzun hesaplama alanında, (adlandırma çakışmalarını önlemek için) Toplam Kar adlı bir ölçü oluşturacağız. Sonunda, daha önce verdiğimizle aynı sonuçları verir, ancak bir Kar hesaplanan sütunu olmadan.

İlk olarak, Satış tablosunda SatışTutarı sütununu seçiyoruz ve sonra Otomatik Toplam'a tıklayarak SatışTutarı ölçüsünün açık bir Toplamını oluşturuyoruz. Unutmayın, açık bir ölçü, Power Pivot'taki bir tablonun hesaplama alanında oluştururuz. COGS sütunu için de aynısını yapıyoruz. Bunları, tanımlanmalarını kolaylaştırmak için Toplam SatışTutarı ve Toplam SMG olarak yeniden adlandıracağız.

Sonra bu formülle başka bir ölçü oluştururuz:

Toplam Kar:=[Toplam SatışTutarı] - [Toplam SMM]

Yeni Toplam Kar ölçümümüzün biçimini para birimi olarak değiştirdikten sonra, bu biçimi PivotTable'ımıza ekleyebiliriz.

Yeni Toplam Kar ölçümüzün, bir Kar hesaplanan sütunu oluşturup bunu DEĞERLER'e yerleştirmekle aynı sonuçları verdiğini görebilirsiniz. Aradaki fark, Toplam Kâr ölçümümüzün çok daha verimli olması ve veri modelimizi daha temiz ve daha yalın hale getirmesidir, çünkü hesaplamayı o anda ve yalnızca PivotTable'ımız için seçtiğimiz alanlar için yapıyoruz. Sonuçta bu Kar hesaplanan sütununa ihtiyacımız yok.

Bu son kısım neden önemli? Hesaplanan sütunlar veri modeline veri ekler ve veriler bellekte yer kaplar. Veri modelini yenilersek, Kar sütunundaki değerlerin tümünü yeniden hesaplamak için işleme kaynakları da gerekli olur. PivotTable'da ürün kategorileri, bölge veya tarihlere göre gibi Kar istediğimiz alanları seçtiğimizde gerçekten kârımızı hesaplamak istediğimiz için bu gibi kaynakları kullanmamız gerekmez.

Başka bir örneğe bakalım. Hesaplanan sütunun ilk bakışta doğru gibi görünen sonuçlar oluşturduğu ama...

Bu örnekte, satış tutarlarını toplam satışların yüzdesi olarak hesaplamak istiyoruz. Satış tablomuzda Satış % adlı hesaplanmış sütunu şu şekilde oluştururuz:

Formülümüz şöyledir: Satış tablosundaki her satır için, SatışTutarı sütunundaki miktarı SatışTutarı sütunundaki tüm tutarların TOPLAMINA bölün.

Bir PivotTable oluşturur ve SÜTUNLAR'a Ürün Kategorisi eklersek ve yeni Satış % sütunumuzu seçerek DEĞERLER'e yerleştirirsek, her bir ürün kategorimiz için toplam Satış Yüzdesi toplamını elde ederiz.

Tamam'ı seçin. Bu şimdiye kadar iyi görünüyor. Ancak bir dilimleyici ekleyelim. Calendar Yıl ekleyip bir yıl seçiyoruz. Bu örnekte 2007'yi seçiyoruz. Elde ettiğimiz şey bu.

İlk bakışta, bu hala doğru görünebilir. Ancak, yüzdelerimiz gerçekten %100 olmalıdır, çünkü 2007 yılı için her bir ürün kategorimiz için toplam satışların yüzdesini bilmek istiyoruz. Peki yanlış giden neydi?

Satış Yüzdesi sütunumuz, her satır için, SatışTutarı sütunundaki değerin SatışTutarı sütunundaki tüm değerlerin toplamına bölünmesiyle elde edilen yüzdeyi hesapladı. Hesaplanan sütundaki değerler sabittir. Bunlar, tablodaki her satır için değişmez sonuçlardır. PivotTable'ımıza Satış Yüzdesi eklediğimizde, bu, SatışTutarı sütunundaki tüm değerlerin toplamı olarak toplandı. Satış Yüzdesi sütunundaki tüm değerlerin toplamı her zaman %100 olur.

Satış Yüzdesi hesaplanan sütunumuzu silebiliriz, çünkü bu bize yardımcı olmaz. Bunun yerine, uygulanan filtre veya dilimleyiciden bağımsız olarak toplam satışlardaki yüzdemizi doğru şekilde hesaplayan bir ölçü oluşturacağız.

Daha önce oluşturduğumuz, basitçe SatışTutarı sütununun toplamını alan TotalSalesAmount ölçüsünü hatırlıyor musunuz? Bunu Toplam Kar ölçümünde bağımsız değişken olarak kullandık ve yeni hesaplanan alanımızda da bağımsız değişken olarak kullanacağız.

Aşağıdaki formülle yeni bir ölçü oluşturuyoruz:

Toplam Satış Yüzdesi:=([Toplam SatışTutarı]) / CALCULATE([Toplam SatışTutarı], ALLSELECTED())

Bu formülde şunlar belirtilir: PivotTable'da tanımlananlar dışında bir sütun veya satır filtresi olmadan, Toplam SatışTutarı sonucunu, SatışTutarı toplamına bölün.

Şimdi, yeni Toplam Satış Yüzdesini PivotTable'a eklersek şunu elde ederiz:

Bu daha iyi görünüyor. Şimdi her bir ürün kategorisi için Toplam Satış Yüzdesi , 2007 yılı için toplam satışların yüzdesi olarak hesaplanıyor. Farklı bir yıl veya TakvimYılı dilimleyicisinde birden fazla yıl seçersek ürün kategorilerimiz için yeni yüzdeler alırız ancak genel toplamımız yine %100'dür. Başka dilimleyiciler ve filtreler de ekleyebiliriz. Toplam Satış Yüzdesi ölçümüz, uygulanan dilimleyici veya filtrelerden bağımsız olarak her zaman toplam satışların bir yüzdesini oluşturur. Ölçülerle, sonuç her zaman SÜTUN ve SATIRLARDAKI alanlar ve uygulanan filtreler veya dilimleyiciler tarafından belirlenen bağlama göre hesaplanır. Bu, önlemlerin gücüdür.

Aşağıda, hesaplanan sütunun veya hesaplamanın belirli bir hesaplama gereksinimi için uygun olup olmadığına karar verirken size yardımcı olacak birkaç kılavuz verilmiştir:

Hesaplanmış sütunları kullanma

• Yeni verilerinizin PivotTable'daki SATIRLAR, SÜTUNLAR veya FİLTRELER içinde veya Power View görsel öğesindeki EKSEN, GÖSTERGE veya KUTUCUK ÖLÇÜTÜ üzerinde görünmesini istiyorsanız, hesaplanmış sütun kullanmalısınız. Tıpkı normal veri sütunları gibi, hesaplanmış sütunlar da herhangi bir alanda alan olarak kullanılabilir ve sayısal olmaları durumunda DEĞERLER'de de toplanabilirler.
• Yeni verilerinizin satır için sabit bir değer olmasını istiyorsanız. Örneğin, tarih sütunu olan bir tarih tablonuz olduğunu ve yalnızca ayın numaralarını içeren başka bir sütun istediğinizi varsayalım. 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 isterseniz, hesaplanmış sütun kullanın. Metin değerleri içeren alanlar hiçbir zaman DEĞERLER'de toplanamaz. Örneğin, =FORMAT('Date'[Date],"mmmm") bize Tarih tablosunun Tarih sütununda yer alan her tarihin ay adını verir.

Ölçüleri kullanma

• Hesaplamanızın sonucu her zaman PivotTable'da seçtiğiniz diğer alanlara bağlı olacaksa.
• Bir tür filtreye dayalı sayım hesaplama veya yıla göre sapma hesaplama gibi daha karmaşık hesaplamalar yapmanız gerekiyorsa, hesaplanan alanı kullanın.
• Çalışma kitabınızın boyutunu minimumda tutmak ve performansını en üst düzeye çıkarmak istiyorsanız, hesaplamalarınızın mümkün olduğunca çoğunu ölçüler kadar oluşturun. Çoğu durumda, hesaplamalarınızın tümü çalışma kitabınızın boyutunu önemli ölçüde azaltıp yenileme süresini hızlandıracak şekilde ölçülere dönüştürülebilir.

Kar sütunumuzda yaptığımız gibi hesaplanan sütunlar oluşturup bunları bir PivotTable'da veya raporda toplamakta bir yanlışlık olmadığını unutmayın. Aslında kendi hesaplamalarınızı öğrenmek ve oluşturmak için gerçekten iyi ve kolay bir yoldur. Power Pivot'un bu son derece güçlü iki özelliğini anladıkça, yapabileceğiniz en verimli ve en doğru veri modelini oluşturmak isteyeceksiniz. Umarım burada öğrendikleriniz yardımcı olmuştur. Size yardımcı olabilecek gerçekten harika başka kaynaklar da var. Burada yalnızca birkaçı verilmiştir: DAX Formüllerinde Bağlam, Power Pivot'ta 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 Veri Modelleme ve Çözümleme örneği, harika veri modelleme ve formül örnekleriyle doludur.