Saat pertama kali mempelajari cara menggunakan Power Pivot, sebagian besar pengguna menemukan bahwa kekuatan riil ada dalam agregasi atau penghitungan hasil dengan beberapa cara. Jika data memiliki kolom dengan nilai numerik, Anda dapat dengan mudah mengagregasinya dengan memilihnya dalam Daftar Bidang PivotTable atau Power View. Secara alam, karena berupa numerik, maka akan secara otomatis dijumlahkan, dihitung rata-rata, dihitung, atau tipe agregasi apa pun yang Anda pilih. Hal ini dikenal sebagai ukuran implisit. Pengukuran implisit bagus untuk agregasi cepat dan mudah, tapi memiliki batas, dan batas itu hampir selalu bisa diatasi dengan pengukuran eksplisit dan kolom terhitung.
Mari kita terlebih dahulu melihat contoh di mana kita menggunakan kolom terhitung untuk menambahkan nilai teks baru untuk setiap baris dalam tabel bernama Produk. Setiap baris di tabel Produk berisi semua jenis informasi tentang setiap produk yang kami jual. Kami memiliki kolom untuk Nama Produk, Warna, Ukuran, Harga Oranye, dll.. Kami memiliki tabel terkait lainnya bernama Kategori Produk yang berisi kolom ProductCategoryName. Apa yang kami inginkan adalah untuk setiap produk di tabel Produk untuk menyertakan nama kategori produk dari tabel Kategori Produk. Dalam tabel Produk kami, kita bisa membuat kolom terhitung bernama Kategori Produk seperti ini:
Rumus Kategori Produk kami yang baru menggunakan fungsi DAX TERKAIT untuk mendapatkan nilai dari kolom ProductCategoryName dalam tabel Kategori Produk terkait lalu memasukkan nilai tersebut untuk setiap produk (setiap baris) di tabel Product.
Ini adalah contoh yang bagus tentang cara menggunakan kolom terhitung untuk menambahkan nilai tetap untuk setiap baris yang bisa kita gunakan nanti di area BARIS, KOLOM, atau FILTER PivotTable atau di laporan Power View.
Mari kita buat contoh lain di mana kita ingin menghitung margin laba untuk kategori produk kita. Ini adalah skenario umum, bahkan dalam banyak tutorial. Kami memiliki tabel Penjualan di model data kami yang memiliki data transaksi, dan ada hubungan antara tabel Penjualan dan tabel Kategori Produk. Dalam tabel Penjualan, kami memiliki kolom yang memiliki jumlah penjualan dan kolom lain yang memiliki biaya.
Kami dapat membuat kolom terhitung yang menghitung jumlah laba untuk setiap baris dengan mengurangi nilai di kolom COGS dari nilai dalam kolom JumlahJualan, seperti ini:
Sekarang, kita bisa membuat PivotTable dan menyeret bidang Kategori Produk ke KOLOM, dan bidang Laba baru kami ke dalam area NILAI (kolom dalam tabel di PowerPivot adalah Bidang dalam Daftar Bidang PivotTable). Hasilnya adalah pengukuran implisit bernama Jumlah Laba. Ini adalah jumlah agregat nilai dari kolom laba untuk setiap kategori produk yang berbeda. Hasil kami terlihat seperti ini:
Dalam kasus ini, Laba hanya masuk akal sebagai bidang dalam NILAI. Jika kami ingin menempatkan Laba di area KOLOM, PivotTable kami akan terlihat seperti ini:
Bidang Profit kami tidak menyediakan informasi yang berguna ketika diletakkan di area KOLOM, BARIS, atau FILTER. Hal ini hanya masuk akal sebagai nilai agregat dalam area NILAI.
Apa yang telah kita lakukan adalah membuat kolom bernama Laba yang menghitung margin laba untuk setiap baris di tabel Penjualan. Kami kemudian menambahkan Laba ke area NILAI dari PivotTable, secara otomatis membuat pengukuran implisit, tempat hasil dihitung untuk setiap kategori produk. Jika Anda berpikir kami benar-benar menghitung laba untuk kategori produk kami dua kali, Anda sudah benar. Kami pertama-dulu menghitung laba untuk setiap baris di dalam tabel Penjualan, lalu kami menambahkan Laba ke area NILAI di mana laba diagregasi untuk setiap kategori produk. Jika Anda juga berpikir bahwa kami tidak benar-benar perlu membuat kolom Terhitung laba, Anda juga benar. Namun, bagaimana cara menghitung laba tanpa membuat kolom terhitung Laba?
Laba, benar-benar lebih baik dihitung sebagai ukuran eksplisit.
Untuk saat ini, kita akan membiarkan kolom terhitung Laba di tabel Penjualan dan Kategori Produk dalam KOLOM dan Laba dalam NILAI PivotTable, untuk membandingkan hasilnya.
Di area perhitungan tabel Penjualan kami, kami akan membuat pengukuran bernama Laba Total (untuk menghindari konflik penamaan). Di akhir, excel akan menghasilkan hasil yang sama seperti yang kita lakukan sebelumnya, tapi tanpa kolom Terhitung laba.
Pertama, dalam tabel Penjualan, kami memilih kolom JumlahJualan, lalu klik JumlahOtomatis untuk membuat ukuran Jumlah PenjualanAmount yang eksplisit. Ingat, ukuran eksplisit adalah ukuran yang kita buat di area perhitungan tabel di Power Pivot. Kami melakukan hal yang sama untuk kolom COGS. Kami akan mengganti nama Total SalesAmount dan Total COGS ini agar lebih mudah mengidentifikasinya.
Lalu kita membuat pengukuran lain dengan rumus ini:
Total Laba:=[ Total SalesAmount] - [Total COGS]
Catatan: Kami juga dapat menulis rumus kami sebagai Total Profit:=SUM([SalesAmount]) - SUM([COGS]), tetapi dengan membuat pengukuran Total SalesAmount dan Total COGS secara terpisah, kami juga dapat menggunakannya dalam PivotTable, dan kami dapat menggunakannya sebagai argumen dalam semua jenis rumus pengukuran lainnya.
Setelah mengubah format Total Pengukuran Laba ke mata uang kami yang baru, kita bisa menambahkannya ke PivotTable kami.
Anda dapat melihat Pengukuran Total Laba kami yang baru mengembalikan hasil yang sama seperti membuat kolom terhitung Laba, lalu menempatkannya di NILAI. Perbedaannya adalah Ukuran Total Laba kami jauh lebih efisien dan membuat model data kami lebih rapi dan lebih ramping karena kami menghitung pada saat itu dan hanya untuk bidang yang kami pilih untuk PivotTable. Kami benar-benar tidak memerlukan kolom terhitung laba.
Mengapa bagian terakhir ini penting? Kolom terhitung menambahkan data ke model data, dan data menggunakan memori. Jika kita melakukan refresh model data, pemrosesan sumber daya juga diperlukan untuk menghitung ulang semua nilai di kolom Laba. Kami tidak benar-benar perlu mengambil sumber daya seperti ini karena kami benar-benar ingin menghitung laba ketika memilih bidang yang kami inginkan Laba untuk di PivotTable, seperti kategori produk, kawasan, atau menurut tanggal.
Mari kita lihat contoh lainnya. Di mana kolom terhitung membuat hasil yang sekilas terlihat benar, tapi....
Dalam contoh ini, kami ingin menghitung jumlah penjualan sebagai persentase dari total penjualan. Kami membuat kolom terhitung bernama % Penjualan dalam tabel Penjualan kami, seperti ini:
Rumus kami menyatakan: Untuk setiap baris dalam tabel Penjualan, bagi jumlah dalam kolom JumlahJualan dengan total SUM dari semua jumlah dalam kolom JumlahJualan.
Jika membuat PivotTable dan menambahkan Kategori Produk ke KOLOM dan memilih kolom % Penjualan baru untuk meletakkannya ke dalam NILAI, kami mendapatkan total total % Penjualan untuk setiap kategori produk.
Oke. Sejauh ini terlihat bagus. Tapi, mari kita tambahkan pemotong. Kami menambahkan Tahun Kalender lalu memilih tahun. Dalam hal ini, kami memilih 2007. Inilah yang akan kami dapatkan.
Sekilas, ini mungkin masih tampak benar. Namun, persentase kami benar-benar akan berjumlah 100%, karena kami ingin mengetahui persentase penjualan total untuk setiap kategori produk kami untuk 2007. Lalu apa yang salah?
Kolom % Penjualan kami menghitung persen untuk setiap baris yang merupakan nilai dalam kolom JumlahJualan dibagi dengan total jumlah semua nilai dalam kolom JumlahJualan. Nilai di kolom terhitung telah diperbaiki. Ini adalah hasil yang tetap untuk setiap baris dalam tabel. Ketika menambahkan % Penjualan ke PivotTable, kami menggabungkan semua nilai dalam kolom JumlahJualan. Jumlah semua nilai dalam kolom % penjualan akan selalu 100%.
Tips: Pastikan untuk membaca Konteks dalam Rumus DAX. Artikel ini menyediakan pemahaman yang baik tentang konteks tingkat baris dan konteks filter, yang kami gambarkan di sini.
Kami dapat menghapus kolom terhitung % Penjualan karena kolom tersebut tidak akan membantu kami. Sebagai gantinya, kami akan membuat pengukuran yang menghitung persen total penjualan dengan benar, tanpa memperhatikan filter atau pemotong yang diterapkan.
Ingat ukuran TotalSalesAmount yang kami buat sebelumnya, yang hanya menjumlahkan kolom JumlahJualan? Kami menggunakannya sebagai argumen dalam pengukuran Total Laba, dan kami akan menggunakannya lagi sebagai argumen dalam bidang terhitung baru kami.
Tips: Membuat ukuran eksplisit seperti Total SalesAmount dan Total COGS tidak hanya berguna sendiri dalam PivotTable atau laporan, tetapi juga berguna sebagai argumen dalam ukuran lain ketika Anda memerlukan hasil sebagai argumen. Ini membuat rumus Anda lebih efisien dan lebih mudah dibaca. Ini adalah praktik pemodelan data yang baik.
Kami membuat pengukuran baru dengan rumus berikut:
% dari Total Sales:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())
Rumus ini menyatakan: Bagi hasil dari Jumlah Total Sales dengan jumlah total SalesAmount tanpa filter kolom atau baris selain yang ditentukan dalam PivotTable.
Tips: Pastikan untuk membaca tentang fungsi CALCULATEdan ALLSELECTED di Referensi DAX.
Sekarang, jika anda menambahkan % Total Sales baru ke PivotTable, kita akan mendapatkan:
Itu terlihat lebih baik. Sekarang % Dari Total Penjualan kami untuk setiap kategori produk dihitung sebagai persentase dari total penjualan untuk tahun 2007. Jika memilih tahun yang berbeda, atau lebih dari satu tahun di pemotong CalendarYear, kami akan mendapatkan persentase baru untuk kategori produk, tetapi total keseluruhan kami masih 100%. Kami juga dapat menambahkan pemotong dan filter lain. Ukuran % Total Penjualan kami akan selalu menghasilkan persentase penjualan total terlepas dari pemotong atau filter yang diterapkan. Dengan pengukuran, hasil selalu dihitung sesuai dengan konteks yang ditentukan oleh bidang dalam KOLOM dan BARIS, dan dengan filter atau pemotong apa pun yang diterapkan. Ini adalah kekuatan pengukuran.
Berikut adalah beberapa panduan untuk membantu Anda ketika memutuskan apakah kolom terhitung atau bukan atau ukuran yang tepat untuk kebutuhan penghitungan tertentu:
Menggunakan kolom terhitung
-
Jika ingin data baru muncul pada BARIS, KOLOM, atau di FILTER dalam PivotTable, atau pada SUMBU, LEGENDA, atau, UBIN MENURUT dalam visualisasi Power View, Anda harus menggunakan kolom terhitung. Sama seperti kolom data reguler, kolom terhitung dapat digunakan sebagai bidang di area mana pun, dan jika berupa numerik, kolom tersebut juga dapat diagregasi dalam NILAI.
-
Jika ingin data baru menjadi nilai tetap untuk baris tersebut. Misalnya, Anda memiliki tabel tanggal dengan kolom tanggal, dan ingin kolom lainnya yang hanya berisi jumlah bulan. Anda dapat membuat kolom terhitung yang menghitung jumlah bulan saja dari tanggal dalam kolom Tanggal. Misalnya, =MONTH('Date'[Date]).
-
Jika Anda ingin menambahkan nilai teks untuk setiap baris ke tabel, gunakan kolom terhitung. Bidang dengan nilai teks tidak bisa diagregasi dalam VALUES. Misalnya, =FORMAT('Date'[Date],"mmmm") memberi kami nama bulan untuk setiap tanggal dalam kolom Tanggal dalam tabel Tanggal.
Menggunakan pengukuran
-
Jika hasil perhitungan akan selalu bergantung pada bidang lain yang Anda pilih dalam PivotTable.
-
Jika perlu melakukan penghitungan yang lebih kompleks, seperti menghitung hitungan berdasarkan filter pengurutan, atau menghitung tahun dalam setahun, atau variansi, gunakan bidang terhitung.
-
Jika Anda ingin mempertahankan ukuran buku kerja Anda agar minimal dan memaksimalkan kinerjanya, buat perhitungan sebanyak mungkin. Dalam banyak kasus, semua perhitungan Anda bisa mengukur, mengurangi ukuran buku kerja secara signifikan dan mempercepat waktu refresh.
Ingatlah, tidak ada yang salah dengan membuat kolom terhitung seperti yang kami lakukan dengan kolom Laba kami, lalu agregasi kolom di PivotTable atau laporan. Ini sebenarnya cara yang benar-benar baik dan mudah untuk mempelajari dan membuat penghitungan Anda sendiri. Karena pemahaman Anda tentang dua fitur Power Pivot yang sangat efektif ini berkembang, Anda mungkin ingin membuat model data yang paling efisien dan akurat yang bisa Anda lakukan. Diharapkan apa yang telah Anda pelajari di sini dapat membantu. Ada beberapa sumber daya lain yang sangat hebat di luar sana yang bisa membantu Anda juga. Berikut ini beberapa saja: Konteks dalam Rumus DAX, Agregasi di Power Pivot, dan Pusat Sumber Daya DAX. Dan, meski sedikit lebih canggih, dan diarahkan ke arah profesional akuntansi dan keuangan, Pemodelan dan Analisis Data Laba dan Kerugian dengan Microsoft Power Pivot dalam Excel sampel dimuat dengan pemodelan data dan contoh rumus yang hebat.
