Skenario DAX di PowerPivot

Membuat penghitungan kustom untuk PivotTable

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

• CALCULATE

• CALCULATETABLE

Menerapkan filter ke rumus

Di sebagian besar tempat di mana fungsi DAX mengambil tabel sebagai argumen, Anda biasanya bisa pas dalam tabel yang difilter, baik dengan menggunakan fungsi FILTER sebagai ganti nama tabel, atau dengan menentukan ekspresi filter sebagai salah satu argumen fungsi. Topik berikut ini menyediakan contoh tentang 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 dengan menggunakan ekspresi, sementara fungsi lain didesain khusus untuk memfilter nilai kosong.

• FILTER

• ALLNOBLANKROW

• FIRSTNONBLANK

• LASTNONBLANK

Menghapus filter secara selektif untuk membuat rasio dinamis

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

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

• Berapa banyak dana di divisi ini disumbangkan ke laba total untuk semua tahun operasi, dibandingkan dengan divisi lainnya?

Rumus yang digunakan dalam PivotTable dapat dipengaruhi oleh konteks PivotTable, tetapi Anda dapat mengubah konteks secara selektif dengan menambahkan atau menghapus filter. Contoh dalam topik SEMUA memperlihatkan kepada Anda cara melakukannya. 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 SEMUA.

Topik ALLEXCEPT menyediakan contoh cara menghapus filter secara selektif pada rumus. Kedua contoh ini akan memandu Anda melihat bagaimana hasilnya berubah tergantung pada desain PivotTable.

• ALL

• ALLEXCEPT

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

• ISBLANK

• ISERROR

Menggunakan nilai dari pengulangan luar

Selain menggunakan nilai dari konteks saat ini dalam perhitungan, DAX bisa menggunakan nilai dari pengulangan sebelumnya dalam membuat serangkaian perhitungan terkait. Topik berikut ini menyediakan panduan tentang cara menyusun rumus yang merefer dari nilai dari pengulangan luar. Fungsi EARLIER mendukung hingga dua tingkat pengulangan yang ditumpuk.

• Fungsi EARLIER

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

Membuat kolom kunci dengan penggabungan

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

• Fungsi CONCATENATE

Membuat tanggal berdasarkan bagian tanggal yang diekstrak dari tanggal teks

Power Pivot menggunakan tipe data SQL Server/waktu 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 kawasan yang tidak dikenali oleh mesin data Power Pivot , atau jika data Anda menggunakan tombol pengganti bilangan bulat -- Anda mungkin perlu menggunakan rumus DAX untuk mengekstrak bagian tanggal lalu menyusun bagiannya menjadi representasi tanggal/waktu yang valid.

Misalnya, jika Anda mempunyai kolom tanggal yang telah dinyatakan sebagai bilangan bulat lalu diimpor sebagai string teks, Anda bisa mengonversi string tersebut 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 ketika mengonversi nilai menjadi string, atau dari string. Topik berikut ini juga menyediakan daftar detail format yang sudah ditentukan sebelumnya yang tersedia untuk bekerja dengan tanggal dan angka.

• Format Numerik Yang Telah Ditetapkan untuk Fungsi FORMAT

• Format Numerik Kustom untuk Fungsi FORMAT

• Format Tanggal dan Waktu yang telah ditetapkan sebelumnya untuk Fungsi FORMAT

• Format Tanggal dan Waktu Kustom untuk Fungsi FORMAT

Mengubah tipe data menggunakan rumus

Di Power Pivotsumber, 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 dijalankan 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 minus 3 hari, lalu output nilai bilangan bulat yang terkait.

  =(TODAY()-3)*1,0

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

  =""& TODAY()

Fungsi berikut ini juga bisa 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 bersyarat. Topik berikut berisi beberapa contoh sederhana pemrosesan bersyarat dan nilai bersyarat:

• IF

• Fungsi OR

• Fungsi CONCATENATE

Menguji kesalahan dalam rumus

Tidak seperti Excel, Anda tidak bisa memiliki nilai yang valid dalam satu baris dari kolom terhitung dan nilai yang tidak valid di baris lain. Artinya, jika ada kesalahan di setiap 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 menjumpai nilai kosong ketika mengharapkan nilai numerik. Saat Anda mengembangkan model data, yang terbaik adalah memperbolehkan kesalahan muncul sehingga Anda bisa mengklik pada pesan dan memecahkan masalah. Namun, saat menerbitkan buku kerja, Anda harus memadukan penanganan kesalahan untuk mencegah nilai yang tidak diharapkan menyebabkan kegagalan perhitungan.

Untuk menghindari mengembalikan kesalahan dalam kolom terhitung, Anda menggunakan kombinasi fungsi logika dan informasi untuk menguji kesalahan dan selalu mengembalikan nilai yang valid. Topik berikut ini memberikan beberapa contoh sederhana cara melakukannya dalam DAX:

• Fungsi ISBLANK

• Fungsi IFERROR

Menghitung penjualan kumulatif

Topik berikut berisi contoh cara menghitung saldo tutup dan buka. Contohnya memungkinkan Anda membuat saldo sementara di berbagai interval berbeda seperti hari, bulan, kuartal, atau tahun.

• Fungsi CLOSINGBALANCEMONTH, Fungsi CLOSINGBALANCEQUARTER, Fungsi CLOSINGBALANCEYEAR

• Fungsi OPENINGBALANCEMONTH, Fungsi OPENINGBALANCEQUARTER, Fungsi OPENINGBALANCEYEAR

Membandingkan nilai seiring waktu

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

• Fungsi PREVIOUSMONTH, FUNGSI PREVIOUSQUARTER, PREVIOUSYEAR

• Fungsi TOTALMTD, Fungsi TOTALQTD,Fungsi TOTALYTD

• Fungsi PARALLELPERIOD

Menghitung nilai di atas rentang tanggal kustom

Lihat topik berikut ini sebagai 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 serangkaian tanggal kustom, Anda bisa menggunakan kumpulan tanggal itu sebagai input ke fungsi yang menjalankan perhitungan, untuk membuat agregat kustom di seluruh periode waktu. Lihat topik berikut ini sebagai contoh cara melakukan ini:

• Fungsi PARALLELPERIOD

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

Memperlihatkan sepuluh item teratas saja dalam PivotTable

Urutan item secara dinamis dengan 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 mendasarinya telah berubah. Selain itu, karena rumus digunakan dalam kolom terhitung, Anda dapat menggunakan peringkat di Pemotong, lalu memilih 5 teratas, 10 teratas, atau bahkan 100 nilai teratas.

• Fungsi EARLIER.