เมื่อเรียนรู้วิธีใช้งาน Power Pivot เป็นครั้งแรก ผู้ใช้ส่วนใหญ่จะค้นพบว่าพลังที่แท้จริงอยู่ที่การรวมหรือคํานวณผลลัพธ์ในทางใดทางหนึ่ง ถ้าข้อมูลของคุณมีคอลัมน์ที่มีค่าตัวเลข คุณสามารถรวมคอลัมน์ดังกล่าวได้อย่างง่ายดาย โดยการเลือกคอลัมน์นั้นในรายการเขตข้อมูล PivotTable หรือ Power View โดยธรรมชาติแล้ว เนื่องจากเป็นตัวเลข จึงรวม หาค่าเฉลี่ย นับจํานวน หรือการรวมชนิดใดก็ตามที่คุณเลือกโดยอัตโนมัติ สิ่งนี้เรียกว่ามาตรการโดยนัย การวัดโดยนัยเหมาะอย่างยิ่งสําหรับการรวมที่ง่ายและรวดเร็ว แต่มีขีดจํากัด และขีดจํากัดเหล่านั้นสามารถเอาชนะได้เกือบทุกครั้งด้วยการวัดที่ชัดเจนและคอลัมน์จากการคํานวณ
ก่อนอื่นมาดูตัวอย่างที่เราใช้คอลัมน์จากการคํานวณเพื่อเพิ่มค่าข้อความใหม่สําหรับแต่ละแถวในตารางที่ชื่อ Product แต่ละแถวในตาราง ผลิตภัณฑ์ ประกอบด้วยข้อมูลทุกชนิดเกี่ยวกับแต่ละผลิตภัณฑ์ที่เราขาย เรามีคอลัมน์สําหรับชื่อผลิตภัณฑ์ สี ขนาด ราคาตัวแทนจําหน่าย ฯลฯ เรามีตารางที่เกี่ยวข้องอีกตารางหนึ่งที่ชื่อว่า ประเภทผลิตภัณฑ์ ซึ่งมีคอลัมน์ ProductCategoryName สิ่งที่เราต้องการให้ผลิตภัณฑ์แต่ละรายการในตาราง ผลิตภัณฑ์ รวมชื่อประเภทผลิตภัณฑ์จากตารางประเภทผลิตภัณฑ์ ในตาราง ผลิตภัณฑ์ ของเรา เราสามารถสร้างคอลัมน์จากการคํานวณที่ชื่อว่า ประเภทผลิตภัณฑ์ ได้ดังนี้
สูตรประเภทผลิตภัณฑ์ใหม่ของเราจะใช้ฟังก์ชัน DAX ที่เกี่ยวข้อง เพื่อรับค่าจากคอลัมน์ ProductCategoryName ในตารางประเภทผลิตภัณฑ์ที่เกี่ยวข้อง แล้วใส่ค่าเหล่านั้นสําหรับแต่ละผลิตภัณฑ์ (แต่ละแถว) ในตารางผลิตภัณฑ์
นี่เป็นตัวอย่างที่ดีของวิธีที่เราสามารถใช้คอลัมน์จากการคํานวณเพื่อเพิ่มค่าคงที่สําหรับแต่ละแถวที่เราสามารถใช้ภายหลังในพื้นที่แถวคอลัมน์หรือตัวกรองของ PivotTable หรือในรายงาน Power View
เรามาสร้างอีกตัวอย่างหนึ่งที่เราต้องการคํานวณอัตรากําไรสําหรับประเภทผลิตภัณฑ์ของเรา นี่เป็นสถานการณ์ทั่วไป แม้ในบทช่วยสอนจํานวนมาก เรามีตารางยอดขายในตัวแบบข้อมูลของเราที่มีข้อมูลธุรกรรม และมีความสัมพันธ์ระหว่างตารางยอดขายและตารางประเภทผลิตภัณฑ์ ในตารางยอดขาย เรามีคอลัมน์หนึ่งที่มียอดขาย และอีกคอลัมน์หนึ่งที่มีต้นทุน
เราสามารถสร้างคอลัมน์จากการคํานวณที่คํานวณจํานวนกําไรสําหรับแต่ละแถว โดยการลบค่าในคอลัมน์ COGS ออกจากค่าในคอลัมน์ SalesAmount ได้ ดังนี้
ตอนนี้ เราสามารถสร้าง PivotTable และลากเขตข้อมูล ประเภทผลิตภัณฑ์ ไปยัง คอลัมน์ และเขตข้อมูล กําไร ใหม่ของเราลงในพื้นที่ ค่า (คอลัมน์ในตารางใน PowerPivot คือเขตข้อมูลในรายการเขตข้อมูล PivotTable) ผลลัพธ์คือหน่วยวัดโดยนัยที่ชื่อว่า Sum of Profit ซึ่งเป็นจํานวนรวมของค่าจากคอลัมน์กําไรสําหรับแต่ละประเภทผลิตภัณฑ์ที่แตกต่างกัน ผลลัพธ์ของเรามีลักษณะดังนี้:
ในกรณีนี้ Profit จะสมเหตุสมผลในฐานะเขตข้อมูลใน VALUES เท่านั้น ถ้าเราใส่กําไรในพื้นที่คอลัมน์ PivotTable ของคุณจะมีลักษณะดังนี้:
เขตข้อมูลกําไรของเราไม่ได้ให้ข้อมูลที่เป็นประโยชน์เมื่อถูกวางไว้ในพื้นที่คอลัมน์ แถว หรือตัวกรอง ซึ่งจะสมเหตุสมผลเมื่อเป็นค่ารวมในพื้นที่ ค่าเท่านั้น
สิ่งที่เราทําคือสร้างคอลัมน์ชื่อ กําไร ที่คํานวณอัตรากําไรสําหรับแต่ละแถวในตารางยอดขาย จากนั้นเราเพิ่มกําไรลงในพื้นที่ค่าของ PivotTable ซึ่งจะสร้างหน่วยวัดโดยนัยโดยอัตโนมัติ ซึ่งมีการคํานวณผลลัพธ์สําหรับแต่ละประเภทผลิตภัณฑ์ ถ้าคุณคิดว่าเราคํานวณกําไรสําหรับหมวดหมู่ผลิตภัณฑ์ของเราสองครั้งจริงๆ คุณคิดถูก ก่อนอื่น เราคํานวณกําไรสําหรับแต่ละแถวในตาราง ยอดขาย จากนั้นเราจะเพิ่มกําไรลงในพื้นที่ ค่า ซึ่งกําไรถูกรวมไว้สําหรับแต่ละประเภทผลิตภัณฑ์ ถ้าคุณยังคิดว่าเราไม่จําเป็นต้องสร้างคอลัมน์จากการคํานวณกําไรจริงๆ คุณคิดถูกเช่นกัน แต่เราจะคํานวณกําไรของเราโดยไม่สร้างคอลัมน์คํานวณกําไรได้อย่างไร?
กําไรน่าจะคํานวณได้ดีกว่าเป็นมาตรการที่ชัดเจน
ในตอนนี้ เราจะปล่อยคอลัมน์กําไรที่คํานวณไว้ในตารางยอดขาย และประเภทผลิตภัณฑ์ไว้ในคอลัมน์ และกําไรในค่าของ PivotTable ของเราเพื่อเปรียบเทียบผลลัพธ์ของเรา
ในพื้นที่การคํานวณของตารางยอดขายของเรา เราจะสร้างหน่วยวัดที่เรียกว่า กําไรรวม (เพื่อหลีกเลี่ยงข้อขัดแย้งของการตั้งชื่อ) ในท้ายที่สุด มันจะให้ผลลัพธ์เช่นเดียวกับที่เราทํามาก่อน แต่ไม่มีคอลัมน์คํานวณกําไร
ขั้นแรก ในตารางยอดขาย เราจะเลือกคอลัมน์ SalesAmount แล้วคลิก ผลรวมอัตโนมัติ เพื่อสร้างหน่วยวัด ผลรวมของ SalesAmount ที่ชัดเจน โปรดจําไว้ว่าหน่วยวัดที่ชัดเจนคือหน่วยวัดที่เราสร้างในพื้นที่การคํานวณของตารางใน Power Pivot เราทําแบบเดียวกันกับคอลัมน์ COGS เราจะเปลี่ยนชื่อ Total SalesAmount และ Total COGS เหล่านี้เพื่อให้ระบุได้ง่ายขึ้น
จากนั้น เราจะสร้างหน่วยวัดอื่นด้วยสูตรนี้:
กําไรรวม:=[Total SalesAmount] - [Total COGS]
หมายเหตุ
เราสามารถเขียนสูตรของเราเป็น Total Profit:=SUM([SalesAmount]) - SUM([COGS]) แต่ด้วยการสร้างการวัด Total SalesAmount และ Total COGS แยกต่างหาก เราสามารถใช้ใน PivotTable ของเราได้เช่นกัน และเราสามารถใช้เป็นอาร์กิวเมนต์ในสูตรการวัดอื่นๆ ได้ทุกประเภท
หลังจากเปลี่ยนรูปแบบของหน่วยวัดกําไรรวมใหม่ของเราเป็นสกุลเงินแล้ว เราสามารถเพิ่มลงใน PivotTable ของเราได้
คุณสามารถดูการวัดกําไรรวมใหม่ของเราส่งกลับผลลัพธ์เดียวกันกับการสร้างคอลัมน์จากการคํานวณกําไรแล้ววางใน ค่า ความแตกต่างคือการวัดกําไรรวมของเรามีประสิทธิภาพมากกว่ามาก และทําให้ตัวแบบข้อมูลสะอาดตาและกระชับกว่า เนื่องจากเรากําลังคํานวณในเวลานั้นและเฉพาะเขตข้อมูลที่เราเลือกสําหรับ PivotTable เท่านั้น เราไม่จําเป็นต้องใช้คอลัมน์จากการคํานวณกําไรนั้นจริงๆ
เหตุใดส่วนสุดท้ายนี้จึงสําคัญ คอลัมน์จากการคํานวณจะเพิ่มข้อมูลลงในตัวแบบข้อมูล และข้อมูลจะใช้หน่วยความจํา ถ้าเรารีเฟรชตัวแบบข้อมูล ยังต้องใช้ทรัพยากรในการประมวลผลเพื่อคํานวณค่าทั้งหมดในคอลัมน์กําไรใหม่อีกด้วย เราไม่จําเป็นต้องใช้ทรัพยากรแบบนี้จริงๆ เนื่องจากเราต้องการคํานวณกําไรของเราจริงๆ เมื่อเราเลือกเขตข้อมูลที่เราต้องการทํากําไรใน PivotTable เช่น ประเภทผลิตภัณฑ์ ภูมิภาค หรือตามวันที่
ลองมาดูอีกตัวอย่างหนึ่ง ที่คอลัมน์จากการคํานวณสร้างผลลัพธ์ที่ในตอนแรกดูถูกต้อง แต่....
ในตัวอย่างนี้ เราต้องการคํานวณจํานวนยอดขายเป็นเปอร์เซ็นต์ของยอดขายทั้งหมด เราสร้างคอลัมน์จากการคํานวณที่ชื่อว่า % of Sales ในตารางยอดขายของเราดังนี้
สูตรของเราระบุว่า: สําหรับแต่ละแถวในตารางยอดขาย ให้หารจํานวนในคอลัมน์ SalesAmount โดยผลรวม SUM ของจํานวนทั้งหมดในคอลัมน์ SalesAmount
ถ้าเราสร้าง PivotTable และเพิ่มประเภทผลิตภัณฑ์ไปยังคอลัมน์ และเลือกคอลัมน์ % ของยอดขาย ใหม่ของเราเพื่อใส่ลงใน ค่า เราจะได้ผลรวมของ % ของยอดขายสําหรับแต่ละประเภทผลิตภัณฑ์ของเรา
ตกลง. สิ่งนี้ดูดีจนถึงตอนนี้ แต่มาเพิ่มตัวแบ่งส่วนข้อมูลกันเถอะ เราจะเพิ่ม Calendar Year แล้วเลือกปี ในกรณีนี้ เราจะเลือก 2007 นี่คือสิ่งที่เราได้รับ
เมื่อมองแวบแรก ข้อมูลนี้อาจยังปรากฏขึ้นอย่างถูกต้อง แต่เปอร์เซ็นต์ของเราควรรวม 100% เพราะเราต้องการทราบเปอร์เซ็นต์ของยอดขายทั้งหมดสําหรับแต่ละหมวดหมู่ผลิตภัณฑ์ของเราในปี 2007 แล้วเกิดอะไรขึ้น?
% ของคอลัมน์ยอดขายของเราคํานวณเปอร์เซ็นต์สําหรับแต่ละแถวที่เป็นค่าในคอลัมน์ SalesAmount หารด้วยผลรวมของค่าทั้งหมดในคอลัมน์ SalesAmount ค่าในคอลัมน์จากการคํานวณเป็นค่าคงที่ ซึ่งเป็นผลลัพธ์ที่ไม่สามารถเปลี่ยนแปลงได้สําหรับแต่ละแถวในตาราง เมื่อเราเพิ่ม % ของยอดขาย ไปยัง PivotTable ของเรา มันจะถูกรวมเป็นผลรวมของค่าทั้งหมดในคอลัมน์ SalesAmount ผลรวมของค่าทั้งหมดในคอลัมน์ % ของยอดขายจะเป็น 100% เสมอ
เคล็ดลับ
ตรวจสอบให้แน่ใจว่าได้อ่านบริบทในสูตร DAX ซึ่งจะทําให้เข้าใจบริบทระดับแถวและบริบทของตัวกรองเป็นอย่างดี ซึ่งเป็นสิ่งที่เรากําลังอธิบายในที่นี้
เราสามารถลบคอลัมน์จากการคํานวณ % ของยอดขายได้ เนื่องจากจะไม่ช่วยเรา แต่เราจะสร้างหน่วยวัดที่คํานวณเปอร์เซ็นต์ของยอดขายทั้งหมดได้อย่างถูกต้อง โดยไม่คํานึงถึงตัวกรองหรือตัวแบ่งส่วนข้อมูลใดๆ ที่ใช้
จําการวัด TotalSalesAmount ที่เราสร้างไว้ก่อนหน้านี้ ซึ่งเป็นหน่วยวัดที่รวมคอลัมน์ SalesAmount ได้ใช่หรือไม่ เราใช้เป็นอาร์กิวเมนต์ในการวัดกําไรรวม และเราจะใช้อีกครั้งเป็นอาร์กิวเมนต์ในเขตข้อมูลจากการคํานวณใหม่ของเรา
เคล็ดลับ
การสร้างการวัดที่ชัดเจน เช่น Total SalesAmount และ Total COGS ไม่เพียงมีประโยชน์ใน PivotTable หรือรายงานเท่านั้น แต่ยังมีประโยชน์เป็นอาร์กิวเมนต์ในการวัดอื่นๆ เมื่อคุณต้องการผลลัพธ์เป็นอาร์กิวเมนต์ ซึ่งทําให้สูตรของคุณมีประสิทธิภาพมากขึ้นและอ่านง่ายขึ้น นี่เป็นแนวทางการสร้างตัวแบบข้อมูลที่ดี
เราสร้างหน่วยวัดใหม่ด้วยสูตรต่อไปนี้:
% ของยอดขายทั้งหมด:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())
สูตรนี้ระบุว่า: หารผลลัพธ์จาก Total SalesAmount ด้วยผลรวมของ SalesAmount โดยไม่ต้องกรองคอลัมน์หรือแถวใดๆ นอกเหนือไปจากตัวกรองที่กําหนดไว้ใน PivotTable
เคล็ดลับ
อย่าลืมอ่านเกี่ยวกับฟังก์ชัน CALCULATE และ ALLSELECTED ในการอ้างอิง DAX
ตอนนี้ ถ้าเราเพิ่ม % ใหม่ของยอดขายรวมไปยัง PivotTable เราจะได้:
นั่นดูดีกว่า ตอนนี้ % ของยอดขายรวมสําหรับ แต่ละประเภทผลิตภัณฑ์จะคํานวณเป็นเปอร์เซ็นต์ของยอดขายรวมสําหรับปี 2007 ถ้าเราเลือกปีอื่น หรือมากกว่าหนึ่งปีในตัวแบ่งส่วนข้อมูล CalendarYear เราจะได้เปอร์เซ็นต์ใหม่สําหรับประเภทผลิตภัณฑ์ของเรา แต่ผลรวมทั้งหมดของเรายังคงเป็น 100% เราสามารถเพิ่มตัวแบ่งส่วนข้อมูลและตัวกรองอื่นๆ ได้ด้วย การวัด % ของยอดขายรวมของเราจะให้เปอร์เซ็นต์ของยอดขายทั้งหมดเสมอ โดยไม่คํานึงถึงการใช้ตัวแบ่งส่วนข้อมูลหรือตัวกรองใดๆ ด้วยการวัด ผลลัพธ์จะถูกคํานวณตามบริบทที่กําหนดโดยเขตข้อมูลใน คอลัมน์ และ แถว และโดยตัวกรองหรือตัวแบ่งส่วนข้อมูลใดๆ ที่นําไปใช้เสมอ นี่คือพลังของมาตรการ
ต่อไปนี้เป็นคําแนะนําเล็กๆ น้อยๆ ที่จะช่วยคุณตัดสินใจว่าคอลัมน์จากการคํานวณหรือการวัดนั้นเหมาะสมสําหรับการคํานวณที่ต้องการหรือไม่
ใช้คอลัมน์จากการคํานวณ
- ถ้าคุณต้องการให้ข้อมูลใหม่ของคุณปรากฏบน แถว คอลัมน์ หรือในตัวกรองใน PivotTable หรือบนแกน คําอธิบายแผนภูมิ หรือ เรียงต่อกันโดย ในการแสดงภาพของ Power View คุณต้องใช้คอลัมน์ที่คํานวณ เช่นเดียวกับคอลัมน์ข้อมูลทั่วไป คอลัมน์จากการคํานวณสามารถใช้เป็นเขตข้อมูลในพื้นที่ใดก็ได้ และถ้าเป็นตัวเลข คอลัมน์ก็สามารถรวมเป็นค่าได้เช่นกัน
- ถ้าคุณต้องการให้ข้อมูลใหม่ของคุณเป็นค่าคงที่สําหรับแถว ตัวอย่างเช่น คุณมีตารางวันที่ที่มีคอลัมน์วันที่ และคุณต้องการอีกคอลัมน์หนึ่งที่มีแค่ตัวเลขของเดือน คุณสามารถสร้างคอลัมน์จากการคํานวณที่คํานวณเฉพาะหมายเลขเดือนจากวันที่ในคอลัมน์วันที่ ตัวอย่างเช่น =MONTH('Date'[Date])
- ถ้าคุณต้องการเพิ่มค่าข้อความสําหรับแต่ละแถวลงในตาราง ให้ใช้คอลัมน์จากการคํานวณ เขตข้อมูลที่มีค่าข้อความจะไม่สามารถรวมเข้าใน VALUES ได้ ตัวอย่างเช่น =FORMAT('Date'[Date],"mmmm") จะให้ชื่อเดือนของวันที่แต่ละวันที่ในคอลัมน์วันที่ในตารางวันที่
ใช้หน่วยวัด
- ถ้าผลลัพธ์ของการคํานวณของคุณจะขึ้นอยู่กับเขตข้อมูลอื่นๆ ที่คุณเลือกใน PivotTable เสมอ
- ถ้าคุณต้องการทําการคํานวณที่ซับซ้อนมากขึ้น เช่น คํานวณการนับโดยยึดตามตัวกรองบางประเภท หรือคํานวณปีต่อปี หรือค่าความแปรปรวน ให้ใช้เขตข้อมูลจากการคํานวณ
- ถ้าคุณต้องการรักษาขนาดของเวิร์กบุ๊กให้เล็กที่สุดและเพิ่มประสิทธิภาพการทํางาน ให้สร้างการคํานวณของคุณให้เป็นการวัดให้ได้มากที่สุด ในหลายกรณี การคํานวณทั้งหมดของคุณสามารถเป็นการวัดได้ ซึ่งช่วยลดขนาดของเวิร์กบุ๊กและเร่งเวลาการรีเฟรชอย่างมาก
โปรดทราบว่าไม่มีอะไรผิดปกติที่จะสร้างคอลัมน์จากการคํานวณเหมือนกับที่เราทํากับคอลัมน์กําไรของเรา แล้วรวมไว้ใน PivotTable หรือรายงาน จริงๆ แล้วเป็นวิธีที่ดีและง่ายในการเรียนรู้และสร้างการคํานวณของคุณเอง เมื่อทําความเข้าใจฟีเจอร์ที่มีประสิทธิภาพอย่างยิ่งสองอย่างของ Power Pivot เพิ่มขึ้น คุณอาจต้องการสร้างตัวแบบข้อมูลที่มีประสิทธิภาพและแม่นยํามากที่สุดเท่าที่จะทําได้ หวังว่าสิ่งที่คุณได้เรียนรู้จากที่นี่จะช่วยได้ มีแหล่งข้อมูลที่ยอดเยี่ยมอื่นๆ ที่สามารถช่วยคุณได้เช่นกัน ต่อไปนี้เป็นบางส่วน: บริบทในสูตร DAX การรวมใน Power Pivot และศูนย์ทรัพยากร DAX และแม้ว่าจะขั้นสูงกว่าเล็กน้อย และมุ่งเป้าไปที่ผู้เชี่ยวชาญด้านการบัญชีและการเงิน การสร้าง ตัวแบบและการวิเคราะห์ข้อมูลกําไรขาดทุนด้วย Microsoft Power Pivot ในตัวอย่าง Excel ก็เต็มไปด้วยตัวอย่างการสร้างตัวแบบข้อมูลและสูตรที่ยอดเยี่ยม