การใช้ Excel Power Pivot ตอนที่ 5 : การทำรายงาน Actual vs Target

การใช้ Pivot Table แบบปกตินั้นเวลาเราจะทำรายงานยอดขาย Actual vs Target เราจะทำต้องทำตาราง Pivot แยกกัน 2 อัน แล้วค่อยเอามาเทียบกันด้วยวิธีอะไรบางอย่าง ไม่ว่าจะ Copy Paste หรือ VLOOKUP มาอยู่ข้างๆ กัน แต่ถ้าหากเราใช้ Concept ของ Data Model เราจะสามารถทำรายงาน Actual vs Target ในตาราง Pivot เดียวได้เลย

โหลดไฟล์ Target

โหลดไฟล์ Target ได้ที่นี่

วิธีการทำรายงาน Actual vs Target

จากหลักการของ Data Model ที่เราเรียนมาในตอนที่แล้ว จะช่วยให้เราสามารถทำรายงาน Actual vs Target ได้โดยง่าย แค่หา Dimension Table ที่ Common กัน ระหว่าง Fact Table ที่เป็น Actual กับค่าตัวเลขของตาราง Target เราก็จะสามารถ Filter ข้อมูลจาก Field ที่อยู่ในตาราง Dimension ที่ Common กันนั้น แล้วส่งผลผ่าน Relationship ไปหาทั้งตาราง Actual และ Target พร้อมๆ กันนั่นเอง

เช่นข้อมูลในตาราง Target ผมเป็นแบบนี้

จะเห็นว่าข้อมูลที่พอจะนำไปเชื่อมกับ Dimension ที่ Common กับตาราง Actual ได้ก็คือ สินค้า กับเรื่องของวันที่นั่นเอง

ตอนนี้เรามีตารางสินค้าแล้ว ดังนั้นสิ่งที่เราควรจะทำคือ สร้างตารางวันที่ขึ้นมา

ตารางวันที่

เราสามารถสร้างตารางวันที่แบบที่ 1 บรรทัดคือ 1 วัน หรือ 1 บรรทัดคือ 1 เดือนก็ได้ แล้วแต่เราจะออกแบบ

ซึ่งถ้าเอาละเอียดก็ควรเป็นแบบที่ 1 บรรทัดมี 1 วัน ซึ่งจะสร้างตารางวันที่ใน Excel แล้ว Import เข้า Data Model หรือจะใช้ Power Query สร้างแบบ Dynamic ขึ้นมาก็ได้ (แต่ถ้าใช้ Power BI จะสร้างมารถใช้ DAX แบบ New Table สร้างได้ซึ่งเป็นวิธีที่ง่ายที่สุด)

ซึ่งหากใครอยากจะสร้างตารางวันที่ระดับวัน สามารถทำตามที่เคยได้แนะนำในบทความนี้ได้เลย

แต่เพื่อความง่าย ในบทความนี้เราจะทำตารางวันที่ในระดับเดือนละกัน (สมมติว่าในรายงานของเราจะทำสรุปแค่ระดับเดือน)

ดังนั้นเดี๋ยวผมจะเอาข้อมูลวันที่จาก Target ไปทำเป็นตารางวันที่ซะเลย โดยเดี๋ยวเราจะเปิดไฟล์หลัก (DataModel) แล้วทำการ Get Data จากไฟล์ Target แค่ปีกับเดือน แล้วแยกไปเป็น Query ใหม่ชื่อ DateTable ดังนี้

มาถึง คอลัมน์ปีมันเว้นว่างไว้ ก็สั่ง คลิ๊กขวา -> Fill Down ซะ แล้ว Remove Other Columns

จากนั้นก็ Add Column -> Column from Example แล้วสร้าง Field ที่เชื่อมปีกับเดือนซะ แบบนี้ ซึ่งมันจะสร้างสูตรให้ว่า

Text.Combine({Text.From([ปี], "th-TH"), Text.PadStart(Text.From([เดือน], "th-TH"), 2, "0")})

ผมตั้งชื่อคอลัมน์ใหม่นี้ว่า DateKey เป็นอันจบ

