Tip #1: Salin Model Data Anda dengan Mudah dari Satu File ke File Lainnya

Katakanlah kita sedang bekerja dengan file Excel yang memiliki konfigurasi berikut:

Kami telah menghadirkan serangkaian tabel data menggunakan Power Query .
Kami telah membuat Model Data dengan hubungan antar tabel.
Kami telah membuat serangkaian Tindakan DAX dan KPI untuk membantu penghitungan.

Kami ingin menyalin semua aspek file Excel kami dan menempelkannya ke file Excel baru sehingga kami dapat membawa analisis kami ke arah yang berbeda.

Anda dapat mencoba menyalin dan menempelkan masing-masing elemen ini secara terpisah, namun Anda masih dihadapkan pada rekonstruksi banyak bagian proses secara manual.

Trik untuk mentransfer semuanya secara keseluruhan adalah dengan membuat Tabel Pivot “dummy” kecil.

Jika kita membuat Tabel Pivot dengan setidaknya satu bidang dari setiap tabel di Model Data, kita akan menghubungkan setiap elemen proses pemodelan secara analitis.

Langkah-langkahnya adalah sebagai berikut:

Mulai lembar kerja baru dan masukkan Tabel Pivot baru menggunakan data dari Model Data sebagai sumbernya.
Masukkan setidaknya 1 bidang dari setiap tabel Model Data ke dalam Tabel Pivot.

Kami telah membuat Tabel Pivot “dummy” kami.
Sorot seluruh Tabel Pivot dan klik Salin (CTRL-C).
Mulai buku kerja baru dan klik Tempel (CTRL-V).

Tip #2: Tabel Pengukuran Khusus

Saat bekerja dengan Pengukuran DAX di Power Pivot, Pengukuran dapat ditempatkan di tabel mana pun di Model Data karena merupakan penghitungan independen.

Preferensi penempatan bervariasi dari pengguna ke pengguna.

Beberapa pengguna suka menempatkan Pengukuran dalam tabel yang paling mereka fokuskan secara analitis, sementara pengguna lainnya menempatkan semua pengukuran dalam satu tabel.

Tabel default untuk menempatkan semua pengukuran baru adalah tabel yang memasukkan Model Data terlebih dahulu.

Tabel tujuan Measures ini dapat diubah selama proses pembuatan Measure dengan mengklik dropdown opsi “ Table Name ” dan memilih tabel yang diinginkan.

Jika Anda menempatkan semua Pengukuran dalam satu tabel dan tabel tersebut bukan tabel pertama yang ditempatkan dalam Model Data, memilih ulang tujuan Pengukuran untuk setiap Pengukuran baru dapat menjadi hal yang membosankan.

Tentu saja, jika Anda tahu di tabel mana Anda ingin menyimpan semua Ukuran Anda, masukkan saja tabel itu ke dalam Model terlebih dahulu dan semua ini tidak menjadi masalah.

Membuat Tabel Ukuran Khusus

Namun, beberapa pengguna lebih memilih untuk menyimpan SEMUA pengukuran dalam “Tabel Pengukuran” khusus yang tidak memiliki data lain.

Inilah cara kita membuat tabel seperti itu.

CATATAN: Pembuatan tabel ini harus dilakukan SEBELUM tabel lainnya dimasukkan ke dalam Model Data.

Mulai buku kerja baru dan salin sel A1 . ( selnya kosong )
Buka Model Data dengan memilih Data (tab) -> Data Tools (grup) -> Data Model .
Di jendela Power Pivot, klik Tempel .
Di jendela Tempel Pratinjau, beri nama tabel baru seperti “ KPI ” dan klik OK .

Untuk membuat ikon pengukuran yang menarik dalam Daftar Bidang Tabel Pivot, klik kanan pada judul kolom kosong yang baru dan pilih “ Sembunyikan dari Alat Klien ”.

Tutup jendela Power Pivot dan impor data yang tersisa ke Model Data melalui cara apa pun yang Anda inginkan ( Power Query, impor langsung, dll. )

Sekarang, saat kita membuat Measures, tabel arahan default untuk Measures baru adalah tabel kosong yang kita buat di Langkah 4 .

Saat Anda membuat Tabel Pivot dari Model Data, Anda akan melihat tabel Ukuran dengan simbol SUM yang menarik di samping nama tabel.

Tip #3: Gunakan Fungsi Kubus untuk Pelaporan Fleksibel

Saat bekerja dengan Tabel Pivot, Anda mungkin ingin mereferensikan nilai tertentu di Tabel Pivot secara terpisah, mungkin sebagai bagian dari laporan dasbor.

Cara sederhana untuk mencapai hal ini adalah dengan menggunakan fungsi GETPIVOTDATA . Ini hanya perlu mengetikkan tanda sama dengan lalu mengklik sel Tabel Pivot yang berisi nilai yang diinginkan.

Hasilnya adalah sebagai berikut:

Hal ini sangat bermanfaat karena jika tabel meluas, menyusut, atau mengubah struktur, selama bidang [Kuantitas] tetap berada di Tabel Pivot, agregasi akan dikembalikan.

Meski sederhana dan mudah, hal ini memiliki kelemahan yang sangat besar.

Jika bidang agregasi Tabel Pivot berubah ( misal: dari [Quantity] menjadi [Median Quantity] ), fungsi GETPIVOTDATA gagal.

Bagaimana kita bisa menunjukkan nilai agregasi yang diinginkan sebagai hasil independen yang tidak bergantung pada Tabel Pivot?

Di sinilah fungsi CUBE dapat bermanfaat.

Jangan berhenti membaca! Jika Anda belum pernah menggunakan fungsi CUBE sebelumnya, atau fungsi tersebut tampak menakutkan, Anda dapat memanfaatkannya dengan pengetahuan yang sangat terbatas tentang cara kerja fungsi tersebut. Cukup ikuti langkah-langkah sederhana ini:

Buat Tabel Pivot yang berisi nilai yang Anda inginkan di dasbor Anda. Sebagai contoh, kita hanya menginginkan jumlah total.

Dengan memilih Tabel Pivot baru, ubah Tabel Pivot menjadi sekumpulan fungsi mandiri dengan memilih Analisis Tabel Pivot (tab) -> Perhitungan (grup) -> Alat OLAP -> Konversi ke Rumus .

Dua sel yang menyusun Tabel Pivot telah diganti dengan fungsi CUBE.

Dalam contoh kita di atas, karena fungsi CUBE di sel D5 bergantung pada hasil fungsi CUBE di sel D4 , kita tidak bisa begitu saja menyalin rumus D5 dan membuang rumus D4 .

Namun kita dapat menggabungkan kedua fungsi tersebut menjadi satu fungsi dengan menempatkan seluruh fungsi CUBEMEMBER di tempat referensi sel D4 dalam fungsi CUBEVALUE .

Jika Anda familiar dengan fungsi CUBE, Anda tahu bahwa kami dapat mempersingkat fungsi tersebut dengan menghapus bagian “ CUBEMEMBER(“ThisWorkbookDataModel”, ” dari rumus tanpa masalah apa pun.

Hasil dari fungsi CUBE akan disegarkan setiap kali Model Data disegarkan.

Menyesuaikan Format Hasil CUBE

Untuk membuat keluaran fungsi CUBE lebih rapi, kita dapat menggabungkan label di depan fungsi CUBEVALUE .

(klik rumus di atas untuk gambar lebih besar)

Kita juga dapat menggunakan fungsi TEXT untuk menerapkan format formula pada hasil fungsi CUBEVALUE .

(klik rumus di atas untuk gambar lebih besar)

sumber : xelplus.com