Agregasi adalah cara menciutkan, meringkas, atau mengelompokkan data. Ketika Anda memulai dengan data mentah dari tabel atau sumber data lainnya, data sering datar, yang berarti ada banyak detail, tetapi belum ditata atau dikelompokkan dengan cara apa pun. Kurangnya ringkasan atau struktur ini mungkin menyulitkan untuk menemukan pola dalam data. Bagian penting dari pemodelan data adalah menentukan agregasi yang menyederhanakan, mengabstrak, atau meringkas pola dalam menjawab pertanyaan bisnis tertentu.
Agregasi paling umum, seperti yang menggunakan AVERAGE, COUNT, DISTINCTCOUNT, MAX, MIN, atau SUM dapat dibuat secara otomatis menggunakan JumlahOtomatis. Tipe agregasi lainnya, seperti AVERAGEX, COUNTX, COUNTROWS, atau SUMXmengembalikan tabel, dan memerlukan rumus yang dibuat menggunakan Data Analysis Expressions (DAX).
Memahami Agregasi di Power Pivot
Memilih Grup untuk Agregasi
Saat anda menggabungkan data, anda mengelompokkan data menurut atribut seperti produk, harga, kawasan, atau tanggal lalu menentukan rumus yang bekerja pada semua data dalam grup. Misalnya, ketika membuat total selama satu tahun, Anda membuat agregasi. Jika Anda kemudian membuat rasio tahun ini di tahun sebelumnya dan menyajikannya sebagai persentase, itu adalah tipe agregasi yang berbeda.
Keputusan cara mengelompokkan data didorong oleh pertanyaan bisnis. Misalnya, agregasi bisa menjawab pertanyaan berikut:
Penting Berapa banyak transaksi yang ada dalam sebulan?
Averages Apa arti penjualan dalam bulan ini, oleh tenaga penjual?
Nilai minimum dan maksimum Distrik penjualan mana yang lima teratas dalam hal penjualan unit?
Untuk membuat penghitungan yang menjawab pertanyaan ini, Anda harus memiliki data mendetail yang berisi angka untuk dihitung atau dijumlahkan, dan data numerik tersebut harus terkait dalam beberapa cara ke grup yang akan Anda gunakan untuk menata hasil.
Jika data belum berisi nilai yang bisa Anda gunakan untuk pengelompokan, seperti kategori produk atau nama kawasan geografis tempat penyimpanan berada, Anda mungkin ingin memperkenalkan grup ke data Anda dengan menambahkan kategori. Saat menyusun grup di Excel, Anda harus mengetik secara manual atau memilih grup yang ingin digunakan dari antara kolom di lembar kerja Anda. Namun, dalam sistem relasional, hierarki seperti kategori untuk produk sering disimpan dalam tabel yang berbeda dari tabel fakta atau nilai. Biasanya tabel kategori ditautkan ke data fakta berdasarkan beberapa jenis kunci. Misalnya, Anda merasa bahwa data Anda berisi ID produk, tetapi bukan nama produk atau kategorinya. Untuk menambahkan kategori ke lembar kerja Excel datar, Anda harus menyalin di kolom yang berisi nama kategori. Dengan Power Pivot, Anda bisa mengimpor tabel kategori produk ke model data Anda, membuat hubungan antara tabel dengan data angka dan daftar kategori produk, lalu gunakan kategori untuk mengelompokkan data. Untuk informasi selengkapnya, lihat Create hubungan antar tabel.
Memilih Fungsi untuk Agregasi
Setelah mengidentifikasi dan menambahkan pengelompokan yang akan digunakan, Anda harus memutuskan fungsi matematika mana yang akan digunakan untuk agregasi. Sering kali kata agregasi digunakan sebagai sinonim untuk operasi matematika atau statistik yang digunakan dalam agregasi, seperti jumlah, rata-rata, minimum, atau hitungan. Namun, Power Pivot memungkinkan Anda membuat rumus kustom untuk agregasi, selain agregasi standar yang ditemukan di Power Pivot dan Excel.
Misalnya, dengan kumpulan nilai dan pengelompokan yang sama yang digunakan dalam contoh sebelumnya, Anda dapat membuat agregasi kustom yang menjawab pertanyaan berikut:
Hitungan yang difilter Berapa banyak transaksi yang ada dalam sebulan, tidak termasuk jendela pemeliharaan akhir bulan?
Rasio menggunakan rata-rata seiring waktu Berapa persentase pertumbuhan atau penurunan penjualan dibandingkan periode yang sama tahun lalu?
Nilai minimum dan maksimum yang dikelompokkan Distrik penjualan mana yang menempati peringkat teratas untuk setiap kategori produk, atau untuk setiap promosi penjualan?
Menambahkan Agregasi ke Rumus dan PivotTable
Saat Anda memiliki gambaran umum tentang bagaimana data Anda harus dikelompokkan agar bermakna, dan nilai yang ingin Anda kerjakan, Anda bisa memutuskan apakah akan menyusun PivotTable atau membuat penghitungan di dalam tabel. Power Pivot memperluas dan meningkatkan kemampuan asli Excel untuk membuat agregasi seperti jumlah, hitungan, atau rata-rata. Anda bisa membuat agregasi kustom di Power Pivot baik di dalam jendela Power Pivot , atau di dalam area PivotTable Excel.
-
Dalam kolom terhitung, Anda bisa membuat agregasi yang memperhitungkan konteks baris saat ini untuk mengambil baris terkait dari tabel lain, lalu menjumlahkan, menghitung, atau rata-rata nilai tersebut dalam baris terkait.
-
Dalam ukuran, Anda bisa membuat agregasi dinamis yang menggunakan kedua filter yang ditentukan dalam rumus, dan filter yang dikenakan oleh desain PivotTable dan pilihan Pemotong, judul kolom, dan judul baris. Pengukuran menggunakan agregasi standar dapat dibuat dalam Power Pivot menggunakan JumlahOtomatis atau dengan membuat rumus. Anda juga bisa membuat pengukuran implisit menggunakan agregasi standar dalam PivotTable di Excel.
Menambahkan Pengelompokan ke PivotTable
Saat Anda mendesain PivotTable, Anda menyeret bidang yang mewakili pengelompokan, kategori, atau hierarki, ke bagian kolom dan baris pivotTable untuk mengelompokkan data. Anda lalu menyeret bidang yang berisi nilai numerik ke area nilai sehingga dapat dihitung, dirata-rata, atau dijumlahkan.
Jika Anda menambahkan kategori ke PivotTable tetapi data kategori tidak terkait dengan data fakta, Anda mungkin mendapatkan kesalahan atau hasil yang aneh. Biasanya Power Pivot akan mencoba memperbaiki masalah, dengan mendeteksi dan menyarankan hubungan secara otomatis. Untuk informasi selengkapnya, lihat Bekerja dengan Hubungan dalam PivotTable.
Anda juga bisa menyeret bidang ke pemotong, untuk memilih grup data tertentu untuk ditampilkan. Pemotong memungkinkan Anda mengelompokkan, mengurutkan, dan memfilter hasil secara interaktif dalam PivotTable.
Bekerja dengan Pengelompokan dalam Rumus
Anda juga bisa menggunakan pengelompokan dan kategori untuk mengagregasi data yang disimpan dalam tabel dengan membuat hubungan antar tabel, lalu membuat rumus yang memanfaatkan hubungan tersebut untuk mencari nilai terkait.
Dengan kata lain, jika Anda ingin membuat rumus yang mengelompokkan nilai menurut kategori, Pertama-tama Anda akan menggunakan hubungan untuk menyambungkan tabel yang berisi data detail dan tabel yang berisi kategori, lalu menyusun rumus.
Untuk informasi selengkapnya tentang cara membuat rumus yang menggunakan pencarian, lihat Pencarian di Rumus Power Pivot.
Menggunakan Filter dalam Agregasi
Fitur baru dalam Power Pivot adalah kemampuan untuk menerapkan filter ke kolom dan tabel data, tidak hanya di antarmuka pengguna dan dalam PivotTable atau bagan, tetapi juga dalam rumus yang sangat anda gunakan untuk menghitung agregasi. Filter dapat digunakan dalam rumus baik dalam kolom terhitung maupun dalam s.
Misalnya, dalam fungsi agregasi DAX baru, daripada menentukan nilai yang akan dijumlahkan atau dihitung, Anda dapat menentukan seluruh tabel sebagai argumen. Jika Anda tidak menerapkan filter apa pun ke tabel tersebut, fungsi agregasi akan berfungsi terhadap semua nilai dalam kolom tabel yang ditentukan. Namun, di DAX Anda bisa membuat filter dinamis atau statis pada tabel, sehingga agregasi beroperasi terhadap subkumpulan data yang berbeda tergantung pada kondisi filter dan konteks saat ini.
Dengan menggabungkan kondisi dan filter dalam rumus Anda bisa membuat agregasi yang berubah bergantung pada nilai yang disediakan dalam rumus, atau yang berubah bergantung pada pilihan judul baris dan judul kolom dalam PivotTable.
Untuk informasi selengkapnya, lihat Memfilter Data dalam Rumus.
Perbandingan Fungsi Agregasi Excel dan Fungsi Agregasi DAX
Tabel berikut ini mencantumkan beberapa fungsi agregasi standar yang disediakan oleh Excel, dan menyediakan link ke implementasi fungsi ini dalam Power Pivot. Versi DAX fungsi ini berperilaku jauh sama seperti versi Excel, dengan beberapa perbedaan kecil dalam sintaks dan penanganan tipe data tertentu.
Fungsi Agregasi Standar
|
Fungsi |
Penggunaan |
|
Mengembalikan rata-rata (rata-rata aritmatika) dari semua angka dalam kolom. |
|
|
Mengembalikan nilai rata-rata (rata-rata aritmatika) dari semua nilai dalam kolom. Menangani nilai teks dan nonnumerik. |
|
|
Menghitung jumlah nilai numerik dalam kolom. |
|
|
Menghitung jumlah nilai dalam kolom yang tidak kosong. |
|
|
Mengembalikan nilai numerik terbesar dalam kolom. |
|
|
Mengembalikan nilai terbesar dari sekumpulan ekspresi yang dievaluasikan di atas tabel. |
|
|
Mengembalikan nilai numerik terkecil dalam kolom. |
|
|
Mengembalikan nilai terkecil dari sekumpulan ekspresi yang dievaluasi di atas tabel. |
|
|
Menambahkan semua angka dalam kolom. |
Fungsi Agregasi DAX
DAX menyertakan fungsi agregasi yang memungkinkan Anda menentukan tabel di mana agregasi akan dijalankan. Oleh karena itu, alih-alih hanya menambahkan atau memanfaatkan nilai dalam kolom, fungsi-fungsi ini memungkinkan Anda membuat ekspresi yang menentukan data secara dinamis untuk diagregasi.
Tabel berikut ini mencantumkan fungsi agregasi yang tersedia di DAX.
|
Fungsi |
Penggunaan |
|
Menghitung rata-rata sekumpulan ekspresi yang dievaluasi di atas tabel. |
|
|
Menghitung sekumpulan ekspresi yang dievaluasi di atas tabel. |
|
|
Menghitung jumlah nilai kosong dalam kolom. |
|
|
Menghitung jumlah total baris dalam tabel. |
|
|
Menghitung jumlah baris yang dikembalikan dari fungsi tabel bertumpuk, seperti fungsi filter. |
|
|
Mengembalikan jumlah sekumpulan ekspresi yang dievaluasikan di atas tabel. |
Perbedaan antara Fungsi Agregasi DAX dan Excel
Meskipun fungsi-fungsi ini memiliki nama yang sama seperti fungsi Excel, fungsi ini menggunakan mesin analitik dalam memori Power Pivotdan telah ditulis ulang untuk bekerja dengan tabel dan kolom. Anda tidak bisa menggunakan rumus DAX dalam buku kerja Excel, dan sebaliknya. PivotTable hanya dapat digunakan di jendela Power Pivot dan pivotTable yang didasarkan pada data Power Pivot . Selain itu, meskipun fungsi memiliki nama yang identik, perilakunya mungkin sedikit berbeda. Untuk informasi selengkapnya, lihat topik referensi fungsi individual.
Cara kolom dievaluasi dalam agregasi juga berbeda dari cara Excel menangani agregasi. Contoh mungkin membantu menggambarkan.
Misalkan Anda ingin mendapatkan jumlah nilai dalam kolom Jumlah dalam tabel Penjualan, sehingga Anda membuat rumus berikut ini:
=SUM('Sales'[Amount])
Dalam kasus yang paling sederhana, fungsi mendapatkan nilai dari satu kolom yang tidak difilter, dan hasilnya sama seperti di Excel, yang selalu hanya menambahkan nilai dalam kolom, Amount. Namun, dalam Power Pivot, rumus diinterpretasikan sebagai "Dapatkan nilai dalam Jumlah untuk setiap baris tabel Penjualan, lalu tambahkan nilai individual tersebut. Power Pivot mengevaluasi setiap baris di mana agregasi dijalankan dan menghitung nilai skalar tunggal untuk setiap baris, lalu melakukan agregasi pada nilai tersebut. Oleh karena itu, hasil rumus bisa berbeda jika filter telah diterapkan ke tabel, atau jika nilai dihitung berdasarkan agregasi lain yang mungkin difilter. Untuk informasi selengkapnya, lihat Konteks dalam Rumus DAX.
Fungsi Kecerdasan Waktu DAX
Selain fungsi agregasi tabel yang dijelaskan di bagian sebelumnya, DAX memiliki fungsi agregasi yang berfungsi dengan tanggal dan waktu yang Anda tentukan, untuk menyediakan kecerdasan waktu bawaan. Fungsi ini menggunakan rentang tanggal untuk mendapatkan nilai terkait dan menggabungkan nilai. Anda juga bisa membandingkan nilai di seluruh rentang tanggal.
Tabel berikut ini mencantumkan fungsi kecerdasan waktu yang bisa digunakan untuk agregasi.
|
Fungsi |
Penggunaan |
|
Menghitung nilai di akhir kalender periode tertentu. |
|
|
Menghitung nilai di akhir kalender periode sebelum periode tertentu. |
|
|
Menghitung nilai di atas interval yang dimulai pada hari pertama periode dan berakhir pada tanggal terbaru dalam kolom tanggal yang ditentukan. |
Fungsi lain dalam bagian fungsi Kecerdasan Waktu (Fungsi Kecerdasan Waktu) adalah fungsi yang dapat digunakan untuk mengambil tanggal atau rentang kustom tanggal untuk digunakan dalam agregasi. Misalnya, Anda bisa menggunakan fungsi DATESINPERIOD untuk mengembalikan rentang tanggal, dan menggunakan kumpulan tanggal tersebut sebagai argumen ke fungsi lain untuk menghitung agregasi kustom hanya untuk tanggal tersebut.
