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

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

ไฟล์ประกอบ

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

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

ถ้าแปลงข้อมูลอันนี้ให้เป็นข้อมูลเชิง 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 ลงมาซะ

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

สร้าง DateKey

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

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

สร้าง ProductKey

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

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

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

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

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

จากนั้นแตกเอาคอลัมน์ 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

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

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

TotalTarget = SUM(fTarget[Target])

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

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

%AchieveTarget = DIVIDE([TotalRevenue],[TotalTarget])

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

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

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

สารบัญ Series Power BI

• POWER BI ตอนที่ 01: POWER BI คืออะไร?
• POWER BI ตอนที่ 02: พื้นฐาน EXCEL ที่สำคัญก่อนจะเรียนรู้ POWER BI
• POWER BI ตอนที่ 03: ภาพรวมการใช้งาน POWER BI DESKTOP
• POWER BI ตอนที่ 04: สร้าง REPORT แรก ใน POWER BI
• POWER BI ตอนที่ 05: วิธีการ DRILL เพื่อเจาะลึกข้อมูลใน REPORT
• POWER BI ตอนที่ 06: การปรับแต่งสีใน VISUAL ด้วย CONDITIONAL FORMAT
• POWER BI ตอนที่ 07: เริ่ม GET DATA ตั้งแต่ไฟล์ยังว่างเปล่า
• POWER BI ตอนที่ 08: สร้าง DATA MODEL ที่เหมาะสม
• POWER BI ตอนที่ 09: สร้าง DATE TABLE ด้วย DAX
• POWER BI ตอนที่ 10: เรียนรู้ DAX เบื้องต้น
• POWER BI ตอนที่ 11: เรียนรู้ DAX Table Function – FILTER
• POWER BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน
• POWER BI ตอนที่ 13: CALCULATE ฟังก์ชันที่ทรงพลังที่สุดใน DAX
• Power BI ตอนที่ 14: Context Transition และ พลังแฝงใน Measure
• Power BI ตอนที่ 15: วิธีดึงค่าจาก Slicer มาคำนวณใน Report
• Power BI ตอนที่ 16 : เดินทางข้ามเวลาไปกับ Time Intelligence DAX Function
• Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual
• Power BI ตอนที่ 18 : วิธีการกระจายเป้า Allocate Target ด้วย DAX
• Power BI ตอนที่ 19 : การปรับ Cross Filter Direction เพื่อคำนวณค่าในตาราง Dimension
• ส่วนเสริม
• การคำนวณต้นทุนแบบ FIFO ด้วย DAX
• แสดงข้อมูลสรุปแบบ Top N + Others (ฉบับเทพเอ็กเซล)
• การวิเคราะห์ Event ที่มีช่วงเวลาเริ่มต้นกับสิ้นสุดคนละวัน
• เปรียบเทียบ MAX vs LASTDATE ในภาษา DAX

ใครสนใจอยากเรียนเป็นคลิปวีดีโอ ผมมี