Excel DAX - Melakukan Perhitungan Kompleks

Rumus DAX bisa melakukan penghitungan kompleks yang melibatkan agregasi kustom, pemfilteran, dan penggunaan nilai bersyarat. Anda dapat melakukan hal berikut dengan DAX -

Buat penghitungan kustom untuk PivotTable.
Menerapkan filter ke rumus DAX.
Hapus filter secara selektif untuk membuat rasio dinamis.
Gunakan nilai dari loop luar.

Membuat Perhitungan Kustom untuk PivotTable

Fungsi DAX CALCULATE and CALCULATETABLE sangat kuat dan fleksibel. Mereka berguna untuk menentukan kolom kalkulasi. Fungsi DAX ini memungkinkan Anda untuk mengubah konteks penghitungan yang akan dilakukan. Anda juga dapat menyesuaikan jenis operasi agregasi atau matematika yang akan dilakukan.

CALCULATE Fungsi

HITUNG (<ekspresi>, [<filter1>], [<filter2>]…)

Fungsi CALCULATE mengevaluasi ekspresi yang diberikan dalam konteks yang dimodifikasi oleh nol atau lebih filter yang ditentukan.

Jika data Anda telah difilter, fungsi HITUNG mengubah konteks di mana data difilter dan mengevaluasi ekspresi dalam konteks baru yang Anda tentukan oleh filter. Artinya, filter apa pun yang ada di kolom yang ditentukan akan dihapus dan filter yang digunakan dalam argumen filter akan diterapkan.

Contoh

Misalkan Anda ingin menampilkan persentase medali berdasarkan olahraga yang difilter menurut nama Negara. Perhitungan Anda harus mendapatkan nilai persentase yang mengesampingkan filter yang Anda terapkan pada Negara di PivotTable.

Tentukan bidang kalkulasi - Persentase Jumlah Medali seperti yang ditunjukkan pada tangkapan layar berikut.

Dengan rumus DAX ini, semua baris dalam tabel Hasil diperhitungkan dalam fungsi HITUNG dengan filter yang berisi fungsi SEMUA. Dengan cara ini, Anda mendapatkan jumlah total penyebutnya.

PivotTable Anda akan seperti yang ditunjukkan pada gambar layar berikut.

Pada tangkapan layar di atas, Negara difilter ke AS dan 18 nilai teratas ditampilkan di PivotTable. Selanjutnya, Anda bisa secara dinamis memfilter nilai di PivotTable. Namun, penghitungan akan benar dengan rumus DAX kustom yang Anda gunakan.

Fungsi CALCULATETABLE mengambil tabel nilai dan melakukan tindakan yang sama seperti fungsi CALCULATE.

Memfilter Data dalam Rumus

Anda bisa membuat filter dalam rumus DAX, untuk memilih nilai dari sumber data untuk digunakan dalam penghitungan. Anda bisa melakukan ini dengan menentukan ekspresi filter dan menggunakannya bersama dengan tabel yang merupakan input ke rumus DAX.

Ekspresi filter memungkinkan Anda mendapatkan subset dari data sumber. Filter diterapkan secara dinamis setiap kali Anda memperbarui hasil rumus DAX, bergantung pada konteks data Anda saat ini dan Anda bisa yakin akan hasil yang akurat dan diharapkan.

Ekspresi filter biasanya berisi fungsi filter DAX yang hanya mengembalikan baris yang dipilih dari tabel, yang kemudian bisa digunakan sebagai argumen untuk fungsi DAX lain yang Anda gunakan untuk agregasi data.

Contoh

Tangkapan layar berikut menunjukkan definisi bidang kalkulasi yang memberikan perolehan medali hanya untuk olahraga musim panas.

Dengan bidang terhitung ini, PivotTable terlihat seperti yang ditunjukkan pada gambar layar berikut.

Seperti yang bisa Anda amati, nilai di PivotTable di sisi kanan dengan bidang terhitung baru cocok dengan yang ada di PivotTable di sisi kiri dengan filter pada bidang musim diterapkan secara eksplisit.

Note - Filter DAX dan fungsi nilai mengembalikan tabel, tetapi tidak pernah mengembalikan tabel atau baris secara langsung ke Model Data dan karenanya selalu disematkan dalam fungsi DAX lainnya.

Untuk detail tentang fungsi DAX ini, lihat bab - Fungsi Filter DAX.

Menambah dan Menghapus Filter Secara Dinamis

Rumus DAX yang Anda gunakan dalam PivotTable bisa dipengaruhi oleh konteks PivotTable. Namun, Anda dapat mengubah konteks secara selektif dengan menambahkan atau menghapus filter. Anda dapat menggunakan fungsi DAX ALL dan ALLEXCEPT untuk secara dinamis memilih baris terlepas dari konteks PivotTable.

Selain itu, Anda dapat menggunakan fungsi DAX DISTINCT dan VALUES untuk mengembalikan nilai yang berbeda.

Menggunakan Nilai dari Loop Luar

Anda dapat menggunakan nilai dari pengulangan sebelumnya dalam membuat sekumpulan penghitungan terkait dengan fungsi DAX SEBELUMNYA. Fungsi DAX ini mendukung hingga dua tingkat loop bersarang.