Agregasi di PowerPivot

Agregasi adalah cara runtuh, meringkas, atau mengelompokkan data. Saat Anda memulai dengan data Raw dari tabel atau sumber data lain, data sering kali 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 untuk menentukan agregasi yang menyederhanakan, meringkas, atau merangkum pola dalam menjawab pertanyaan bisnis tertentu.

Agregasi paling umum, seperti yang menggunakan Average, Count, kehormatan, Max, min, atau Sum dapat dibuat secara otomatis dengan menggunakan autosum. measure 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 berfungsi pada semua data dalam grup. Misalnya, saat Anda membuat total selama setahun, Anda membuat agregasi. Jika Anda membuat rasio tahun ini pada tahun sebelumnya dan menyajikannya sebagai persentase, itu adalah tipe agregasi yang berbeda.

Keputusan cara mengelompokkan data didorong oleh pertanyaan bisnis. Misalnya, agregasi dapat menjawab pertanyaan berikut:

Ang   Berapa banyak transaksi yang ada dalam sebulan?

MA    Apa artinya penjualan rata-rata bulan ini, oleh tenaga penjual?

Nilai minimum dan maksimum    Kawasan penjualan mana yang menjadi lima besar dari ketentuan unit yang terjual?

Untuk membuat penghitungan yang menjawab pertanyaan ini, Anda harus memiliki data mendetail yang berisi angka untuk dihitung atau jumlah, dan bahwa data numerik 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 Anda membuat grup di Excel, Anda harus mengetikkan atau memilih grup yang ingin Anda gunakan dari antara kolom di lembar kerja Anda secara manual. Namun, dalam sistem relasional, hierarki seperti kategori untuk produk sering disimpan dalam tabel berbeda dari tabel fakta atau nilai. Biasanya tabel kategori ditautkan ke data fakta dengan beberapa jenis 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 di kolom yang berisi nama kategori. Dengan Power Pivot, Anda bisa mengimpor tabel kategori produk ke model data Anda, membuat hubungan antara tabel dengan daftar data angka dan kategori produk, lalu gunakan kategori untuk mengelompokkan data. Untuk informasi selengkapnya, lihat membuat hubungan antar tabel.

Memilih fungsi untuk agregasi

Setelah Anda mengidentifikasi dan menambahkan pengelompokan yang akan digunakan, Anda harus memutuskan fungsi matematika yang akan digunakan untuk agregasi. Seringkali agregasi kata 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 berjalannya waktu    Berapa persentase pertumbuhan atau penurunan penjualan dibandingkan dengan periode yang sama tahun lalu?

Nilai minimum dan maksimum yang dikelompokkan    Kawasan penjualan mana yang menduduki 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 akan dikelompokkan menjadi bermakna, dan nilai yang ingin Anda kerjakan, Anda bisa memutuskan apakah akan membuat PivotTable atau membuat penghitungan di dalam tabel. Power Pivot memperluas dan meningkatkan kemampuan Excel asli untuk membuat agregasi seperti jumlah, hitungan, atau rata-rata. Anda dapat membuat agregasi kustom dalam Power Pivot baik di dalam jendela Power Pivot , atau 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 di baris terkait.

  • Dalam ukuran, Anda bisa membuat agregasi dinamis yang menggunakan kedua filter yang ditentukan di dalam rumus, dan filter yang diterapkan oleh desain PivotTable dan pemilihan pemotong, judul kolom, dan judul baris. Pengukuran 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 Anda mendesain PivotTable, Anda menyeret bidang yang mewakili pengelompokan, kategori, atau hierarki, ke bagian kolom dan baris PivotTable untuk mengelompokkan data. Anda kemudian seret bidang yang berisi nilai numerik ke area nilai sehingga bisa dihitung, diratakan, atau disimpulkan.

Jika Anda menambahkan kategori ke PivotTable tetapi data kategori tidak terkait dengan data Faktanya, Anda mungkin mendapatkan kesalahan atau hasil yang tidak lazim. 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 bisa menyeret bidang ke pemotong, untuk memilih grup data tertentu untuk ditampilkan. Pemotong memungkinkan Anda secara interaktif mengelompokkan, mengurutkan, dan memfilter hasil di PivotTable.

Bekerja dengan pengelompokan dalam rumus

Anda juga bisa 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 Anda ingin membuat rumus yang mengelompokkan nilai menurut kategori, Anda terlebih dahulu akan menggunakan hubungan untuk menyambungkan tabel yang berisi data detail dan tabel yang berisi kategori tersebut, lalu Build 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, tetapi juga dalam rumus yang Anda gunakan untuk menghitung agregasi. Filter dapat digunakan dalam rumus baik dalam kolom terhitung maupun di s.

Misalnya, dalam fungsi agregasi DAX yang baru, sebagai ganti menentukan nilai untuk jumlah atau hitungan, Anda bisa menentukan seluruh tabel sebagai argumen. 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 bisa 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 bisa membuat agregasi yang berubah tergantung pada nilai yang disediakan dalam rumus, atau yang berubah tergantung 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 di Power Pivot. Versi DAX dari fungsi ini berperilaku sama seperti versi Excel, dengan beberapa perbedaan kecil dalam sintaks dan penanganan tipe data tertentu.

Fungsi agregasi standar

Fungsi

Penggunaan

rata-rata

Mengembalikan nilai rata-rata (rata-rata aritmetika) dari semua angka dalam kolom.

