“งั้นถ้าอยากสรุปจำนวนออเดอร์ที่ส่งแล้วและยังไม่ส่ง ควรทำยังไงดีคะ?” น้องนิจิคนงามถามต่อ
“ต้องสร้างตาราง และ measure เพิ่มครับ” ผมตอบ
“ทำไมต้องสร้างตารางเพิ่มด้วยล่ะคะ?”
“เพราะตอนนี้เรายังไม่มีคอลัมน์ใดที่มีทั้งชื่อเดือนและคำว่า “Not Delivered” อยู่ในคอลัมน์เดียวกัน” พูดคำว่า “เรา” แล้วรู้สึกดีจัง ^^
(บทความนี้คือ ep.3 อ่าน ep.1 ได้ที่นี่ และ ep.2 ได้ที่นี่)
รีพอร์ตที่น้องนิจิต้องการหน้าตาแบบนี้ครับ

รีพอร์ตนี้ไม่สามารถใช้ DMonthYear เป็น Columns ใน Matrix
เพราะ DMonthYear มีแต่ชื่อเดือน ไม่มีคำว่า “Not Delivered”
จึงต้องสร้างตารางขึ้นมาใหม่ เขียนสูตรใหม่ และปรับอะไรบางอย่าง
สรุปเป็นขั้นตอนทั้งหมด 3 ขั้นดังนี้
- สร้างตารางที่มีคำว่า Not Delivered
- สร้าง measure คำนวณ
- สังเกต Subtotal
1. สร้างตารางที่มีคำว่า Not Delivered
อาจตั้งชื่อตารางใหม่ว่า tDMonthYear_wNotDelivered (หรือใช้ชื่ออื่นที่เข้าใจง่ายก็ได้)
โดยหลักการแล้วคือการสรุปคอลัมน์ DMonthYear และ DYearMonthNum สร้างได้โดยคลิก Table tools/ New table

ใช้ฟังก์ชัน SUMMARIZE หรือเขียนสูตรเป็น
tDMonthYear_wNotDelivered =
SUMMARIZE(
tDeliveryDate,
tDeliveryDate[DYearMonthNum],
tDeliveryDate[DMonthYear]
)
ถ้าเขียนสูตรแค่นี้จะไม่มีคำว่า “Not Delivered” จึงต้องเพิ่มบรรทัดที่มีคำว่า Not Delivered เข้าไปด้วย
โดยกำหนดให้ DMonthYear เป็น Not Delivered
และกำหนดให้ DYearMonthNum เป็น 300000
(หรือใช้เลขอื่นที่มีค่ามากกว่า Year*12 + Month สูงสุด เช่น ต้องมากกว่า 24252)
การเพิ่มบรรทัดใช้ฟังก์ชัน ROW หรือเขียนสูตรเป็น
ROW(
"DYearMonthNum",300000,
"DMonthYear","Not Delivered"
)
รวมข้อมูลเข้าด้วยกันโดยใช้ฟังก์ชัน UNION
tDMonthYear_wNotDelivered =
UNION(
SUMMARIZE(
tDeliveryDate,
tDeliveryDate[DYearMonthNum],
tDeliveryDate[DMonthYear]
),
ROW(
"DYearMonthNum",300000,
"DMonthYear","Not Delivered"
)
)
ได้ผลลัพธ์เป็นตารางนี้

ปรับคอลัมน์ DMonthYear ให้ Sort by column: DYearMonthNum

ไม่ต้องสร้างความสัมพันธ์ระหว่างตารางนี้กับตารางใด เพราะสร้างขึ้นเพื่อใช้เฉพาะกิจ ศัพท์เทคนิคของตารางแบบนี้เรียกว่า Disconnected Table

คอลัมน์น DMonthYear จากตารางนี้จะถูกใช้เป็น Columns ในรีพอร์ตสุดท้ายนั่นเอง

2. สร้าง measure คำนวณ
ลองนึกภาพว่าตัวเลขในตารางมีอะไรเป็นเงื่อนไขบ้าง (ศัพท์เทคนิคคำว่าเงื่อนไขของตัวเลขใด ๆ คือ Filter Context)

จากภาพด้านบน เงื่อนไข (Filter Context) ของตัวเลข 2,143 คือ
OMonthYear = Oct-20
DMonthYear = Nov-20

