Wa'alaikumussalam wr wb
mas Zainul,
Dah balik 2 tahun lalu kok...
Itu bikinnya pakai Power Pivot (sebut saja Excel Data Model). Jadi,
pembuatan seluruh kolom dan measure di 3 file tersebut adalah di Data Model
nya.
Di Excel 2010 ke atas, (pastikan COM Addin PowerPivot telah diaktifkan
melalui Excel Options -> Addins -> pilih COM Addin)
ribbon Power Pivot -> grup menu Data Model -> klik Manage
atau
ribbon Data -> grup menu Data Tools -> klik Manage Data Model
Nanti akan muncul data model. Lalu lihat tabel (sheet atau tab) bla_det
(lali aku jenenge opo yo kae)
Untuk file yang pakai calculated column, maka calculated column akan tampak
dengan warna latar header kolom yang berbeda.
Untuk file yang pakai calculated measure, maka calculated measure akan
tampak di area cells yang ada di bawah area data view (di bawah area
records preview)
fyi,
Calculated Column : akan menambah size worbook dan membutuhkan waktu saat
akan membuka data model (bukan membuka workbook ya), tapi kalkulasi tidak
diproses berulang kecuali saat refresh
Calculated Measure : tidak menambah size workbook secara signifikan, tetapi
kalkulasi dilakukan berulang setiap ada pengubahan terhadap pivot table-nya
nanti. Sangat cepat jika pivot table tidak menampilkan banyak nilai data.
Wassalamu'alaikum wr wb
Kid
On Thu, Jan 11, 2018 at 2:26 PM, Zainul Ulum zainul_u...@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:
>
>
> Assalamu'laikum Mas Kid..
> Masih di negeri kanguru?
>
> Saya mau mencoba saran yang diuraikan tetapi saya kesulitan untuk
> menampilkan tool calculated field di excel 2013 home edition.
>
> Apakah langkah yang diuraikan oleh Mas Kid menggunakan tool calculated
> field di Power Pivot?.
>
> Wassalaamu'alaikum Wr. Wb.
> -zainul
>
> #dikirim_pakai_mail.yahoo.com_tanpa_pulsa#
>
> --------------------------------------------
> On Wed, 10/1/18, 'Mr. Kid' mr.nm...@gmail.com [belajar-excel] <
> belajar-excel@yahoogroups.com> wrote:
>
> Subject: Re: [belajar-excel] Membuat Calculated Field di Pivot Data Model
> To: "BeExcel" <belajar-excel@yahoogroups.com>
> Date: Wednesday, 10 January, 2018, 10:26 PM
>
>
>
>
>
>
>
>
>
>
>
>
>
> Wassalamu'alaikum
> wr wb
>
> mas Zainul,
>
> File terlampir.
> > Model 1 CalcCols1 : calculated column
> setiap elemen yang dibutuhkan (terutama ketika hasil satu
> kalkulasi akan digunakan berulang di kolom lain atau
> dikalkulasi lain)
> calculated
> column 1 [Unit Price] = lookup nilai unit price dari tabel
> up
> calculated column 2
> [Total Price] = [Unit Price] * [Qty]> Model 1
> CalcCols2 : calculated column untuk menjadi explicit measure
> (field bawaan dataset) yang dimasukkan ke area Values
> calculated column 2
> [Total Price] = hasil lookup nilai unit price dari tabel up
> * [Qty]
> > Model 2 Implicit
> Measure (calculated measure) : measure hasil
> kalkulasi di bawah data view, pada
> salah satu cells diisi definisi Calculated
> Measure. calculated measure
> (fx) [Total Price]:= kalkulasi sum tiap record dari
> tabel det, dengan definisi kalkulasi sebagai hasil lookup
> nilai unit price dari tabel up * [Qty]
> Pada pivot table, Model 1 akan
> menampilkan kolom [Total Price] seperti field lainnya,
> sedangkan Model 2 akan menampilkan [Total Price] dengan icon
> calculated measure ( fx Total Price )
> Wassalamu'alaikum wr
> wbKid
> P.S.Sorry, file lampiran
> lebih dari 250KB.Supaya utuh perbandingannya.
>
>
> 2018-01-10 18:43 GMT+07:00
> Zainul Ulum zainul_u...@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Dear Be-Excel,
>
>
>
> Saya mempunya pivot dengan setting data model yang
> menggabungkan (relationship) antara tabel [tbl_det] dan
> [tbl_up].
>
>
>
> Bagaimanakah caranya setting di [MDX] sehingga didapatkan
> hasil seperti di table [PIVOT] tanpa saya harus membuta
> kolom bantu [Unit Price] dan [Price] di [tbl_det]?
>
>
>
> Untuk detail silakan lihat di file terlampir.
>
>
>
> Wassalam Wr. Wb,
>
> -zainul
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> #yiv6800798298 #yiv6800798298 --
> #yiv6800798298ygrp-mkp {
> border:1px solid #d8d8d8;font-family:Arial;margin:10px
> 0;padding:0 10px;}
>
> #yiv6800798298 #yiv6800798298ygrp-mkp hr {
> border:1px solid #d8d8d8;}
>
> #yiv6800798298 #yiv6800798298ygrp-mkp #yiv6800798298hd {
> color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
> 0;}
>
> #yiv6800798298 #yiv6800798298ygrp-mkp #yiv6800798298ads {
> margin-bottom:10px;}
>
> #yiv6800798298 #yiv6800798298ygrp-mkp .yiv6800798298ad {
> padding:0 0;}
>
> #yiv6800798298 #yiv6800798298ygrp-mkp .yiv6800798298ad p {
> margin:0;}
>
> #yiv6800798298 #yiv6800798298ygrp-mkp .yiv6800798298ad a {
> color:#0000ff;text-decoration:none;}
> #yiv6800798298 #yiv6800798298ygrp-sponsor
> #yiv6800798298ygrp-lc {
> font-family:Arial;}
>
> #yiv6800798298 #yiv6800798298ygrp-sponsor
> #yiv6800798298ygrp-lc #yiv6800798298hd {
> margin:10px
> 0px;font-weight:700;font-size:78%;line-height:122%;}
>
> #yiv6800798298 #yiv6800798298ygrp-sponsor
> #yiv6800798298ygrp-lc .yiv6800798298ad {
> margin-bottom:10px;padding:0 0;}
>
> #yiv6800798298 #yiv6800798298actions {
> font-family:Verdana;font-size:11px;padding:10px 0;}
>
> #yiv6800798298 #yiv6800798298activity {
> background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;
> padding:10px;}
>
> #yiv6800798298 #yiv6800798298activity span {
> font-weight:700;}
>
> #yiv6800798298 #yiv6800798298activity span:first-child {
> text-transform:uppercase;}
>
> #yiv6800798298 #yiv6800798298activity span a {
> color:#5085b6;text-decoration:none;}
>
> #yiv6800798298 #yiv6800798298activity span span {
> color:#ff7900;}
>
> #yiv6800798298 #yiv6800798298activity span
> .yiv6800798298underline {
> text-decoration:underline;}
>
> #yiv6800798298 .yiv6800798298attach {
> clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
> 0;width:400px;}
>
> #yiv6800798298 .yiv6800798298attach div a {
> text-decoration:none;}
>
> #yiv6800798298 .yiv6800798298attach img {
> border:none;padding-right:5px;}
>
> #yiv6800798298 .yiv6800798298attach label {
> display:block;margin-bottom:5px;}
>
> #yiv6800798298 .yiv6800798298attach label a {
> text-decoration:none;}
>
> #yiv6800798298 blockquote {
> margin:0 0 0 4px;}
>
> #yiv6800798298 .yiv6800798298bold {
> font-family:Arial;font-size:13px;font-weight:700;}
>
> #yiv6800798298 .yiv6800798298bold a {
> text-decoration:none;}
>
> #yiv6800798298 dd.yiv6800798298last p a {
> font-family:Verdana;font-weight:700;}
>
> #yiv6800798298 dd.yiv6800798298last p span {
> margin-right:10px;font-family:Verdana;font-weight:700;}
>
> #yiv6800798298 dd.yiv6800798298last p
> span.yiv6800798298yshortcuts {
> margin-right:0;}
>
> #yiv6800798298 div.yiv6800798298attach-table div div a {
> text-decoration:none;}
>
> #yiv6800798298 div.yiv6800798298attach-table {
> width:400px;}
>
> #yiv6800798298 div.yiv6800798298file-title a, #yiv6800798298
> div.yiv6800798298file-title a:active, #yiv6800798298
> div.yiv6800798298file-title a:hover, #yiv6800798298
> div.yiv6800798298file-title a:visited {
> text-decoration:none;}
>
> #yiv6800798298 div.yiv6800798298photo-title a,
> #yiv6800798298 div.yiv6800798298photo-title a:active,
> #yiv6800798298 div.yiv6800798298photo-title a:hover,
> #yiv6800798298 div.yiv6800798298photo-title a:visited {
> text-decoration:none;}
>
> #yiv6800798298 div#yiv6800798298ygrp-mlmsg
> #yiv6800798298ygrp-msg p a span.yiv6800798298yshortcuts {
> font-family:Verdana;font-size:10px;font-weight:normal;}
>
> #yiv6800798298 .yiv6800798298green {
> color:#628c2a;}
>
> #yiv6800798298 .yiv6800798298MsoNormal {
> margin:0 0 0 0;}
>
> #yiv6800798298 o {
> font-size:0;}
>
> #yiv6800798298 #yiv6800798298photos div {
> float:left;width:72px;}
>
> #yiv6800798298 #yiv6800798298photos div div {
> border:1px solid
> #666666;min-height:62px;overflow:hidden;width:62px;}
>
> #yiv6800798298 #yiv6800798298photos div label {
> color:#666666;font-size:10px;overflow:hidden;text-align:
> center;white-space:nowrap;width:64px;}
>
> #yiv6800798298 #yiv6800798298reco-category {
> font-size:77%;}
>
> #yiv6800798298 #yiv6800798298reco-desc {
> font-size:77%;}
>
> #yiv6800798298 .yiv6800798298replbq {
> margin:4px;}
>
> #yiv6800798298 #yiv6800798298ygrp-actbar div a:first-child {
> margin-right:2px;padding-right:5px;}
>
> #yiv6800798298 #yiv6800798298ygrp-mlmsg {
> font-size:13px;font-family:Arial, helvetica, clean,
> sans-serif;}
>
> #yiv6800798298 #yiv6800798298ygrp-mlmsg table {
> font-size:inherit;font:100%;}
>
> #yiv6800798298 #yiv6800798298ygrp-mlmsg select,
> #yiv6800798298 input, #yiv6800798298 textarea {
> font:99% Arial, Helvetica, clean, sans-serif;}
>
> #yiv6800798298 #yiv6800798298ygrp-mlmsg pre, #yiv6800798298
> code {
> font:115% monospace;}
>
> #yiv6800798298 #yiv6800798298ygrp-mlmsg * {
> line-height:1.22em;}
>
> #yiv6800798298 #yiv6800798298ygrp-mlmsg #yiv6800798298logo {
> padding-bottom:10px;}
>
>
> #yiv6800798298 #yiv6800798298ygrp-msg p a {
> font-family:Verdana;}
>
> #yiv6800798298 #yiv6800798298ygrp-msg
> p#yiv6800798298attach-count span {
> color:#1E66AE;font-weight:700;}
>
> #yiv6800798298 #yiv6800798298ygrp-reco
> #yiv6800798298reco-head {
> color:#ff7900;font-weight:700;}
>
> #yiv6800798298 #yiv6800798298ygrp-reco {
> margin-bottom:20px;padding:0px;}
>
> #yiv6800798298 #yiv6800798298ygrp-sponsor #yiv6800798298ov
> li a {
> font-size:130%;text-decoration:none;}
>
> #yiv6800798298 #yiv6800798298ygrp-sponsor #yiv6800798298ov
> li {
> font-size:77%;list-style-type:square;padding:6px 0;}
>
> #yiv6800798298 #yiv6800798298ygrp-sponsor #yiv6800798298ov
> ul {
> margin:0;padding:0 0 0 8px;}
>
> #yiv6800798298 #yiv6800798298ygrp-text {
> font-family:Georgia;}
>
> #yiv6800798298 #yiv6800798298ygrp-text p {
> margin:0 0 1em 0;}
>
> #yiv6800798298 #yiv6800798298ygrp-text tt {
> font-size:120%;}
>
> #yiv6800798298 #yiv6800798298ygrp-vital ul li:last-child {
> border-right:none !important;
> }
> #yiv6800798298
>
>
>