Averagea

Mengembalikan rata-rata (rata-rata aritmetika) dari semua nilai dalam kolom. Menangani teks dan nilai non-numerik.

Menghitung

Menghitung jumlah nilai numerik dalam kolom.

COUNTA

Menghitung jumlah nilai dalam kolom yang tidak kosong.

maks

Mengembalikan nilai numerik terbesar dalam kolom.

MAXX

Mengembalikan nilai terbesar dari sekumpulan ekspresi yang dievaluasi di atas tabel.

min

Mengembalikan nilai numerik terkecil dalam kolom.

Minx

Mengembalikan nilai terkecil dari sekumpulan ekspresi yang dievaluasi di atas tabel.

SUM

Menambahkan semua angka dalam kolom.

Fungsi agregasi DAX

DAX menyertakan fungsi agregasi yang memungkinkan Anda menentukan tabel yang akan dijalankan agregasi. Oleh karena itu, sebagai ganti hanya menambahkan atau rata-rata nilai dalam kolom, fungsi ini memungkinkan Anda membuat ekspresi yang secara dinamis mendefinisikan data untuk digabungkan.

Tabel berikut ini mencantumkan fungsi agregasi yang tersedia di DAX.

Fungsi

Penggunaan

Averagex

Rata-rata sekumpulan ekspresi yang dievaluasi di atas tabel.

Countax

Menghitung sekumpulan ekspresi yang dievaluasi di atas tabel.

Countblank

Menghitung jumlah nilai kosong dalam kolom.

Countx

Menghitung jumlah total baris dalam tabel.

https://docs.microsoft.com/dax/countrows-function-dax

Menghitung jumlah baris yang dikembalikan dari fungsi tabel bertumpuk, seperti fungsi filter.

Sumx

Mengembalikan jumlah sekumpulan ekspresi yang dievaluasi di atas tabel.

Perbedaan antara fungsi agregasi DAX dan Excel

Meskipun fungsi ini memiliki nama yang sama dengan rekan Excel mereka, mereka menggunakan mesin analitik dalam memori Power Pivotdan telah ditulis ulang untuk bekerja dengan tabel dan kolom. Anda tidak bisa menggunakan rumus DAX di buku kerja Excel, dan sebaliknya. Mereka hanya dapat digunakan di jendela Power Pivot dan di PivotTable yang didasarkan pada data Power Pivot . Selain itu, meskipun fungsinya memiliki nama identik, perilaku mungkin sedikit berbeda. Untuk informasi selengkapnya, lihat topik referensi masing-masing fungsi.

Cara agar kolom dievaluasi dalam agregasi juga berbeda dengan cara Excel menangani agregasi. Contoh mungkin membantu mengilustrasikan.

Misalkan Anda ingin mendapatkan jumlah nilai dalam kolom jumlah dalam tabel penjualan, maka Anda membuat rumus berikut ini:

=SUM('Sales'[Amount])

Dalam kasus paling sederhana, fungsi mendapatkan nilai dari kolom tunggal tanpa filter, dan hasilnya sama seperti di Excel, yang selalu menambahkan nilai dalam kolom, jumlah. Namun, di Power Pivot, rumus ditafsirkan sebagai "Dapatkan nilai dalam jumlah untuk setiap baris tabel penjualan, lalu tambahkan nilai individual tersebut. Power Pivot mengevaluasi setiap baris di mana agregasi dilakukan dan menghitung nilai skalar tunggal untuk setiap baris, lalu menjalankan agregasi pada nilai tersebut. Oleh karena itu, hasil dari 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 bawaan. Fungsi ini menggunakan rentang tanggal untuk mendapatkan nilai terkait dan menggabungkan nilainya. Anda juga dapat membandingkan nilai di sepanjang rentang tanggal.

Tabel berikut ini mencantumkan fungsi kecerdasan waktu yang bisa digunakan untuk agregasi.

Fungsi

Penggunaan

Closingbalancemonth

Closingbalancequarter

Closingbalanceyear

Menghitung nilai di akhir kalender periode tertentu.

Openingbalancemonth

Openingbalancequarter

Openingbalanceyear

Menghitung nilai di akhir kalender periode sebelum periode tertentu.

Totalmtd

Totalytd

Totalqtd

Menghitung nilai pada interval yang dimulai pada hari pertama periode dan berakhir pada tanggal terbaru dalam kolom tanggal yang ditentukan.

Fungsi lainnya dalam bagian fungsi kecerdasan waktu (fungsi kecerdasan waktu) adalah fungsi yang bisa digunakan untuk mengambil tanggal atau rentang tanggal kustom untuk digunakan dalam agregasi. Misalnya, Anda bisa menggunakan fungsi DATESINPERIOD untuk mengembalikan rentang tanggal, dan gunakan rangkaian tanggal sebagai argumen ke fungsi lain untuk menghitung agregasi kustom hanya untuk tanggal tersebut.

Catatan:  Halaman ini diterjemahkan menggunakan mesin dan mungkin terdapat kesalahan tata bahasa atau masalah keakuratan. Kami bertujuan menyediakan konten yang bermanfaat untuk Anda. Dapatkah Anda memberi tahu kami apakah informasi ini bermanfaat untuk Anda? Berikut adalah artikel dalam bahasa Inggris untuk referensi.

Perlu bantuan lainnya?

Kembangkan keterampilan Office Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×