เทพเอ็กเซล ช่วย "ปลุกเทพ Excel ในตัวคุณ"
0
  • No products in the cart.
Data Model

Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual

target vs actual

เนื้อหาตอนนี้คิดว่าเป็นเรื่องที่หลายๆ คนอยากรู้ นั่นก็คือวิธีทำรายงานเทียบเป้าหมาย Target vs Actual โดยที่สามารถจะแสดงข้อมูลใน Visual เดียวกันได้ เทียบได้ว่าค่าจริงต่างจากเป้าหมายเท่าไหร่เป็นต้น

ไฟล์ประกอบ

โหลดอันนี้ได้เลย มีไฟล์ Target ให้ด้วยครับ

หน้าตาของไฟล์ Target ของเราเป็นแบบนี้

Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual 1

ถ้าแปลงข้อมูลอันนี้ให้เป็นข้อมูลเชิง Database เราจะสามารถนำไปสร้าง Relationship กับตารางอื่นๆ ใน Data Model ได้

จะทำ Target vs Actual อะไรได้บ้าง?

ไฟล์ Target ของเรามีการแบ่งมิติตาม Year Month และ ProductSubCategory ซึ่งเราจะสามารถทำ Target ตามมิติเหล่านี้ได้อย่างแน่นอน รวมถึงมิติที่ใหญ่กว่าซึ่งเกิดจากผลรวมของตัวเหล่านี้ด้วย เช่น ProductCategory ก็จะสามารถทำได้ไปด้วย

อย่างไรก็ตาม ถ้าหากอยากดูในมิติที่ย่อยลงกว่าเดิม มันขึ้นกับว่ามีวิธี Allocate ค่า Target จากตัวใหญ่ไปตัวย่อยหรือไม่? เช่น ถ้าจะ Allocate ค่าจากรายเดือน เป็นรายวัน ก็น่าจะทำได้ เพราะเอาไปหารจำนวนวันในเดือนก็น่าจะจบ แต่ถ้าจะดู Budget ของสินค้าแต่ละอันเลย จะทำไม่ได้ เพราะไม่มีวิธี Allocate ที่น่าจะดีพอ เป็นต้น

ผูก Data Model ยังไง?

Data Model ของเราน่าจะต้องผูกกับ ตารางวันที่ และ ตารางสินค้าอย่างแน่นอน ซึ่งการจะผูกกับตารางวันที่อย่าง dDate ได้จะต้องใช้ Key เชื่อมที่เป็นระดับวันที่ และจะผูกตารางสินค้าซึ่งตอนนี้เรามี dProduct ได้ ก็จะต้องมี Key ระดับ ProductID นี่คือสิ่งที่ต้องรู้ในการผูก Data Model

สำหรับตารางวันที่ที่ต้องใช้วันเป็นตัวเชื่อม เรายังทำให้ตาราง Target ของเราเพิ่มคอลัมน์วันที่ไปได้ง่ายๆ เช่น เพิ่มให้เป็นวันที่ 1 หรือวันสุดท้ายของแต่ละเดือนไปซะ ก็พอจะเชื่อมได้

แต่ว่าสินค้า ใน Budget เรามีแต่ SubCategory ไม่มีข้อมูล ProductKey ให้ผูกเลย ทางแก้คือ เราต้องหาทางสร้างคอลัมน์ ProductKey ที่เป็นตัวแทนของ SubCategory นั้นๆ มาให้ได้ เพื่อจะเป็น Key ผูกให้ได้นั่นเอง

สรุปแล้ว เราจะต้องเพิ่มคอลัมน์ตัวแทน 2 อัน คือ DateKey และ ProductKey ขึ้นมาในตาราง Target ด้วย

เริ่มสร้างตาราง Target ให้ใช้งานได้จริง

เริ่มจาก Get Data จาก Excel มา แล้ว Promote Header และ Fill Down ProductSubCategory ซะ จากนั้นลบคอลัมน์ Grand Total ด้านขวาสุดออก

จากนั้นเลือกคอลัมน์ ProductSubcategory และ Year จากนั้นคลิ๊กขวา Unpivot Other Columns ลงมาซะ

Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual 2

เปลี่ยนชื่อคอลัมน์ให้เรียบร้อย

Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual 3

สร้าง DateKey

ทีนี้เราจะสร้างคอลัมน์ใหม่เพื่อให้เป็นวันที่วันแรกของเดือน วิธีที่ง่ายที่สุดคือสร้างคอลัมน์ใหม่ด้วย Column From Example จาก Year และ Month แบบนี้

Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual 4

จากนั้นกดเปลี่ยนประเภทข้อมูลให้เป็น Date ซะ (ถ้าไม่ได้ให้กด Using Locale)

Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual 5

สร้าง ProductKey

การจะหา ProductKey มาเป็นตัวแทนของแต่ละ SubCategory วิธีที่ง่ายที่สุดคือสร้าง Query อีกตัวแล้ว Reference ค่าจาก dProduct ออกมาก่อน

Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual 6

