Saat pertama kali mempelajari cara menggunakan Power Pivot, sebagian besar pengguna menemukan bahwa kekuatan sebenarnya terletak pada pengagregatan atau penghitungan hasil dengan cara tertentu. Jika data memiliki kolom dengan nilai numerik, Anda dapat dengan mudah mengagregasinya dengan memilihnya dalam Daftar Bidang PivotTable atau Power View. Berdasarkan alam, karena bersifat numerik, akan secara otomatis dijumlahkan, dirata-ratakan, dihitung, atau jenis agregasi apa pun yang Anda pilih. Ini dikenal sebagai ukuran implisit. Ukuran implisit sangat bagus untuk agregasi yang cepat dan mudah, tetapi memiliki batasan, dan batas tersebut hampir selalu dapat diatasi dengan pengukuran eksplisit dan kolom yang dihitung.
Pertama-tama mari kita lihat contoh di mana kita menggunakan kolom terhitung untuk menambahkan nilai teks baru untuk setiap baris dalam tabel yang bernama Produk. Setiap baris dalam tabel Produk berisi segala macam 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 menyertakan nama kategori produk dari tabel Kategori Produk. Dalam tabel Produk, kita dapat membuat kolom terhitung bernama Kategori Produk seperti ini:
Rumus Kategori Produk baru kami menggunakan fungsi DAX TERKAIT untuk mendapatkan nilai dari kolom ProductCategoryName dalam tabel Kategori Produk terkait, lalu masukkan nilai tersebut untuk setiap produk (setiap baris) dalam tabel Produk.
Ini adalah contoh bagus tentang bagaimana kita dapat menggunakan kolom terhitung untuk menambahkan nilai tetap untuk setiap baris yang dapat kita gunakan nanti di area BARIS, KOLOM, atau FILTER pada PivotTable atau dalam 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 terdapat hubungan antara tabel Penjualan dan tabel Kategori Produk. Dalam tabel Penjualan, kita memiliki kolom yang memiliki jumlah penjualan dan kolom lain yang memiliki biaya.
Kita dapat membuat kolom terhitung yang menghitung jumlah laba untuk setiap baris dengan mengurangi nilai di kolom HPP dari nilai di kolom SalesAmount, seperti ini:
Sekarang, kita dapat membuat PivotTable dan menyeret bidang Kategori Produk ke COLUMNS, dan bidang Profit baru kita ke area VALUES (kolom dalam tabel di PowerPivot adalah Bidang dalam Daftar Bidang PivotTable). Hasilnya adalah ukuran implisit bernama Sum of Profit. 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 di VALUES. Jika kita menempatkan Profit di area COLUMNS, PivotTable kita akan terlihat seperti ini:
Bidang Laba kami tidak memberikan informasi yang berguna jika ditempatkan di area KOLOM, BARIS, atau FILTER. Ini hanya masuk akal sebagai nilai agregat di area VALUES.
Apa yang telah kami lakukan adalah membuat kolom bernama Laba yang menghitung margin laba untuk setiap baris dalam tabel Penjualan. Kami kemudian menambahkan Profit ke area VALUES di PivotTable kami, secara otomatis membuat pengukuran implisit, di mana hasil dihitung untuk setiap kategori produk. Jika Anda berpikir kami benar-benar menghitung keuntungan untuk kategori produk kami dua kali, Anda benar. Pertama-tama kami menghitung laba untuk setiap baris dalam tabel Penjualan, lalu kami menambahkan Laba ke area NILAI tempat laba digabungkan untuk setiap kategori produk. Jika Anda juga berpikir kami tidak benar-benar perlu membuat kolom kalkulasi Keuntungan, Anda juga benar. Tapi, lalu bagaimana kita menghitung keuntungan kita tanpa membuat kolom kalkulasi Keuntungan?
Keuntungan, akan benar-benar lebih baik dihitung sebagai ukuran eksplisit.
Untuk saat ini, kita akan meninggalkan kolom Laba yang dihitung di tabel Penjualan dan Kategori Produk di KOLOM dan Laba dalam NILAI PivotTable kita, untuk membandingkan hasil kita.
Di area penghitungan tabel Penjualan, kita akan membuat ukuran bernama Total Laba (untuk menghindari konflik penamaan). Pada akhirnya, itu akan menghasilkan hasil yang sama seperti yang kami lakukan sebelumnya, tetapi tanpa kolom yang dihitung Profit.
Pertama, dalam tabel Sales, kami memilih kolom SalesAmount, lalu klik AutoSum untuk membuat pengukuran Jumlah SalesAmount yang eksplisit. Ingat, pengukuran eksplisit adalah pengukuran yang kita buat di area penghitungan tabel di Power Pivot. Kami melakukan hal yang sama untuk kolom HPP. Kami akan mengganti nama ini menjadi Total SalesAmount dan Total COGS agar lebih mudah diidentifikasi.
Kemudian kita membuat ukuran lain dengan rumus ini:
Total Keuntungan:=[Total SalesAmount] - [Total HPP]
Catatan
Kita juga dapat menulis rumus kita sebagai Total Profit:=SUM([SalesAmount]) - SUM([COGS]), tetapi dengan membuat ukuran Total SalesAmount dan Total COGS yang terpisah, kita juga dapat menggunakannya di PivotTable kita, dan kita dapat menggunakannya sebagai argumen dalam segala macam rumus pengukuran lainnya.
Setelah mengubah format ukuran Total Laba baru kami ke mata uang, kami dapat menambahkannya ke PivotTable kami.
Anda dapat melihat ukuran Laba Total baru kami mengembalikan hasil yang sama dengan membuat kolom kalkulasi Laba lalu menempatkannya di NILAI. Perbedaannya adalah ukuran Laba Total kami jauh lebih efisien dan membuat model data kami lebih bersih dan lebih ramping karena kami menghitung pada saat dan hanya untuk bidang yang kami pilih untuk PivotTable kami. Kita tidak benar-benar membutuhkan kolom kalkulasi Laba itu.
Mengapa bagian terakhir ini penting? Kolom terhitung menambahkan data ke model data, dan data menggunakan memori. Jika kita merefresh model data, sumber daya pemrosesan juga diperlukan untuk menghitung ulang semua nilai di kolom Keuntungan. Kita tidak perlu menggunakan sumber daya seperti ini karena kita benar-benar ingin menghitung keuntungan saat kita memilih bidang yang kita inginkan untuk Keuntungan di PivotTable, seperti kategori produk, wilayah, atau menurut tanggal.
Mari kita lihat contoh lain. Satu di mana kolom terhitung membuat hasil yang pada pandangan pertama terlihat benar, tetapi....
Dalam contoh ini, kita ingin menghitung jumlah penjualan sebagai persentase dari total penjualan. Kami membuat kolom terhitung bernama % Penjualan dalam tabel Penjualan, seperti ini:
Rumus kami menyatakan: Untuk setiap baris dalam tabel Penjualan, bagi jumlah dalam kolom SalesAmount dengan total SUM dari semua jumlah dalam kolom SalesAmount.
Jika kita membuat PivotTable dan menambahkan Kategori Produk ke KOLOM dan memilih kolom % Penjualan baru untuk memasukkannya ke dalam NILAI, kita mendapatkan jumlah total % Penjualan untuk setiap kategori produk kita.
Baik. Ini terlihat bagus sejauh ini. Tapi, mari kita tambahkan pemotong. Kami menambahkan Calendar Year lalu memilih tahun. Dalam hal ini, kami memilih 2007. Inilah yang kami dapatkan.
Sekilas, ini mungkin masih tampak benar. Tapi, persentase kami harus benar-benar total 100%, karena kami ingin mengetahui persentase dari total penjualan untuk masing-masing kategori produk kami untuk tahun 2007. Jadi apa yang salah?
Kolom % Penjualan kami menghitung persentase untuk setiap baris yang merupakan nilai dalam kolom SalesAmount dibagi dengan jumlah total semua nilai di kolom SalesAmount. Nilai dalam kolom terhitung tetap. Mereka adalah hasil yang tidak dapat diubah untuk setiap baris dalam tabel. Ketika kami menambahkan % Penjualan ke PivotTable kami, itu digabungkan sebagai jumlah semua nilai dalam kolom SalesAmount. 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 jelaskan di sini.
Kami dapat menghapus kolom kalkulasi % Penjualan karena itu tidak akan membantu kami. Sebagai gantinya, Kami akan membuat pengukuran yang menghitung persentase total penjualan kami dengan benar, terlepas dari filter atau pemotong yang diterapkan.
Ingat pengukuran TotalSalesAmount yang kami buat sebelumnya, yang hanya menjumlahkan kolom SalesAmount? Kami menggunakannya sebagai argumen dalam ukuran Total Laba kami, dan kami akan menggunakannya lagi sebagai argumen di bidang kalkulasi 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 ketika Anda memerlukan hasilnya sebagai argumen. Hal 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 Penjualan:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())
Rumus ini menyatakan: Bagi 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 di Referensi DAX.
Sekarang, jika kita menambahkan % baru dari Total Penjualan ke PivotTable, kita mendapatkan:
Itu terlihat lebih baik. Sekarang % dari Total Penjualan untuk setiap kategori produk dihitung sebagai persentase dari total penjualan untuk tahun 2007. Jika kami memilih tahun yang berbeda, atau lebih dari satu tahun di pemotong Tahun Kalender, kami mendapatkan persentase baru untuk kategori produk kami, tetapi total keseluruhan kami masih 100%. Kami juga dapat menambahkan pemotong dan filter lain. % dari pengukuran Total Penjualan kami akan selalu menghasilkan persentase dari total penjualan terlepas dari pemotong atau filter yang diterapkan. Dengan pengukuran, hasilnya selalu dihitung sesuai dengan konteks yang ditentukan oleh bidang di KOLOM dan BARIS, dan oleh filter atau pemotong apa pun yang diterapkan. Inilah kekuatan langkah-langkah.
Berikut adalah beberapa panduan untuk membantu Anda memutuskan apakah kolom atau ukuran terhitung tepat atau tidak untuk kebutuhan penghitungan tertentu:
Menggunakan kolom terhitung
- Jika Anda ingin data baru muncul di ROWS, COLUMNS, atau in FILTERS di PivotTable, atau di AXIS, LEGEND, atau, TILE BY dalam visualisasi Power View, Anda harus menggunakan kolom terhitung. Sama seperti kolom data biasa, kolom terhitung dapat digunakan sebagai bidang di area mana pun, dan jika numerik, kolom tersebut juga dapat digabungkan dalam VALUES.
- Jika Anda ingin data baru Anda menjadi nilai tetap untuk baris. Misalnya, Anda memiliki tabel tanggal dengan kolom tanggal, dan Anda menginginkan kolom lain yang hanya berisi angka bulan. Anda dapat membuat kolom terhitung yang hanya menghitung jumlah bulan dari tanggal di 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 dapat digabungkan dalam VALUES. Misalnya, =FORMAT('Tanggal'[Tanggal],"mmmm") memberi kita nama bulan untuk setiap tanggal dalam kolom Tanggal dalam tabel Tanggal.
Menggunakan pengukuran
- Jika hasil penghitungan Anda akan selalu bergantung pada bidang lain yang Anda pilih di PivotTable.
- Jika Anda perlu melakukan penghitungan yang lebih kompleks, seperti menghitung hitungan berdasarkan filter tertentu, atau menghitung nilai tahun-ke-tahun, atau varians, gunakan bidang terhitung.
- Jika Anda ingin meminimalkan ukuran buku kerja dan memaksimalkan kinerjanya, buat penghitungan sebanyak mungkin. Dalam banyak kasus, semua penghitungan Anda dapat diukur, secara signifikan mengurangi ukuran buku kerja dan mempercepat waktu refresh.
Perlu diingat, tidak ada salahnya membuat kolom terhitung seperti yang kita lakukan dengan kolom Laba kita, lalu mengagregasikannya dalam PivotTable atau laporan. Ini sebenarnya cara yang sangat bagus dan mudah untuk mempelajari dan membuat perhitungan Anda sendiri. Seiring berkembangnya pemahaman Anda tentang dua fitur Power Pivot yang sangat canggih ini, Anda perlu membuat model data yang paling efisien dan akurat. Semoga apa yang Anda pelajari di sini dapat membantu. Ada beberapa sumber daya lain yang sangat hebat di luar sana yang dapat membantu Anda juga. Berikut ini hanya beberapa: Konteks dalam rumus DAX, agregasi di Power Pivot, dan Pusat Sumber Daya DAX. Dan, meskipun sedikit lebih canggih, dan diarahkan kepada profesional akuntansi dan keuangan, sampel Pemodelan dan Analisis Data Laba Rugi dengan Microsoft Power Pivot di Excel sarat dengan contoh pemodelan data dan rumus yang hebat.