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

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

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

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

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

ไฟล์ประกอบ

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

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

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

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

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

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

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

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

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

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

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

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

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

TotalTargetNew = SUM(fTarget[DailyTarget])

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

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

จะเห็นว่ามันเป็น 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 )
    )
allocate target

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

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

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

เล่นง่าย

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

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

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

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

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

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

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

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

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

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

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

FinalTarget =
[ThisMonthDailyTarget] * COUNTROWS ( VALUES ( dDate[Date] ) )
Power BI ตอนที่ 18 : วิธีการกระจายเป้า Allocate Target ด้วย DAX 11

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

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

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

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

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

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

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

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

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

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

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

FinalTarget2 =
SUMX ( DISTINCT ( dDate[Date] ), [ThisMonthDailyTarget] )
Power BI ตอนที่ 18 : วิธีการกระจายเป้า Allocate Target ด้วย DAX 14

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

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

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

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

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

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

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

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

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

สารบัญ Series Power BI

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

This image has an empty alt attribute; its file name is Powerful-Data-Power-BI-1024x538.png
แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 106
  •  
  •  
  •  
  •  
  • 106
  •  
  •  
  •  
  •  
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