Agregasi adalah cara men collapsing, meringkas, atau mengelompokkan data. Ketika Anda memulai dengan data mentah dari tabel atau sumber data lainnya, data seringkali datar, artinya ada banyak detail, namun 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, abstrak, atau meringkas pola sebagai jawaban atas pertanyaan bisnis tertentu.
Agregasi paling umum, seperti agregasi yang menggunakan AVERAGE,COUNT,DISTINCTCOUNT,MAX,MIN,atau SUM dapat dibuat dalam pengukuran secara otomatis menggunakan Jumlah Otomatis. 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 mengagregasi data, Anda mengelompokkan data menurut atribut seperti produk, harga, kawasan, atau tanggal lalu menetapkan rumus yang bekerja pada semua data dalam grup. Misalnya, ketika membuat total untuk satu tahun, Anda membuat agregasi. Jika Kemudian Anda membuat rasio tahun ini pada tahun sebelumnya dan menyajikannya sebagai persentase, ini adalah tipe agregasi yang berbeda.
Keputusan cara mengelompokkan data digerakkan oleh pertanyaan bisnis. Misalnya, agregasi bisa menjawab pertanyaan berikut ini:
Hitungan Berapa banyak transaksi yang ada dalam satu bulan?
Rata-rata Apa artinya penjualan di bulan ini, oleh tenaga penjual?
Nilai minimum dan maksimum Distrik penjualan mana yang merupakan lima teratas dalam hal unit yang terjual?
Untuk membuat perhitungan yang menjawab pertanyaan ini, Anda harus memiliki data detail yang berisi angka untuk dihitung atau dijumlahkan, dan data numerik tersebut harus terkait dengan beberapa cara ke grup yang akan Anda gunakan untuk menata hasil.
Jika data belum berisi nilai yang bisa Anda gunakan untuk mengelompokkan, seperti kategori produk atau nama kawasan geografis tempat penyimpanan berada, Anda mungkin ingin memperkenalkan grup ke data Anda dengan menambahkan kategori. Saat Anda menyusun grup di Excel, Anda harus mengetik secara manual atau memilih grup yang ingin Anda gunakan dari antara kolom di lembar kerja Anda. Namun, dalam sistem relasional, hierarki seperti kategori untuk produk sering disimpan dalam tabel yang berbeda dari fakta atau tabel nilai. Biasanya tabel kategori ditautkan ke data fakta berdasarkan beberapa jenis kunci. Misalnya, anggaplah Anda data anda berisi PC produk, tapi bukan nama produk atau kategorinya. Untuk menambahkan kategori ke lembar Excel kerja datar, Anda harus menyalin di kolom yang berisi nama kategori. Dengan Power Pivotbidang , Anda bisa mengimpor tabel kategori produk ke model data Anda, 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 Anda mengidentifikasi dan menambahkan grup yang akan digunakan, Anda harus memutuskan fungsi matematika mana yang 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 perhitungan. Namun, Power Pivot memungkinkan Anda membuat rumus kustom untuk agregasi, selain agregasi standar yang ditemukan baik dalam Power Pivot maupun Excel.
Misalnya, jika kumpulan nilai dan grup yang sama dengan yang digunakan dalam contoh sebelumnya, Anda dapat membuat agregasi kustom yang menjawab pertanyaan berikut:
Hitungan yang difilter Berapa banyak transaksi yang ada dalam satu bulan, 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 berada dalam peringkat teratas untuk setiap kategori produk, atau untuk setiap promosi penjualan?
Menambahkan Agregasi ke Rumus dan PivotTable
Bila Anda mempunyai gambaran umum tentang bagaimana data Anda harus dikelompokkan menjadi bermakna, dan nilai-nilai yang ingin Anda bekerja dengannya, Anda bisa memutuskan apakah menyusun PivotTable atau membuat perhitungan di dalam tabel. Power Pivot memperluas dan meningkatkan kemampuan asli pengguna Excel membuat agregasi seperti jumlah, hitungan, atau rata-rata. Anda bisa membuat agregasi kustom di Power Pivot baik dalam jendela Power Pivot , atau di dalam area Excel PivotTable.
-
Dalam kolom terhitung ,Anda dapat membuat agregasi yang mempertimbangkan konteks baris saat ini untuk mengambil baris terkait dari tabel lain, lalu menjumlahkan, menghitung, atau menghitung rata-rata nilai tersebut dalam baris terkait.
-
Dalam pengukuran, Anda dapat membuat agregasi dinamis yang menggunakan kedua 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 bisa dibuat dalam Power Pivot dengan menggunakan JumlahOtomatis atau dengan membuat rumus. Anda juga bisa membuat pengukuran implisit menggunakan agregasi standar dalam PivotTable dalam Excel.
Menambahkan Groupings ke PivotTable
Saat Anda mendesain PivotTable, Anda menyeret bidang yang mewakili kelompok, kategori, atau hierarki, ke bagian kolom dan baris PivotTable untuk mengelompokkan data. Kemudian, seret bidang yang berisi nilai numerik ke dalam area nilai agar dapat dihitung, dihitung rata-ratanya, atau dijumlahkan.
Jika Anda menambahkan kategori ke PivotTable tapi data kategori tidak terkait dengan data fakta, Anda mungkin mendapatkan kesalahan atau hasil yang aneh. Biasanya Power Pivot akan mencoba memperbaiki masalah tersebut, dengan mendeteksi dan menyarankan hubungan secara otomatis. Untuk informasi selengkapnya, lihat Bekerja dengan Hubungan di PivotTable.
Anda juga dapat menyeret bidang ke Pemotong, untuk memilih grup data tertentu untuk ditampilkan. Pemotong memungkinkan Anda mengelompokkan, mengurutkan, dan memfilter hasil dalam PivotTable secara interaktif.
Bekerja dengan Mengelompokkan dalam Rumus
Anda juga bisa menggunakan mengelompokkan dan kategori untuk mengagregasi data yang disimpan dalam tabel dengan membuat hubungan antar tabel, lalu membuat rumus yang memaksimalkan hubungan tersebut untuk mencari nilai terkait.
Dengan kata lain, jika Anda ingin membuat rumus yang mengggulasi nilai menurut kategori, Anda terlebih dahulu akan menggunakan hubungan untuk menghubungkan tabel yang berisi data rincian dan tabel yang berisi kategori, lalu menyusun rumusnya.
Untuk informasi selengkapnya tentang cara membuat rumus yang menggunakan pencarian, lihat Pencarian dalam 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 di dalam PivotTable atau bagan, tapi juga dalam rumus yang sangat sering Anda gunakan untuk menghitung agregasi. Filter dapat digunakan di rumus, baik dalam kolom terhitung maupun 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 itu, fungsi agregasi akan berfungsi terhadap semua nilai dalam kolom tabel yang ditentukan. Namun, dalam DAX, Anda bisa membuat filter dinamis atau statis pada tabel, sehingga agregasi bekerja terhadap subkumpulan data berbeda bergantung pada kondisi filter dan konteks saat ini.
Dengan menggabungkan kondisi dan filter dalam rumus, Anda dapat membuat agregasi yang berubah tergantung pada nilai yang disertakan dalam rumus, atau perubahan tergantung pada pilihan judul baris dan judul kolom dalam PivotTable.
Untuk informasi selengkapnya, lihat Memfilter Data dalam Rumus.
Perbandingan Excel Fungsi Agregasi dan Fungsi Agregasi DAX
Tabel berikut ini mencantumkan beberapa fungsi agregasi standar yang disediakan oleh Excel, dan menyediakan link untuk implementasi fungsi-fungsi Power Pivot. Versi DAX fungsi ini berperilaku jauh sama seperti versi Excel, dengan beberapa perbedaan minor dalam sintaks dan penanganan tipe data tertentu.
Fungsi Agregasi Standar
Fungsi |
Penggunaan |
Mengembalikan rata-rata (nilai rata-rata aritmatika) dari semua angka dalam kolom. |
|
Mengembalikan rata-rata (nilai rata-rata aritmatika) dari semua nilai dalam kolom. Menangani teks dan nilai non-numerik. |
|
Menghitung jumlah nilai numerik dalam sebuah kolom. |
|
Menghitung jumlah nilai dalam kolom yang tidak kosong. |
|
Mengembalikan nilai numerik terbesar dalam sebuah kolom. |
|
Mengembalikan nilai terbesar dari serangkaian ekspresi yang dievaluasi ke atas tabel. |
|
Mengembalikan nilai numerik terkecil dalam sebuah kolom. |
|
Mengembalikan nilai terkecil dari serangkaian ekspresi yang dievaluasi ke 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, daripada hanya menambahkan atau rata-rata nilai dalam kolom, fungsi ini memungkinkan Anda membuat ekspresi yang menentukan secara dinamis data untuk diagregasi.
Tabel berikut mencantumkan fungsi agregasi yang tersedia dalam DAX.
Fungsi |
Penggunaan |
Membuat rata-rata kumpulan ekspresi yang dievaluasi pada tabel. |
|
Menghitung serangkaian ekspresi yang dievaluasi pada tabel. |
|
Menghitung jumlah nilai kosong dalam satu kolom. |
|
Menghitung jumlah total baris dalam sebuah tabel. |
|
Menghitung jumlah baris yang dikembalikan dari fungsi tabel bertumpuk, seperti fungsi filter. |
|
Mengembalikan jumlah dari serangkaian ekspresi yang dievaluasi ke atas tabel. |
Perbedaan antara Fungsi DAX Excel dan Agregasi
Meskipun fungsi-fungsi ini memiliki nama yang sama seperti Excel, fungsi tersebut memanfaatkan mesin analitik dalam memori Power Pivotdan telah ditulis ulang untuk bekerja dengan tabel dan kolom. Anda tidak bisa menggunakan rumus DAX dalam Excel kerja, dan sebaliknya. Ini hanya bisa digunakan di jendela Power Pivot dan dalam PivotTable yang didasarkan pada Power Pivot data. Selain itu, meskipun fungsi memiliki nama yang identik, perilakunya mungkin akan sedikit berbeda. Untuk informasi selengkapnya, lihat topik referensi fungsi individual.
Cara kolom dievaluasi dalam agregasi juga berbeda dari cara Excel agregasi. Contoh dapat membantu menggambarkan.
Misalnya, Anda ingin mendapatkan jumlah nilai dalam kolom Jumlah dalam tabel Sales, sehingga rumus berikut akan dibuat:
=SUM('Sales'[Amount])
Dalam kasus paling sederhana, fungsi mendapatkan nilai dari kolom tunggal yang tidak difilter, dan hasilnya sama seperti di Excel, yang selalu menambahkan nilai di kolom, Jumlah. Namun, Power Pivot, rumus diinterpretasikan sebagai "Dapatkan nilai dalam Jumlah untuk setiap baris dari 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 pada tabel, atau jika nilai dihitung berdasarkan agregasi lain yang mungkin difilter. Untuk informasi selengkapnya, lihat Konteks dalam Rumus DAX.
Fungsi Inteligensi Waktu DAX
Selain fungsi agregasi tabel yang diuraikan 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 periode kalender tertentu. |
|
Menghitung nilai di akhir periode kalender sebelum periode tertentu. |
|
Menghitung nilai di atas interval yang dimulai pada hari pertama periode dan berakhir di tanggal terbaru di kolom tanggal yang ditentukan. |
Fungsi lainnya di bagian fungsi Kecerdasan Waktu ( FungsiKecerdasanWaktu ) adalah fungsi yang bisa 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 untuk fungsi lain untuk menghitung agregasi kustom hanya untuk tanggal tersebut.