ทำให้เหลือแค่ ProductKey และ ProductSubcategory โดยเลือก 2 ตัวนี้แล้วกด Remove Other Columns

Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual 7

จากนั้นสั่ง Remove Duplicates ที่คอลัมน์ ProductSubcategory ซะ

Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual 8

จากนั้นเปลี่ยนชื่อ Query เป็น MapProductKey ซะ แล้วเลือกเอา Enable Load ออกด้วย เพราะเราจะไม่เอาผลลัพธ์ตารางนี้ออกไปยัง Data Model

จากนั้นกลับไปยัง Target แล้วสั่ง Merge กับ MapProductKey เพื่อจะดึงค่า ProductKey ที่เป็นตัวแทนของแต่ละ ProductSubcategory มายังตาราง Target

Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual 9

จากนั้นแตกเอาคอลัมน์ ProductKey ออกมาอันเดียวก็จบ

เปลี่ยนชื่อ Query ให้เป็น fTarget ซะ แล้วกด Close & Apply

ทำ Data Model รองรับ Target vs Actual

จากนั้นก็ผูก Date กับ DateKey เข้าด้วยกันซะ (เพราะชื่อคอลัมน์ไม่ตรงกัน เราเลยต้องทำเอง) สุดท้ายจะได้ Data Model แบบนี้ ซึ่งแปลว่า dDate และ dProduct สามารถ Filter ได้ทั้ง fSales และ fTarget พร้อมๆ กันแล้ว (ดูทิศทางการ Filter จากลูกศร)

ซึ่ง Model แบบนี้ก็คือ Model ที่มี Fact Table 2 ตาราง (ขึ้นไป) นั่นเอง แต่เป็นแบบที่ไม่ได้ผูกกันเองนะ แต่ผูกผ่าน Dimension Table อื่นๆ เช่น dDate กับ dProduct

Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual 10

เริ่มสร้างรายงาน Target vs Actual

ให้เราสร้าง Measure ที่ชื่อว่า TotalTarget ขึ้นมา โดยใช้สูตรที่ง่ายที่สุดในโลกอย่าง SUM ธรรมดาๆ

TotalTarget = SUM(fTarget[Target])

จากนั้นก็สามารลาก TotalTarget เข้า Visual ได้แล้ว และจะเห็นว่าเราสามารถลาก Dimension ที่มีความละเอียดเทียบเท่าหรือใหญ่กว่า Dimension ในตาราง fTarget ได้สบายๆ เช่น ProductCategory นั้นหยาบกว่า สามารถใช้ได้เลย รวมถึง MonthName ที่มีความละเอียดเทียบเท่ากันด้วย

Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual 11

และแน่นอนว่าถ้าจะทำคำนวณพวก % Achievement นั้นก็ง่ายเหลือเกิน แค่เอา Actual มาหารด้วย Target

%AchieveTarget = DIVIDE([TotalRevenue],[TotalTarget])
Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual 12

จากนั้นจะทำ Visual เป็นกราฟแบบไหนก็แล้วแต่คุณแล้ว

Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual 13

ซึ่งจะใช้พวก Custom Visual มาช่วยก็ได้กราฟเท่ๆ ไปอีกแบบนึง เช่น อันนี้เป็น Custom Visual ชื่อว่า Bullet Chart by OKViz ครับ ซึ่งทำให้ประหยัดเนื้อที่เรื่อง Target ไปได้เยอะเลย เพราะเหลือแค่ขีดเอง

Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual 14

เดี๋ยวบทความนี้เราจะขอจบเท่านี้ก่อนดีกว่า (เริ่มยาวละ) ในตอนต่อไปผมจะมาแนะนำวิธี Allocate Target กรณีที่จะดูในมิติที่ย่อยกว่าในตาราง fTarget ที่ทำไว้นะครับ

สารบัญ Series Power BI

ใครสนใจอยากเรียนเป็นคลิปวีดีโอ ผมมีคอร์สออนไลน์ที่สอน Power BI ตั้งแต่พื้นฐาน สามารถไปดูรายละเอียดได้ที่นี่

This image has an empty alt attribute; its file name is Powerful-Data-Power-BI-1024x538.png
แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 562
  •  
  •  
  •  
  •  
  • 562
  •  
  •  
  •  
  •  
Sira Ekabut
the authorSira Ekabut
• Microsoft Office Specialist : Excel ระดับ Expert • ก่อตั้งเว็บไซต์และ แฟนเพจเทพเอ็กเซล คนติดตาม 8 หมื่นคน+, คลิปวีดีโอ 1 ล้าน Views + • หนังสือ Best Seller คอมพิวเตอร์ : Excel Level Up! , จอมเวท เทพ Excel , Excel Power Up!วิทยากรใน SkillLane.com คอร์สออนไลน์ชั้นนำ / อบรม Excel ให้บริษัทชั้นนำ

Leave a Reply