จากภาพด้านบน เงื่อนไข (Filter Context) ของตัวเลข 649 คือ
OMonthYear = Oct-20
DMonthYear = Not Delivered
การคำนวณแบบมีเงื่อนไข มักใช้ฟังก์ชัน CALCULATE
เนื่องจากตาราง tDMonthYear_wNotDelivered ไม่มีความสัมพันธ์กับตาราง tOrder การจะรับเงื่อนไขจาก tDMonthYear_wNotDelivered ได้นั้นอาจใช้ฟังก์ชัน SELECTEDVALUE
เพื่อให้สูตรดูเข้าใจง่าย ใช้เทคนิคการสร้างตัวแปรภายใน หรือ VAR .. RETURN แบบนี้
VAR DeliverMonthYear =
SELECTEDVALUE(tDMonthYear_wNotDelivered[DMonthYear])
การคำนวณแบ่งเป็น 2 กรณีคือ
- กรณีนับจำนวนออเดอร์ที่ส่งแล้ว (Delivered)
- กรณีนับจำนวนออเดอร์ที่ยังไม่ส่ง (Not Delivered)
กรณีนับจำนวนออเดอร์ที่ส่งแล้ว (Delivered) ใช้สูตร
VAR Delivered =
CALCULATE(
COUNTROWS(tOrder),
tDeliveryDate[DMonthYear]=DeliverMonthYear
)
กรณีนับจำนวนออเดอร์ที่ยังไม่ส่ง (Not Delivered) ให้นับเฉพาะค่าว่างในคอลัมน์ DeliverDate
การนับค่าว่าง ใช้ฟังก์ชัน COUNTBLANK
หรือเขียนสูตรเป็น
VAR NotDelivered =
COUNTBLANK(tOrder[DeliveryDate])
เวลาคำนวณต้องเช็คก่อนว่าคอลัมน์ DMonthYear เป็น “Not Delivered” หรือไม่
ถ้าใช่ ให้คำนวณแบบ NotDelivered
ถ้าไม่ใช่ ให้คำนวณแบบ Delivered
ดังนั้นต้องใช้ฟังก์ชัน IF ช่วย โดยเงื่อนไขคือ
DeliverMonthYear=”Not Delivered”
หรืออาจเขียนสูตรเป็น
VAR Result =
IF(
DeliverMonthYear="Not Delivered",
NotDelivered,
Delivered
)
ได้ตรรกะครบแล้ว คราวนี้ก็จับตรรกะทั้งหมดมารวมกัน สร้างเป็น measure
คลิก New Measure

สูตร DAX ที่ใช้คือ
#Order_wNotDelivered =
VAR DeliverMonthYear =
SELECTEDVALUE(tDMonthYear_wNotDelivered[DMonthYear])
VAR Delivered =
CALCULATE(
COUNTROWS(tOrder),
tDeliveryDate[DMonthYear]=DeliverMonthYear
)
VAR NotDelivered =
COUNTBLANK(tOrder[DeliveryDate])
VAR Result =
IF(
DeliverMonthYear="Not Delivered",
NotDelivered,
Delivered
)
RETURN Result
สร้างกราฟ Matrix โดยกำหนดค่าดังนี้
Rows: OMonthYear
Columns: DMontYear (จากตาราง tDMonthYear_wNotDelivered)
(ย้ำ! จากตาราง tDMonthYear_wNotDelivered ไม่ใช่ tDeliveryDate)
Values: #Order_wNotDelivered

สร้าง Slicer คอลัมน์ OYear ช่วยวิเคราะห์
ผลลัพธ์ที่ได้คือ

3. สังเกต Subtotal
“หวาว! พี่เก่งมากเลยค่ะ” น้องนิจิประกบมือเข้าหากันเบา ๆ อิริยาบทช่างเรียบร้อย
“แหม่ ไม่เท่าไรหรอกครับ” ผมยกมือขวาลูบท้ายทอย การถูกคนสวยชมมันก็เขินนะ ^^
“แต่.. เอ๊ะ!” น้องนิจิร้อง
“มีอะไรหรือครับ?”
“ทำไม Total บางเดือนถึงเป็นค่าว่างล่ะคะ?”
“เฮ้ย! จริงด้วย!” ผมได้ยินเสียงหน้าตัวเองปริแตก

ลองมาวิเคราะห์ความผิดพลาดกัน
อาจวิเคราะห์จากตัวเลข Total ในเดือน Mar-20

