Skenario DAX di PowerPivot

Membuat penghitungan kustom untuk PivotTable

CALCULATE dan CALCULATETABLE adalah fungsi fleksibel canggih yang berguna untuk menentukan bidang terhitung. Fungsi ini memungkinkan Anda mengubah konteks penghitungan yang akan dilakukan. Anda juga bisa mengkustomisasi tipe agregasi atau operasi matematika untuk dijalankan. Lihat topik berikut ini sebagai contohnya.

• HITUNG

• CALCULATETABLE

Menerapkan filter ke rumus

Di sebagian besar tempat di mana fungsi DAX mengambil tabel sebagai argumen, Anda biasanya bisa meneruskan dalam tabel yang difilter, baik dengan menggunakan fungsi FILTER, bukan nama tabel, atau dengan menentukan ekspresi filter sebagai salah satu argumen fungsi. Topik berikut ini menyediakan contoh cara membuat filter dan bagaimana filter mempengaruhi hasil rumus. Untuk informasi selengkapnya, lihat Memfilter Data dalam Rumus DAX.

Fungsi FILTER memungkinkan Anda menentukan kriteria filter menggunakan ekspresi, sementara fungsi lain dirancang khusus untuk memfilter nilai kosong.

• SARINGAN

• ALLNOBLANKROW

• FIRSTNONBLANK

• LASTNONBLANK

Menghapus filter secara selektif untuk membuat rasio dinamis

Dengan membuat filter dinamis dalam rumus, Anda bisa dengan mudah menjawab pertanyaan seperti berikut ini:

• Apa kontribusi penjualan produk saat ini terhadap total penjualan untuk tahun ini?

• Berapa banyak yang telah divisi ini berkontribusi pada total laba untuk semua tahun operasi, dibandingkan dengan divisi lain?

Rumus yang Anda gunakan dalam PivotTable bisa dipengaruhi oleh konteks PivotTable, tapi Anda bisa mengubah konteks secara selektif dengan menambahkan atau menghapus filter. Contoh dalam topik ALL memperlihatkan cara melakukan hal ini. Untuk menemukan rasio penjualan untuk penjual tertentu atas penjualan untuk semua penjual, Buat pengukuran yang menghitung nilai untuk konteks saat ini yang dibagi dengan nilai untuk konteks ALL.

Topik ALLEXCEPT menyediakan contoh cara menghapus filter secara selektif pada rumus. Kedua contoh tersebut memandu Anda melihat bagaimana hasil berubah bergantung pada desain PivotTable.

• SEMUA

• ALLEXCEPT

Untuk contoh lain tentang cara menghitung rasio dan persentase, lihat topik berikut:

• ISBLANK

• ISERROR

Menggunakan nilai dari pengulangan luar

Selain menggunakan nilai dari konteks saat ini dalam penghitungan, DAX dapat menggunakan nilai dari pengulangan sebelumnya dalam membuat sekumpulan perhitungan terkait. Topik berikut ini menyediakan panduan tentang cara menyusun rumus yang mereferensikan nilai dari pengulangan luar. Fungsi EARLIER mendukung hingga dua tingkat pengulangan bertumpuk.

• Fungsi EARLIER

Untuk mempelajari selengkapnya tentang konteks baris dan tabel terkait, serta cara menggunakan konsep ini dalam rumus, lihat Konteks dalam Rumus DAX.

Membuat kolom kunci menurut penggambungan

Power Pivot tidak mengizinkan kunci komposit; oleh karena itu, jika Anda memiliki kunci komposit di sumber data Anda, Anda mungkin perlu menggabungkannya menjadi satu kolom kunci. Topik berikut ini menyediakan satu contoh cara membuat kolom terhitung berdasarkan kunci komposit.

• Fungsi CONCATENATE

Membuat tanggal berdasarkan bagian tanggal yang diekstrak dari tanggal teks

