ก่อนที่เราจะทำรายงาน Actual vs Target ได้นั้นเราจะต้องมีความเข้าใจเรื่องของ Data Model ซะก่อนว่ามันทำงานยังไง ซึ่งในตอนที่แล้วผมแค่ทำการสร้าง Relationship แล้วทำรายงานให้ดูเฉยๆ แต่ยังไม่ได้อธิบายการทำงานของมันเลย จึงคิดว่าเพื่อเป็นพื้นฐานที่ดี เราควรจะเข้าใจการทำงานของมันอย่างลึกซึ้งยิ่งขึ้นซะก่อน
สารบัญ
สาเหตุที่วิเคราะห์ข้อมูลข้ามตารางได้
การที่เราสามารถลากข้อมูลจากคนละตารางมาวิเคราะห์ร่วมกันได้ เพราะมันมีการ Filter ข้อมูลข้ามตาราง ผ่านทิศทางของเส้น Relationship ซึ่งสังเกตว่ามันจะวิ่งจาก Dimension Table (ตารางอ้างอิง) ไป Fact Table (ตารางหลัก)
เช่น ที่เราลากประเทศลงมา แล้วตัวเลขต่างๆ ใน Pivot สามารถแสดงออกมาได้ถูกต้องตามรูปนี้ มันมีหลักการดังนี้
สมมติเราดูที่ประเทศลาว : การที่ได้เลข TotalSales 24,231 และ Count of TXID 49 มันมาจากอะไร?
เราต้องอย่าลืม Concept ที่สำคัญที่สุดของการคำนวณทุกอย่างใน Pivot Table นั่นก็คือ
ข้อมูลแต่ละช่องใน Pivot Table แท้จริงนั้นคำนวณมาจากการ Filter ข้อมูลตามสิ่งที่ระบุใน Rows Label, Column Label, และหัว Filter รวมถึง Slicer (เราเรียกบริบทการ Filter ทั้งหมดที่แต่ละช่องในรายงาน Pivot ต้องเจอว่า Filter Context) จากนั้นค่อยทำการคำนวณตามวิธีที่ระบุในช่อง Values (ซึ่งถ้าเป็น Power Pivot ก็สามารถคำนวณด้วยสูตรใน Measure)
เทพเอ็กเซลพยายามเน้นเรื่องนี้ตลอดนะ สำคัญมากๆ
ดังนั้นใน Row Label ที่เป็นของประเทศลาว จึงเปรียบเสมือนว่าเรา Filter ตาราง CustomerCountry ที่คอลัมน์ประเทศ ด้วยค่า “ลาว” ซึ่งจะทำให้เหลือ customer id แค่เบอร์ C00008, C00009, และ C00010 ดังนี้
ซึ่งค่าเหล่านี้เป็ Key ที่เชื่อมไปยังตารางหลัก ซึ่งก็คือ TXData ของเรา ซึ่งมันจะไป Filter ตารางหลักในคอลัมน์ลูกค้าด้วยค่าเหล่านี้ด้วย ซึ่งได้ผลดังนี้
จะเห็นว่าเลขที่คำนวณได้จาก Row ที่เหลือจากการ Filter นั้นตรงกับค่าใน Pivot เลย และนี่ก็คือที่มาที่ไปของเลขนั้น
และถ้าใน Pivot เราลากสินค้า (จากตาราง ProductCost) ลงมาอีก เช่น
หากดูที่ ประเทศลาว สินค้าเป็น dvd หนัง : จะเหลือ TotalSales 8,681 และ Count of TXID 10
มันก็เปรียบเสมือนมีการ Filter สินค้า ในตาราง ProductCost ให้เป็น dvd หนัง แล้วมันก็จะส่งผลให้ไป Filter ตารางหลักให้เป็น dvd หนังไปด้วยนั่นเอง (เรียกได้ว่ามีการรุมกัน Filter จากทั้ง CustomerCountry และ ProductCost เลยล่ะ)
Field สินค้ามี 2 ที่ ควรลากจากตารางไหน?
Field สินค้านั้นมีอยู่ทั้งในตาราง ProductCost (Dimension Table) และ TXData (Fact Table) ซึ่งจริงๆ ก็เป็นงี้กับทุกตารางนั่นแหละ
การจะลาก Field สินค้าจากตาราง ProductCost หรือลาก Field สินค้าจาก TXData ลงมาวิเคราะห์ ณ ตอนนี้จะมีค่าเท่ากัน… เพราะว่ามีการขายสินค้าใน ProductCost ทุกอย่างครบในตาราง TXData จริงๆ
แต่ถ้าหากว่าในตาราง ProductCost (Dimension Table) มีประเภท Product เยอะกว่า เช่น มี Product บางตัวของบริษัทที่ยังขายไม่ออก (จึงยังไม่มีในตาราง TXData) แบบนี้ผลลัพธ์จะไม่เหมือนกันได้ การจะเห็นภาพชัดกว่าก็ควรลากจากตาราง Dimension มากกว่าที่จะลากจาก Fact Table
เช่น เราไปเพิ่มสินค้าใน Product Cost ของ sheet Excel แล้ว Refresh ใน Data Model ดังนี้
จากนั้นไปกด Data -> Refresh All เพื่อให้ Data Model มีการอัปเดท
ทีนี้หากเราลองไปที่Pivot แล้ว ใส่สินค้าจากตาราง ProductCostไปที่ Row อย่างเดียว (อย่าเพิ่งใส่ Value) จะเห็น item เกมด้วย (ถ้าลากสินค้าจาก TXData จะไม่มีเกม)
แต่พอเราลาก TotalSales ลงมา เกมจะหายไป เพราะปกติแล้วหากค่า Value เป็น Blank มันจะไม่แสดงออกมา
หากเป็น Pivot Table ปกติ เราจะสามารถตั้งค่าให้แสดงแบบ Show item with no data ได้ แต่ใน Power Pivot มันจะติ๊ก option นี้ไม่ได้
ทางนึงที่พอจะแก้ได้คือ ให้เราแก้สูตรใน Measure ให้มันไม่มีทางเป็น Blank ซะเลย เช่น จับบวกเลข 0 ลงไป แค่นี้ก็ไม่มีทาง Blank แล้ว
ซึ่งเราสามารถไป Edit Measure ที่สร้างไว้แล้วใน Ribbon Power pivot ได้ โดยไปที่ Power Pivot -> Measures -> Manage Measures -> Edit แล้วแก้สูตรโดยบวก 0 เข้าไปซะ
แค่นี้เราก็สามารถแสดงค่า 0 ออกมาได้สบายๆ แล้ว
นอกจากนี้ บางทีเราต้องการจะนับ item แบบไม่ซ้ำด้วย DISTINCTCOUNT เราก็ต้องเลือกให้เหมาะสม
- ถ้าจะนับสินค้าทุกอย่างที่มีให้ขายก็ควรจะนับจาก Dimension Table
- =DISTINCTCOUNT(ProductCost[สินค้า])
- ถ้าจะนับสินค้าที่มีให้ขายแล้วจริงก็ควรจะนับจาก Fact Table
- =DISTINCTCOUNT(TXData[สินค้า])
เช่นแบบนี้
หวังว่าเพื่อนๆ จะพอเข้าใจหลักการทำงานของ Data Model มากขึ้นนะครับ ใครไม่เข้าใจตรงไหนก็ comment ถามได้นะ แต่อย่างที่บอกไปหลายครั้งว่า Concept ของ Power Pivot นั้นเหมือนกับ Power BI มาก ใครอยากเจาะลึกก็แนะนำ
ตอนต่อไป
ตอนต่อไป เราจะมาเรียนรู้เรื่องการทำรายงาน Actual vs Target กันจริงๆ ละ 555
สารบัญซีรีส์ Power Pivot
- วิธีกำหนดให้ Pivot Table แสดงเฉพาะแถว/คอลัมน์ที่ต้องการ
- การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน
- การใช้ Excel Power Pivot ตอนที่ 2 : ทำผลสรุป Value ให้เป็นข้อความด้วย DAX
- การใช้ Excel Power Pivot ตอนที่ 3 : ลาก่อน VLOOKUP สวัสดี Data Model
- การใช้ Excel Power Pivot ตอนที่ 4 : Data Model ทำงานอย่างไร
- การใช้ Excel Power Pivot ตอนที่ 5 : การทำรายงาน Actual vs Target
- Inventory Management ทำรายงานสินค้าคงคลังด้วย DAX และ Data Model ใน Excel
- วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด
- หลากวิธีเอาข้อมูลในกลุ่มเดียวกันไปรวมเป็นข้อความเดียวกัน