ตัวเลขนี้มีอะไรเป็น Filter Context บ้าง?
OMonthYear = Mar-20
DMonthYear = Total
จากสูตรที่เขียนไว้
VAR Result =
IF(
DeliverMonthYear="Not Delivered",
NotDelivered,
Delivered
)
เมื่อ DMomthYear = Total เงื่อนไขเป็น FALSE จึงต้องคำนวณแบบ Delivered
ซึ่งสูตรของ Delivered คือ
VAR Delivered =
CALCULATE(
COUNTROWS(tOrder),
tDeliveryDate[DMonthYear]=DeliverMonthYear
)
เงื่อนไขของสูตรคือบรรทัดที่ 7 หรือ
tDeliveryDate[DMonthYear]=DeliverMonthYear
สูตรของ DeliveryMonthYear คือ
VAR DeliverMonthYear =
SELECTEDVALUE(tDMonthYear_wNotDelivered[DMonthYear])
เนื่องจากเงื่อนไขในตอนนี้เป็น Total
Total ไม่ใช่เดือนใด ๆ ผลลัพธ์ของ
SELECTEDVALUE(tDMonthYear_wNotDelivered[DMonthYear])
จึงเป็นค่าว่าง
ดังนั้น เงื่อนไขของเคสนี้คือ
OMonthYear = Mar-20
DMonthYear = ค่าว่าง (BLANK)
ผลลัพธ์ของ Mar-20 จึงเป็นค่าว่าง
แต่ถ้าสังเกต Oct-20
เงื่อนไขคือ
OMonthYear = Oct-20
DMonthYear = ค่าว่าง (BLANK)
ได้ผลลัพธ์เป็น 649 ซึ่งมีเท่ากับ DMonthYear เป็นค่าว่างหรือ Not Delivered นั่นเอง
แปลว่าถ้าต้องการคำนวณ Subtotal ให้คำนวณอีกแบบนึง
นั่นคือการนับแถวธรรมดา (COUNTROWS) ไม่ต้องเพิ่มเงื่อนไข หรือใช้สูตร
COUNTROWS(tOrder)
แล้วจะรู้ได้ยังไงว่าตอนไหนคำนวณแบบเดิม ตอนไหนคำนวณแบบ Subtotal?
ใช้ฟังก์ชัน HASONEVALUE เช็คว่าคอลัมน์นั้นมีเพียง 1 ค่าหรือไม่
ถ้าใช่ ให้คำนวณแบบเดิม
ถ้าไม่ใช่ ให้คำนวณแบบ Subtotal
ดังนั้นเคสนี้จึงต้องใช้ IF 2 ชั้น
ชั้นแรก เช็คว่ามีเพียง 1 ค่าหรือเปล่า
ชั้นที่สอง เช็คว่า DMonthYear เป็น Not Delivered หรือเปล่า
เปลี่ยนสูตร IF เป็น
VAR Result =
IF(
HASONEVALUE(tDMonthYear_wNotDelivered[DMonthYear]),
IF(
DeliverMonthYear="Not Delivered",
NotDelivered,
Delivered
),
COUNTROWS(tOrder)
)
หรือสูตรเต็ม ๆ ของ measure นี้คือ
#Order_wNotDelivered =
VAR DeliverMonthYear =
SELECTEDVALUE(tDMonthYear_wNotDelivered[DMonthYear])
VAR Delivered =
CALCULATE(
COUNTROWS(tOrder),
tDeliveryDate[DMonthYear]=DeliverMonthYear
)
VAR NotDelivered =
COUNTBLANK(tOrder[DeliveryDate])
VAR Result =
IF(
HASONEVALUE(tDMonthYear_wNotDelivered[DMonthYear]),
IF(
DeliverMonthYear="Not Delivered",
NotDelivered,
Delivered
),
COUNTROWS(tOrder)
)
RETURN Result
ได้ผลลัพธ์ตามต้องการ

ถ้าต้องการวิเคราะห์ข้อมูลเชิงลึก ก็สร้าง OMonthYear เป็น Slicer

จัดเรียงให้สวยงาม

จากภาพด้านบน แปลความหมายได้ว่า ในเดือน Oct-20 มีออเดอร์ทั้งหมด 5,959
ถูกส่งในเดือน Oct-20: 992
ถูกส่งในเดือน Nov-20: 2,143
ถูกส่งในเดือน Dec-20: 2,175
ยังไม่ได้ส่ง: 649
“หวาว! แบบนี้ใช่เลย ขอบคุณมาก ๆ เลยนะคะ” น้องนิจิโค้งขอบคุณ
“ยินดีครับ” ผมรู้สึกเหมือนออฟฟิศสว่าง มีออร่าบาง ๆ ที่ตัวเธอ ออร่าสีรุ้งเสียด้วย
“ขอถามเพิ่มได้ไหมคะ?”
“ได้ครับ” ผมรู้สึกว่าโลกนี้ช่างสดใส
“รีพอร์ตนี้ทำให้เรารู้สถานะล่าสุด หรือก็คือสถานะของวันนี้ แต่ถ้าอยากรู้สถานะ ณ สิ้นเดือนใด ๆ และมองย้อนหลังไปอีก 6 เดือนต้องทำยังไงคะ?”
รีพอร์ตที่น้องนิจิพูดถึงหน้าตาแบบนี้ครับ

คำถามดึงสติผมกลับมาทันที!
เป็นคำถามที่ดูเหมือนง่ายแต่ไม่ง่ายเลย ต้อง ‘เล่นแร่แปรธาตุ’ กันพอควร
รีพอร์ตนี้ทำยังไง?
เนื่องจาก ep.3 ยาวมากแล้ว เดี๋ยวมาคุยกันต่อใน ep.4 ครับ ^_^
สำหรับผู้ที่สนใจไฟล์ตัวอย่างที่ใช้ในบทความ ดาวน์โหลดได้จากลิงก์นี้ครับ
https://drive.google.com/file/d/1I1TFosw5zGdLbg-3MGT3vu6HA7GZQTyF/view?usp=sharing
บทความนี้แชร์ได้นะครับ ยิ่งมีคนอ่าน คนเขียนยิ่งมีกำลังใจครับ ^_^
ยอดเยี่ยม ขอบคุณค่ะ