Power Pivot menggunakan tipe data tanggal/waktu SQL Server untuk bekerja dengan tanggal; oleh karena itu, jika data eksternal Anda berisi tanggal yang diformat secara berbeda - misalnya, jika tanggal Anda ditulis dalam format tanggal regional yang tidak dikenali oleh mesin data Power Pivot , atau jika data Anda menggunakan kunci pengganti bilangan bulat -- Anda mungkin perlu menggunakan rumus DAX untuk mengekstrak bagian tanggal lalu menulis bagian-bagian ke dalam representasi tanggal/waktu yang valid.

Misalnya, jika Anda memiliki kolom tanggal yang telah dinyatakan sebagai bilangan bulat lalu diimpor sebagai string teks, Anda bisa mengonversi string menjadi nilai tanggal/waktu dengan menggunakan rumus berikut:

=DATE(RIGHT([Value1],4),LEFT([Value1],2),MID([Value1],2))

Topik berikut ini menyediakan informasi selengkapnya tentang fungsi yang digunakan untuk mengekstrak dan menulis tanggal.

• Fungsi DATEVALUE

• Fungsi TIMEVALUE

• Fungsi DATE

Menentukan format tanggal atau angka kustom

Jika data Anda berisi tanggal atau angka yang tidak dinyatakan dalam salah satu format teks Windows standar, Anda bisa menentukan format kustom untuk memastikan bahwa nilai ditangani dengan benar. Format ini digunakan saat mengonversi nilai menjadi string, atau dari string. Topik berikut ini juga menyediakan daftar mendetail format yang sudah ditentukan sebelumnya yang tersedia untuk bekerja dengan tanggal dan angka.

• Format Numerik yang Ditentukan Sebelumnya untuk Fungsi FORMAT

• Format Numerik Kustom untuk Fungsi FORMAT

• Format Tanggal dan Waktu yang ditentukan sebelumnya untuk Fungsi FORMAT

• Format Tanggal dan Waktu Kustom untuk Fungsi FORMAT

Mengubah tipe data menggunakan rumus

Di Power Pivot, tipe data output ditentukan oleh kolom sumber, dan Anda tidak bisa menentukan tipe data hasil secara eksplisit, karena tipe data optimal ditentukan oleh Power Pivot. Namun, Anda dapat menggunakan konversi tipe data implisit yang dilakukan oleh Power Pivot untuk memanipulasi tipe data output. 

• Untuk mengonversi tanggal atau string angka menjadi angka, kalikan dengan 1,0. Misalnya, rumus berikut menghitung tanggal saat ini dikurangi 3 hari, lalu menghasilkan nilai bilangan bulat terkait.

  =(TODAY()-3)*1.0

• Untuk mengonversi nilai tanggal, angka, atau mata uang menjadi string, menggabungkan nilai dengan string kosong. Misalnya, rumus berikut mengembalikan tanggal hari ini sebagai string.

  =""& TODAY()

Fungsi berikut ini juga dapat digunakan untuk memastikan bahwa tipe data tertentu dikembalikan:

Mengonversi bilangan riil menjadi bilangan bulat

• Fungsi ROUND

• Fungsi CEILING

• Fungsi FLOOR

  Mengonversi bilangan riil, bilangan bulat, atau tanggal menjadi string

• Fungsi FIXED

• Fungsi FORMAT

  Mengonversi string menjadi bilangan riil atau tanggal

• Fungsi VALUE

• Fungsi DATEVALUE

• Fungsi TIMEVALUE

Membuat nilai berdasarkan kondisi

Anda dapat menggunakan kondisi IF bertumpuk untuk menguji nilai dan menghasilkan nilai baru secara bersyukur. Topik berikut ini berisi beberapa contoh sederhana pemrosesan bersyarah dan nilai bersyarah:

• KALAU

• Fungsi OR

• Fungsi CONCATENATE

Menguji kesalahan dalam rumus

Tidak seperti Excel, Anda tidak bisa memiliki nilai yang valid dalam satu baris kolom terhitung dan nilai yang tidak valid di baris lain. Artinya, jika ada kesalahan di bagian kolom Power Pivot , seluruh kolom dibenderai dengan kesalahan, sehingga Anda harus selalu mengoreksi kesalahan rumus yang menghasilkan nilai yang tidak valid.

