Power BI ตอนที่ 18 : วิธีการกระจายเป้า Allocate Target ด้วย DAX

ในตอนที่แล้วผมได้พูดถึงเรื่องการทำ Data Model ที่รองรับการแสดงรายงานแบบ Target vs Actual ไปแล้ว แต่ว่าเราแสดงเป็น Target ระดับเดือนอยู่ ซึ่งหากว่าเราอยากจะแสดง ยอดขายที่ละเอียดแต่ละวันด้วย ดังนั้นเราก็จะต้องแจกเป้า หรือ Allocate Target ให้ลงมาถึงที่ระดับวันด้วยนั่นเอง

อีกประเด็นนึงก็คือ ถึงเราไม่ต้องการจะลงรายละเอียด Target ถึงระดับวัน แต่ถ้าใน Visual ดันดึงข้อมูลระดับวันมา มันก็จะมี Target ทั้งเดือนอยู่ที่วันแรกหมดเลย ซึ่งดูไม่ Make Sense ดังรูป ซึ่งก็อาจจะต้องจัดการอยู่ดี

ไฟล์ประกอบ

ใช้ไฟล์จากบทที่แล้วต่อ หรือโหลดอันนี้ได้เลย

แนวคิดเบื้องต้นก็คือ เราจะหา Target เฉลี่ยของแต่ละวันให้ได้ก่อน แล้วค่อยเอามาแสดงค่า ขึ้นอยู่กับว่า Filter Context ของ Visual ที่แสดงอยู่มันมีกี่วัน

คำนวณ Target ระดับวัน

การจะหา Target เฉลี่ยของแต่ละวันเราจะใช้ DAX คำนวณก็ได้ หรือจะทำตั้งแต่ใน Power Query ก็ได้เช่นกัน แต่เพื่อให้ง่าย เราจะใช้ Power Query ทำ โดยกด Transform Data แล้วเข้าไป Edit Query ของ fTarget ดังนี้

หาจำนวนวันในเดือน โดยคลิ๊กที่วันที่ แล้วเลือกตามรูป

จากนั้นค่อยเอา Target รายเดือน มาหารด้วยจำนวนวันในเดือน

จากนั้นเราก็จะได้ Target รายวันมา ซึ่งผมขอตั้งชื่อว่า DailyTarget

กด Close & Apply เพื่อ Load ข้อมูลเข้า Data Model

จากนั้นลองสร้าง Measure ใหม่ โดย SUM ค่าของ Daily Target มาดังนี้

TotalTargetNew = SUM(fTarget[DailyTarget])

หากลองลากเข้า Visual ก็จะเป็นดังนี้

จะเห็นว่ามันเป็น Target ระดับวันแล้ว แต่มีแค่วันเดียว สิ่งที่เราจะต้องคิดคือเราจะต้องทำให้มันโผล่ขึ้นมาทุกวัน

เริ่ม Allocate Target

โดยหลักการคือ เราต้องใช้ DAX ดึงค่า Target จากวันที่ 1 ของแต่ละเดือน มาโปรยลงวันอื่นๆ ด้วย ซึ่งเราจะใช้ CALCULATE ในการเปลี่ยน Filter Context ของวันอื่นๆ ให้กลายเป็นวันที่ 1 ให้หมดเลย ดังนี้

ThisMonthDailyTarget =
VAR currentDate =
    FIRSTDATE ( dDate[Date] )
VAR currentYear =
    YEAR ( currentDate )
VAR currentMonth =
    MONTH ( currentDate )
RETURN
    CALCULATE (
        [TotalTargetNew],
        dDate[Date] = DATE ( currentYear, currentMonth, 1 )
    )

จะเห็นว่าเราสามารถแสดง Target ลงระดับวันได้แล้ว

แต่ว่า… หากเราเอาแกนเลขวัน DayNum ออกจาก Visual แล้ว ตัว Measure ของเราก็จะผิดทันที เพราะมันก็จะกลายเป็น Target ของวันเดียว แต่ตัว TotalTarget เรายังใช้ได้นี่!

เล่นง่าย

ดังนั้นเราอาจจะสร้าง Measure ตัวใหม่ขึ้นมา เป็นตัวตัดสินว่าจะใช้ Measure ตัวไหนมาแสดงดี ขึ้นกับว่าในรายงานมันแสดง Dimension ละเอียดถึงระดับวันหรือไม่? ดังนี้

SelectTarget =
IF ( HASONEVALUE ( dDate[DayNum] ), [ThisMonthDailyTarget], [TotalTarget] )

ถ้า DayNum ไม่ได้มีค่าเดียว (แสดงว่าไม่ใช่ระดับวัน) เราก็แสดง TotalTarget

แต่ถ้า DayNum มีค่าเดียว (แสดงว่าเป็นระดับวัน) เราก็แสดง ThisMonthDailyTarget ได้แล้ว

จริงๆ แล้ววิธีข้างบนก็มีปัญหา

