Agregasi adalah cara untuk menciutkan, meringkas, atau mengelompokkan data. Ketika Anda memulai dengan data mentah dari tabel atau sumber data lainnya, data sering kali datar, yang berarti ada banyak detail, tetapi belum diatur 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, mengabstraksi, atau meringkas pola sebagai jawaban atas pertanyaan bisnis tertentu.
Agregasi yang paling umum, seperti yang menggunakan AVERAGE,COUNT, DISTINCTCOUNT,MAX, MIN, atau SUM dapat dibuat dalam suatu pengukuran secara otomatis menggunakan AutoSum. Jenis agregasi lain, seperti AVERAGEX, COUNTX, COUNTROWS, atau SUMX mengembalikan tabel, dan memerlukan rumus yang dibuat menggunakan Ekspresi Analisis Data (DAX).
Memahami agregasi di Power Pivot
Memilih grup untuk agregasi
Ketika Anda menggabungkan data, Anda mengelompokkan data berdasarkan atribut seperti produk, harga, kawasan, atau tanggal, lalu menentukan rumus yang berfungsi pada semua data dalam grup. Misalnya, jika membuat total untuk setahun, Anda membuat agregasi. Jika Anda kemudian membuat rasio tahun ini dibandingkan tahun sebelumnya dan menyajikannya dalam bentuk persentase, itu adalah jenis agregasi yang berbeda.
Keputusan tentang cara mengelompokkan data didorong oleh pertanyaan bisnis. Misalnya, agregasi dapat menjawab pertanyaan berikut:
Jumlah Berapa banyak transaksi yang ada dalam sebulan?
Rata-rata Berapa rata-rata penjualan pada bulan ini, menurut tenaga penjualan?
Nilai minimum dan maksimum Distrik penjualan mana yang merupakan lima teratas dalam hal unit yang terjual?
Untuk membuat penghitungan yang menjawab pertanyaan-pertanyaan ini, Anda harus memiliki data terperinci yang berisi angka yang akan dihitung atau dijumlahkan, dan data numerik tersebut harus terkait dengan grup yang akan Anda gunakan untuk menyusun hasil.
Jika data belum berisi nilai yang dapat Anda gunakan untuk pengelompokan, seperti kategori produk atau nama wilayah geografis tempat penyimpanan berada, Anda mungkin ingin memperkenalkan grup ke data dengan menambahkan kategori. Ketika membuat grup di Excel, Anda harus mengetik atau memilih grup yang ingin digunakan secara manual 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 dengan semacam kunci. Misalnya, Anda menemukan bahwa data Anda berisi ID produk, tetapi bukan nama produk atau kategorinya. Untuk menambahkan kategori ke lembar kerja Excel datar, Anda harus menyalin dalam kolom yang berisi nama kategori. Dengan Power Pivot, Anda dapat mengimpor tabel kategori produk ke model data, membuat hubungan antara tabel dengan data angka dan daftar kategori produk, lalu menggunakan kategori untuk mengelompokkan data. Untuk informasi selengkapnya, lihat Membuat 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. Seringkali 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, mengingat kumpulan nilai dan pengelompokan yang sama dengan yang digunakan dalam contoh sebelumnya, Anda dapat membuat agregasi kustom yang menjawab pertanyaan berikut:
Jumlah yang difilter Berapa banyak transaksi yang ada dalam sebulan, tidak termasuk periode pemeliharaan akhir bulan?
Rasio menggunakan rata-rata dari waktu ke waktu Berapa persentase pertumbuhan atau penurunan penjualan dibandingkan periode yang sama tahun lalu?
Mengelompokkan nilai minimum dan maksimum Distrik penjualan mana yang menduduki peringkat teratas untuk setiap kategori produk, atau untuk setiap promosi penjualan?
Menambahkan agregasi ke rumus dan PivotTable
Ketika Anda memiliki gagasan umum tentang bagaimana data Anda harus dikelompokkan agar bermakna, dan nilai yang ingin Anda gunakan, Anda dapat memutuskan apakah akan membuat PivotTable atau membuat penghitungan dalam tabel. Power Pivot memperluas dan meningkatkan kemampuan asli Excel untuk membuat agregasi seperti jumlah, hitungan, atau rata-rata. Anda dapat membuat agregasi kustom di Power Pivot baik dalam jendela Power Pivot, maupun dalam area PivotTable Excel.
- Dalam kolom terhitung, Anda dapat membuat agregasi yang memperhitungkan konteks baris saat ini untuk mengambil baris terkait dari tabel lain, lalu menjumlahkan, menghitung, atau menghitung nilai rata-rata dalam baris terkait.
- Dalam suatu ukuran, Anda dapat membuat agregasi dinamis yang menggunakan filter yang ditentukan dalam rumus, dan filter yang diberlakukan oleh desain PivotTable dan pilihan Pemotong, judul kolom, dan judul baris. Pengukuran yang menggunakan agregasi standar dapat dibuat di Power Pivot menggunakan AutoSum atau dengan membuat rumus. Anda juga dapat membuat pengukuran implisit menggunakan agregasi standar dalam PivotTable di Excel.
Menambahkan pengelompokan ke PivotTable
Saat mendesain PivotTable, seret bidang yang mewakili pengelompokan, kategori, atau hierarki ke bagian kolom dan baris pada PivotTable untuk mengelompokkan data. Anda kemudian menyeret bidang yang berisi nilai numerik ke area nilai sehingga dapat dihitung, dirata-ratakan, atau dijumlahkan.
Jika 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 di PivotTable.
Anda juga dapat menyeret bidang ke dalam 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 dapat menggunakan pengelompokan dan kategori untuk menggabungkan 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 ingin membuat rumus yang mengelompokkan nilai berdasarkan kategori, Anda harus menggunakan hubungan terlebih dahulu untuk menghubungkan 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 di 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 Anda gunakan untuk menghitung agregasi. Filter dapat digunakan dalam rumus baik dalam kolom terhitung maupun dalam s.
Misalnya, dalam fungsi agregasi DAX baru, Anda dapat menentukan seluruh tabel sebagai argumen sebagai argumen sebagai fungsi agregasi DAX baru, alih-alih menentukan nilai yang akan dijumlahkan atau dihitung. Jika Anda tidak menerapkan filter apa pun ke tabel tersebut, fungsi agregasi akan bekerja terhadap semua nilai dalam kolom tabel yang ditentukan. Namun, di DAX Anda dapat membuat filter dinamis atau statis pada tabel, sehingga agregasi beroperasi terhadap subset data yang berbeda tergantung pada kondisi filter dan konteks saat ini.
Dengan menggabungkan kondisi dan filter dalam rumus, Anda dapat membuat agregasi yang berubah tergantung pada nilai yang disediakan dalam rumus, atau yang berubah tergantung pada pilihan baris, judul, 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 tautan ke implementasi fungsi ini di Power Pivot. Versi DAX dari fungsi ini memiliki perilaku yang sangat mirip dengan versi Excel, dengan beberapa perbedaan kecil dalam sintaks dan penanganan tipe data tertentu.
Fungsi agregasi standar
| Fungsi | Penggunaan |
|---|---|
| AVERAGE | Mengembalikan nilai rata-rata (nilai rata-rata aritmatika) dari semua angka dalam kolom. |
| AVERAGEA | Mengembalikan nilai rata-rata (nilai rata-rata aritmatika) dari semua nilai dalam kolom. Menangani teks dan nilai nonnumerik. |
| COUNT | Menghitung jumlah nilai numerik dalam kolom. |
| COUNTA | Menghitung jumlah nilai dalam kolom yang tidak kosong. |
| MAX | Mengembalikan nilai numerik terbesar dalam kolom. |
| MAXX | Mengembalikan nilai terbesar dari kumpulan ekspresi yang dievaluasi pada tabel. |
| MIN | Mengembalikan nilai numerik terkecil dalam kolom. |
| MINX | Mengembalikan nilai terkecil dari kumpulan ekspresi yang dievaluasi pada tabel. |
| SUM | Menambahkan semua angka dalam kolom. |
Fungsi agregasi DAX
DAX menyertakan fungsi agregasi yang memungkinkan Anda menentukan tabel tempat agregasi akan dilakukan. Oleh karena itu, alih-alih hanya menambahkan atau menghitung nilai rata-rata dalam kolom, fungsi ini memungkinkan Anda membuat ekspresi yang secara dinamis menentukan data yang akan digabungkan.
Tabel berikut ini mencantumkan fungsi agregasi yang tersedia di DAX.
| Fungsi | Penggunaan |
|---|---|
| AVERAGEX | Menghitung rata-rata sekumpulan ekspresi yang dievaluasi pada tabel. |
| COUNTAX | Menghitung sekumpulan ekspresi yang dievaluasi pada tabel. |
| COUNTBLANK | Menghitung jumlah nilai kosong dalam kolom. |
| COUNTX | Menghitung jumlah total baris dalam tabel. |
| HITUNGAN | Menghitung jumlah baris yang dikembalikan dari fungsi tabel bertumpuk, seperti fungsi filter. |
| SUMX | Mengembalikan jumlah dari kumpulan ekspresi yang dievaluasi pada tabel. |
Perbedaan antara Fungsi Agregasi DAX dan Excel
Meskipun fungsi ini memiliki nama yang sama dengan fungsi Excel, fungsi ini menggunakan mesin analitik dalam memori Power Pivot dan telah ditulis ulang untuk bekerja dengan tabel dan kolom. Anda tidak dapat menggunakan rumus DAX di buku kerja Excel, dan sebaliknya. Profil hanya dapat digunakan di jendela Power Pivot dan dalam PivotTable yang didasarkan pada data Power Pivot. Selain itu, meskipun fungsinya memiliki nama yang identik, perilakunya mungkin sedikit berbeda. Untuk informasi selengkapnya, lihat topik referensi fungsi individu.
Cara kolom dievaluasi dalam agregasi juga berbeda dengan cara Excel menangani agregasi. Sebuah contoh dapat membantu mengilustrasikan.
Misalkan Anda ingin mendapatkan jumlah nilai dalam kolom Jumlah dalam tabel Penjualan, jadi Anda membuat rumus berikut:
=SUM('Sales'[Amount])
Dalam kasus yang paling sederhana, fungsi ini mendapatkan nilai dari satu kolom yang tidak difilter, dan hasilnya sama seperti di Excel, yang selalu hanya menjumlahkan nilai dalam kolom, Jumlah. Namun, di Power Pivot, rumus ditafsirkan sebagai "Dapatkan nilai dalam Jumlah untuk setiap baris tabel Penjualan, lalu tambahkan masing-masing nilai tersebut. Power Pivot mengevaluasi setiap baris tempat agregasi dilakukan dan menghitung satu nilai skalar untuk setiap baris, lalu melakukan agregasi pada nilai tersebut. Oleh karena itu, hasil rumus dapat 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 DAX Time Intelligence
Selain fungsi agregasi tabel yang dijelaskan di bagian sebelumnya, DAX memiliki fungsi agregasi yang berfungsi dengan tanggal dan waktu yang Anda tentukan, untuk memberikan kecerdasan waktu bawaan. Fungsi ini menggunakan rentang tanggal untuk mendapatkan nilai terkait dan mengumpulkan nilai. Anda juga dapat membandingkan nilai di seluruh rentang tanggal.
Tabel berikut ini mencantumkan fungsi kecerdasan waktu yang dapat digunakan untuk agregasi.
| Fungsi | Penggunaan |
|---|---|
|
PENUTUPANBALANCEMONTH PENUTUPANBALANCEQUARTERS PENUTUPANBALANCEYEAR |
Menghitung nilai di akhir kalender dari periode tertentu. |
|
OPENINGBALANCEMONTH PEMBUKAANKESEIMBANGANKUARTAL PEMBUKAANKESEIMBANGAN tahun |
Menghitung nilai pada akhir kalender periode sebelum periode tertentu. |
|
TOTALMTD TOTALYTD TOTALQTD |
Menghitung nilai selama interval yang dimulai pada hari pertama periode dan berakhir pada tanggal terakhir dalam kolom tanggal yang ditentukan. |
Fungsi lain di bagian fungsi Kecerdasan Waktu (Fungsi Kecerdasan Waktu) adalah fungsi yang dapat digunakan untuk mengambil tanggal atau rentang tanggal kustom untuk digunakan dalam agregasi. Misalnya, Anda dapat menggunakan fungsi DATESINPERIOD untuk mengembalikan rentang tanggal, dan menggunakan kumpulan tanggal tersebut sebagai argumen untuk fungsi lain untuk menghitung agregasi kustom hanya untuk tanggal tersebut.