Saat pertama kali mempelajari cara menggunakan Power Pivot, sebagian besar pengguna menemukan kekuatan sebenarnya adalah dengan mengagregasi atau menghitung hasil dengan cara tertentu. Jika data Anda memiliki kolom dengan nilai numerik, Anda bisa dengan mudah mengagregasikannya dengan memilihnya dalam PivotTable atau Daftar Bidang Power View. Secara alami, karena angka, secara otomatis akan dijumlahkan, dihitung rata-rata, dihitung, atau tipe agregasi apa pun yang Anda pilih. Ini dikenal sebagai ukuran implisit. Tindakan implisit sangat bagus untuk agregasi cepat dan mudah, tetapi memiliki batas, dan batasan tersebut hampir selalu dapat diatasi dengan pengukuran eksplisit dan kolom terhitung.
Mari kita lihat contoh di mana kita menggunakan kolom terhitung untuk menambahkan nilai teks baru untuk setiap baris dalam tabel bernama Produk. Setiap baris dalam tabel Produk berisi semua jenis informasi tentang setiap produk yang kami jual. Kami memiliki kolom untuk Nama Produk, Warna, Ukuran, Harga Dealer, dll.. Kami memiliki tabel terkait lainnya bernama Kategori Produk yang berisi kolom ProductCategoryName. Yang kami inginkan adalah setiap produk dalam tabel Produk untuk menyertakan nama kategori produk dari tabel Kategori Produk. Dalam tabel Produk, kami dapat membuat kolom terhitung yang bernama Kategori Produk seperti ini:
Rumus Kategori Produk baru kami menggunakan fungsi RELATED DAX untuk mendapatkan nilai dari kolom ProductCategoryName dalam tabel Kategori Produk terkait lalu memasukkan nilai tersebut untuk setiap produk (setiap baris) dalam tabel Produk.
Ini adalah contoh yang bagus tentang bagaimana kita bisa menggunakan kolom terhitung untuk menambahkan nilai tetap untuk setiap baris yang bisa kita gunakan nanti di area BARIS, KOLOM, atau FILTER PivotTable atau dalam laporan Power View.
Mari kita buat contoh lain di mana kita ingin menghitung margin laba untuk kategori produk kami. Ini adalah skenario umum, bahkan dalam banyak tutorial. Kami memiliki tabel Penjualan dalam model data kami yang memiliki data transaksi, dan terdapat hubungan antara tabel Penjualan dan tabel Kategori Produk. Dalam tabel Penjualan, kami memiliki kolom yang memiliki jumlah penjualan dan kolom lain yang memiliki biaya.
Kita bisa membuat kolom terhitung yang menghitung jumlah laba untuk setiap baris dengan mengurangi nilai di kolom COGS dari nilai di kolom SalesAmount, seperti ini:
Sekarang, kita bisa membuat PivotTable dan menyeret bidang Kategori Produk ke KOLOM, dan bidang Laba baru kita ke area NILAI (kolom dalam tabel di PowerPivot adalah Bidang dalam Daftar Bidang PivotTable). Hasilnya adalah ukuran implisit yang bernama Jumlah Laba. Ini adalah jumlah nilai agregat dari kolom laba untuk setiap kategori produk yang berbeda. Hasil kami terlihat seperti ini:
Dalam hal ini, Profit hanya masuk akal sebagai bidang dalam NILAI. Jika kami meletakkan Laba di area KOLOM, PivotTable kami akan terlihat seperti ini:
Bidang Laba kami tidak menyediakan informasi berguna apa pun ketika ditempatkan di area KOLOM, BARIS, atau FILTER. Ini hanya masuk akal sebagai nilai agregat di area NILAI.
Apa yang telah kami lakukan adalah membuat kolom bernama Profit yang menghitung margin laba untuk setiap baris dalam tabel Penjualan. Kami kemudian menambahkan Laba ke area NILAI PivotTable kami, secara otomatis membuat ukuran implisit, di mana hasil dihitung untuk setiap kategori produk. Jika Anda berpikir kami benar-benar menghitung laba untuk kategori produk kami dua kali, Anda benar. Kami terlebih dahulu menghitung laba untuk setiap baris dalam tabel Penjualan, lalu kami menambahkan Laba ke area NILAI di mana laba tersebut diagregasikan untuk setiap kategori produk. Jika Anda juga berpikir kami tidak benar-benar perlu membuat kolom hitung Laba, Anda juga benar. Namun, bagaimana cara menghitung laba kami tanpa membuat kolom hitung Laba?
Profit, benar-benar akan lebih baik dihitung sebagai ukuran eksplisit.
Untuk saat ini, kami akan meninggalkan kolom terhitung Laba kami dalam tabel Penjualan dan Kategori Produk di KOLOM dan Laba dalam NILAI PivotTable kami, untuk membandingkan hasil kami.
Dalam area penghitungan tabel Penjualan kami, kami akan membuat ukuran bernama Total Profit(untuk menghindari konflik penamaan). Pada akhirnya, hasilnya akan sama seperti yang kami lakukan sebelumnya, tetapi tanpa kolom terhitung Profit.
Pertama, dalam tabel Penjualan, kami memilih kolom PenjualanPenjualan, lalu klik JumlahOtomatis untuk membuat pengukuran Jumlah PenjualanEksplisit. Ingat, pengukuran eksplisit adalah ukuran yang kami buat di area penghitungan 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 diidentifikasi.
Lalu kami membuat pengukuran lain dengan rumus ini:
Total Profit:=[ 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 yang terpisah, kami juga dapat menggunakannya dalam PivotTable kami, dan kami dapat menggunakannya sebagai argumen dalam semua jenis rumus pengukuran lainnya.
Setelah mengubah format pengukuran Total Profit baru kami ke mata uang, kami dapat menambahkannya ke PivotTable kami.
Anda dapat melihat ukuran Total Profit kami yang baru mengembalikan hasil yang sama seperti membuat kolom terhitung Laba lalu menempatkannya dalam NILAI. Perbedaannya adalah pengukuran Total Profit kami jauh lebih efisien dan membuat model data kami lebih bersih dan lebih ramping karena kami menghitung pada saat itu dan hanya untuk bidang yang kami pilih untuk PivotTable kami. Kami tidak benar-benar membutuhkan kolom terhitung Laba itu.
Mengapa bagian terakhir ini penting? Kolom terhitung menambahkan data ke model data, dan data memakan memori. Jika kami merefresh model data, pemrosesan sumber daya juga diperlukan untuk menghitung ulang semua nilai dalam kolom Profit. Kami tidak benar-benar perlu mengambil sumber daya seperti ini karena kami benar-benar ingin menghitung laba kami ketika kami memilih bidang yang kami inginkan Profit untuk di PivotTable, seperti kategori produk, kawasan, atau menurut tanggal.
Mari kita lihat contoh lain. 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 % dari Penjualan dalam tabel Penjualan kami, seperti ini:
Status rumus kami: Untuk setiap baris dalam tabel Penjualan, bagi jumlah dalam kolom PenjualanPenjualan dengan total SUM dari semua jumlah dalam kolom PenjualanPenjualan.
Jika kami membuat PivotTable dan menambahkan Kategori Produk ke KOLOM dan memilih kolom % Penjualan baru kami untuk memasukkannya ke dalam NILAI, kami mendapatkan total % Penjualan untuk setiap kategori produk kami.
Oke. Ini terlihat bagus sejauh ini. Tapi, mari kita tambahkan pemotong. Kami menambahkan Tahun Kalender lalu memilih satu tahun. Dalam hal ini, kami memilih 2007. Ini adalah apa yang kita dapatkan.
Sekilas, ini mungkin masih terlihat benar. Namun, persentase kami harus benar-benar total 100%, karena kami ingin mengetahui persentase total penjualan untuk setiap kategori produk kami untuk tahun 2007. Jadi apa yang salah?
Kolom % penjualan kami menghitung persen untuk setiap baris yang merupakan nilai dalam kolom PenjualanPenjualan dibagi dengan total jumlah semua nilai dalam kolom PenjualanPenjualan. Nilai dalam kolom terhitung telah diperbaiki. Hasilnya tidak dapat berubah untuk setiap baris dalam tabel. Ketika kami menambahkan % penjualan ke PivotTable kami, maka jumlah semua nilai dalam kolom SalesAmount telah diagregasikan. Jumlah semua nilai dalam kolom % penjualan akan selalu 100%.
Tips: Pastikan untuk membaca Konteks dalam Rumus DAX. Ini memberikan pemahaman yang baik tentang konteks tingkat baris dan konteks filter, yang kami sebutkan di sini.
Kami dapat menghapus % kolom terhitung Penjualan kami karena tidak akan membantu kami. Sebagai gantinya, Kami akan membuat ukuran yang menghitung persen penjualan total dengan benar, terlepas dari filter atau pemotong apa pun yang diterapkan.
Ingat pengukuran TotalSalesAmount yang kami buat sebelumnya, yang hanya menjumlahkan kolom SalesAmount? Kami menggunakannya sebagai argumen dalam pengukuran Total Profit kami, dan kami akan menggunakannya lagi sebagai argumen dalam bidang terhitung baru kami.
Tips: Membuat pengukuran eksplisit seperti Total SalesAmount dan Total COGS tidak hanya berguna dalam PivotTable atau laporan, tetapi juga berguna sebagai argumen dalam pengukuran lain saat Anda memerlukan hasilnya 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: Membagi hasil dari Total SalesAmount dengan jumlah total SalesAmount tanpa filter kolom atau baris selain yang ditentukan dalam PivotTable.
Tips: Pastikan untuk membaca tentang fungsi CALCULATE dan ALLSELECTED dalam Referensi DAX.
Sekarang, jika kami menambahkan % Penjualan Total baru ke PivotTable, kami mendapatkan:
Itu terlihat lebih baik. Sekarang % dari Total Sales kami untuk setiap kategori produk dihitung sebagai persentase total penjualan untuk tahun 2007. Jika memilih tahun yang berbeda, atau lebih dari satu tahun di pemotong CalendarYear, kami mendapatkan persentase baru untuk kategori produk kami, tetapi total keseluruhan masih 100%. Kami juga dapat menambahkan pemotong dan filter lainnya. Pengukuran % Penjualan Total kami akan selalu menghasilkan persentase total penjualan terlepas dari pemotong atau filter apa pun yang diterapkan. Dengan pengukuran, hasilnya selalu dihitung sesuai dengan konteks yang ditentukan oleh bidang dalam KOLOM dan BARIS, dan oleh filter atau pemotong apa pun yang diterapkan. Ini adalah kekuatan pengukuran.
Berikut adalah beberapa panduan untuk membantu Anda saat memutuskan apakah kolom terhitung atau ukuran tepat atau tidak untuk kebutuhan penghitungan tertentu:
Menggunakan kolom terhitung
-
Jika Anda ingin data baru muncul di BARIS, KOLOM, atau di FILTER dalam PivotTable, atau pada SUMBU, LEGENDA, atau, PETAK BY 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 diagregat dalam NILAI.
-
Jika Anda ingin data baru Anda menjadi nilai tetap untuk baris tersebut. Misalnya, Anda memiliki tabel tanggal dengan kolom tanggal, dan Anda menginginkan kolom lain 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 pernah bisa diagregasi dalam NILAI. Misalnya, =FORMAT('Tanggal'[Tanggal],"mmmm") memberi kami nama bulan untuk setiap tanggal dalam kolom Tanggal dalam tabel Tanggal.
Menggunakan pengukuran
-
Jika hasil penghitungan akan selalu bergantung pada bidang lain yang Anda pilih dalam PivotTable.
-
Jika Anda perlu melakukan penghitungan yang lebih kompleks, seperti menghitung hitungan berdasarkan filter dari beberapa pengurutan, atau menghitung tahun demi tahun, atau varians, gunakan bidang terhitung.
-
Jika Anda ingin menjaga ukuran buku kerja Anda seminimal mungkin dan memaksimalkan kinerjanya, buat sebanyak mungkin perhitungan Anda. Dalam banyak kasus, semua perhitungan Anda bisa diukur, 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 agregasikan dalam PivotTable atau laporan. Sebenarnya ini adalah cara yang sangat baik dan mudah untuk mempelajari dan membuat penghitungan Anda sendiri. Saat pemahaman Anda tentang dua fitur Power Pivot yang sangat canggih ini berkembang, Anda mungkin ingin membuat model data yang paling efisien dan akurat yang bisa Anda lakukan. Semoga apa yang telah Anda pelajari di sini membantu. Ada beberapa sumber daya lain yang benar-benar hebat di luar sana yang dapat membantu Anda juga. Berikut adalah beberapa hal: Konteks dalam Rumus DAX, Agregasi di Power Pivot, dan Pusat Sumber Daya DAX. Dan, meskipun sedikit lebih canggih, dan diarahkan ke profesional akuntansi dan keuangan, Pemodelan dan Analisis Data Laba dan Rugi dengan Microsoft Power Pivot di Excel dimuat dengan contoh pemodelan data dan rumus yang hebat.