วิธีข้างบนจะใช้ได้ ก็ต้อเมื่อเราเลือกว่าจะแสดงระดับเดือน หรือระดับวันเดียวไปเลย (ไม่ได้เลือกวันที่แค่บางช่วงของเดือน) ซึ่งจะมีปัญหาทันทีหากเลือกวันที่แค่บางช่วง เช่น ดูยอดแค่ 10 วันแรก มันก็ดันเอา Target ทั้งเดือนมาใช้อยู่ดี (เพราะว่ามี DayNum มากกว่า 1 Value เลยไปใช้ระดับ Month) ดังรูป

เอาจำนวนวันมาคูณได้มั้ย?

ดังนั้น ถ้าจะให้เนียนขึ้น เราอาจคิดว่า ThisMonthDailyTarget มาคูณด้วยจำนวนวันใน Filter Context นั้นๆ มากกว่า ซึ่งเราจะใช้ Measure ใหม่แทน SelectTarget ดังนี้

FinalTarget =
[ThisMonthDailyTarget] * COUNTROWS ( VALUES ( dDate[Date] ) )

ซึ่งลองไม่ Filter ระดับวัน ผลลัพธ์ก็ไม่ผิดด้วย เพราะมันจะคูณกับจำนวนวันในเดือน จนออกมาถูกต้องเอง

แต่มันยังมีจุดบอดที่ตัว Total

วิธีที่เราคิดข้างบนก็เหมือนจะถูกแล้ว แต่จริงๆ แล้วยังผิดที่ตัวรวมอยู่ เพราะมันดับเอา DailyTarget ของเดือนแรกที่เห็นใน Visual มาคูณจำนวนวันที่เห็นทั้งหมด

ถ้าจะให้เห็นภาพชัดขึ้น ต้องลองทำให้เห็นข้อมูลแค่ไม่กี่วัน แต่ให้มันข้ามเดือนซะ เช่น

จะเห็นว่ายอดรวม 2616129.03 ไม่เท่ากับ 2 เดือนรวมกันนะ ดังนั้นวิธีนี้จึงถือว่ายังไม่ดีเท่าไหร่

แนวคิดที่ดีที่สุด

ดังนั้นแนวคิดที่ดีกว่าคือการใช้ DAX สร้างตารางจำลองวันที่แต่ละวันขึ้นมาในช่วง Filter Context นั้นๆ แล้วเรียกเอา ThisMonthDailyTarget มา Sum กันซะ ซึ่งฟังก์ชันที่ทำแบบนั้นได้ก็คือ SUMX นั่นเอง

ซึ่งพอ SUMX ใช้ผสมกับ Measure [ThisMonthDailyTarget] ก็จะเกิด Context Transition เกิดขึ้นในแต่ละแถวก่อนจะ Sum (ใครไม่เข้าใจลองย้อนไปอ่านเรื่อง Context Transition ได้ที่นี่)

แบบนี้จะออกมาถูกต้องที่สุด และ Dynamic มากที่สุดด้วยครับ

FinalTarget2 =
SUMX ( DISTINCT ( dDate[Date] ), [ThisMonthDailyTarget] )

จะเห็นว่าผลลัพธ์จะถูกเสมอ ไม่ว่าจะเลือก Filter แบบไหนก็ตาม นี่สิวิธีที่ดีที่สุด

สรุปแล้วเราก็ใช้แค่ TotalTargetNew (ที่เป็น DailyTarget) , ThisMonthDailyTarget (ที่เป็น DailyTarget แบบ Allocate ลงรายวัน ) และ FinalTarget2 (ที่ใช้ SUMX) เอง ตัวอื่นไม่ใช้ก็ลบไปซะ เช่น SelectTarget, FinalTarget ก็ไม่ใช้แล้ว

และตัว TotalTarget เราอาจจะอยากใช้กรณีที่อยากเห็น Target ของทั้งเดือนหรือทั้งปี แม้วันที่ยังไม่ครบตามนั้น

ดังนั้นก็ต้องเพิ่ม Measure %AchieveTarget ใหม่เป็นดังนี้ด้วย

%AchieveTargetAll = DIVIDE([TotalRevenue],[TotalTarget])
%AchieveTargetDaily = DIVIDE([TotalRevenue],[FinalTarget2])

เวลาเราเอามาใช้ใน visual ต่างๆ ค่าที่ได้ก็จะแม่นยำมากที่สุด

และนี่ก็คือการ Allocate Target ครับ ซึ่งอาจจะดูยุ่งยากอยู่บ้าง แต่ผมอยากจะลองไล่ Logic ให้ดูว่าทำไมการทำแบบบ้านๆ มันอาจจะผิดยังไงได้บ้าง และแบบที่ดีควรเป็นยังไง ถ้าอ่านแล้วสงสัยยังไงก็ Comment บอกได้นะครับ

สารบัญ 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

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