Misalnya, jika Anda membuat rumus yang membagi dengan nol, Anda mungkin mendapatkan hasil tak terbatas, atau kesalahan. Beberapa rumus juga akan gagal jika fungsi menemukan nilai kosong ketika mengharapkan nilai numerik. Saat Anda mengembangkan model data, yang terbaik adalah memperbolehkan kesalahan muncul sehingga Anda bisa mengklik pesan dan memecahkan masalah. Namun, saat Anda menerbitkan buku kerja, Anda harus menggabungkan penanganan kesalahan untuk mencegah nilai yang tidak diharapkan menyebabkan penghitungan gagal.

Untuk menghindari kesalahan yang dikembalikan dalam kolom terhitung, Gunakan kombinasi fungsi logika dan informasi untuk menguji kesalahan dan selalu mengembalikan nilai yang valid. Topik berikut ini menyediakan beberapa contoh sederhana tentang cara melakukan hal ini di DAX:

• Fungsi ISBLANK

• Fungsi IFERROR

Menghitung penjualan kumulatif

Topik berikut berisi contoh cara menghitung saldo penutup dan pembukaan. Contohnya memungkinkan Anda membuat saldo berjalan di berbagai interval seperti hari, bulan, kuartal, atau tahun.

• Fungsi CLOSINGBALANCEMONTH, Fungsi CLOSINGBALANCEQUARTER, Fungsi CLOSINGBALANCEYEAR

• Fungsi OPENINGBALANCEMONTH, Fungsi OPENINGBALANCEQUARTER, Fungsi OPENINGBALANCEYEAR

Membandingkan nilai seiring waktu

Topik berikut ini berisi contoh cara membandingkan jumlah pada periode waktu yang berbeda. Periode waktu default yang didukung oleh DAX adalah bulan, kuartal, dan tahun.

• Fungsi PREVIOUSMONTH, PREVIOUSQUARTER, Fungsi PREVIOUSYEAR

• Fungsi TOTALMTD, Fungsi TOTALQTD, Fungsi TOTALYTD

• Fungsi PARALLELPERIOD

Menghitung nilai di atas rentang tanggal kustom

Lihat topik berikut ini untuk contoh cara mengambil rentang tanggal kustom, seperti 15 hari pertama setelah dimulainya promosi penjualan.

• Fungsi DATESINPERIOD

• Fungsi DATESBETWEEN

• Fungsi DATEADD

• Fungsi FIRSTDATE

• Fungsi LASTDATE

Jika Anda menggunakan fungsi kecerdasan waktu untuk mengambil sekumpulan tanggal kustom, Anda bisa menggunakan kumpulan tanggal tersebut sebagai input ke fungsi yang melakukan penghitungan, untuk membuat agregat kustom sepanjang periode waktu. Lihat topik berikut ini untuk contoh cara melakukan hal ini:

• Fungsi PARALLELPERIOD

  Catatan: Jika Anda tidak perlu menentukan rentang tanggal kustom, tetapi bekerja dengan unit akuntansi standar seperti bulan, kuartal atau tahun, kami menyarankan agar Anda melakukan penghitungan dengan menggunakan fungsi kecerdasan waktu yang dirancang untuk tujuan ini, seperti TOTALQTD, TOTALMTD, TOTALQTD, dll.

Memperlihatkan hanya sepuluh item teratas dalam PivotTable

Mengurutkan item secara dinamis menggunakan rumus

Topik berikut ini berisi contoh cara menggunakan DAX untuk membuat peringkat yang disimpan dalam kolom terhitung. Karena rumus DAX dihitung secara dinamis, Anda selalu dapat memastikan bahwa peringkat sudah benar meskipun data yang mendasar telah berubah. Selain itu, karena rumus digunakan dalam kolom terhitung, Anda dapat menggunakan peringkat dalam Pemotong lalu memilih 5 teratas, 10 teratas, atau bahkan 100 nilai teratas.

• Fungsi EARLIER.