จากนั้นก็กด Close&Load to… แบบ Connection Only แต่ให้ติ๊ก Load เข้า Data Model ซะ

จัดการกับ Table Target

ต่อไปเราจัดการกับไฟล์ Target ให้กลายเป็นข้อมูลเชิง Database ที่เรียบร้อยขึ้นด้วยการ Get Data ใหม่อีกที จากนั้น Fill Down ปี แล้ว Unpivot สินค้าลงมาให้หมด ได้ดังนี้

คราวนี้เราจะไปทำ DateKey ใน Data Model บ้าง ขี้เกียจใช้ Column From Example เหมือนเดิมละ (เปลี่ยนวิธีบ้าง จะได้เห็นวิธีทำหลายๆ แบบเนอะ) ดังนั้นเรา Load Data นี้เข้า Data Model ไปเลย

จากนั้นเราเข้าไปดู Target ใน Power Pivot จะเห็นแบบนี้ (ซึ่งเป็นผลลัพธ์ของ Power Query)

ต่อไปเราจะสร้างคอลัมน์ใหม่ให้เป็น DateKey ด้วย DAX ดังนี้

DateKey:=Target[ปี]&FORMAT(Target[เดือน],"00")

• ซึ่งเราจะอ้างอิงคอลัมน์ด้วย ชื่อตาราง[ชื่อคอลัมน์]
• ฟังก์ชัน FORMAT จะเหมือนกับฟังก์ชัน TEXT ของ Excel มีความสามารถในการแปลง Value ตัวเลขให้เป็น Text ที่มีหน้าตาตาม Custom Number Format ที่กำหนดได้ (ซึ่งผมกำหนดให้เป็นเลข 2 หลัก ด้วย 00)
• จากนั้นเอามาเชื่อมกันด้วย &

เราทำแบบนี้กับตารางหลัก ซึ่งก็คือ TXDate เช่นกัน

DateKey:=TXData[ปี]&FORMAT(TXData[เดือน],"00")

จากนั้นมาดู Data Model ที่ Diagram View

0ากนั้นให้ทำการผูก Relationship เพิ่มเติมซะ

จากนั้น Add Measure Total Target เข้าไปในตาราง Target ซะ

แค่นี้เราก็พร้อมจะทำรายงาน Actual vs Target แล้ว

แค่ต้องลาก Field จากตาราง Dimension ที่ Common กันระหว่างตาราง Actual กัย Target แค่นี้ก็ดูข้อมูลเทียบกันได้แล้ว

ถ้าอยากรู้ว่า Actual ทำได้กี่ % เมื่อเทียบกับ Target ก็สร้าง Measure เพื่อจับหารกันได้ แต่การหารแบบดัก Error ให้เป็น Blank ได้สามารถใช้ฟังก์ชัน DIVIDE ใน DAX มาช่วยได้เลยดังนี้

=DIVIDE([TotalSales],[TotalTarget])

แค่นี้ก็สามารถแสดง %Achievement ได้แล้ว ไม่ต้อง Copy Paste, ไม่ต้อง VLOOKUP อะไรทั้งสิ้น

และนี่ก็คือตัวอย่างการทำรายงานแบบ Actual vs Target ด้วย Power Pivot ครับใครทำตามแล้วสงสัยอะไรก็สามารถ Comment ถามได้นะครับ

ศึกษา Power Query เพิ่มเติมได้ที่ไหน?

ในบทความนี้มีการใช้ Power Query มาช่วยจัดการข้อมูลพอสมควร ใครเห็นแล้วสนใจ อยากจะศึกษาให้ลึกซึ้งกว่านี้ สามารถศึกษาได้จาก (เข้าไปดูเนื้อหาบางส่วนได้ฟรี)

หรือถ้าชอบแบบอ่านหนังสือ ก็ศึกษาได้จาก หนังสือ Excel Power Upได้เลยครับ (สามารถอ่านเนื้อหาฟรีๆ บางส่วนได้ที่นี่)

สารบัญซีรีส์ 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 บรรทัด
• หลากวิธีเอาข้อมูลในกลุ่มเดียวกันไปรวมเป็นข้อความเดียวกัน