สูตร DAX สรุปออเดอร์ที่ยังไม่ได้ส่ง [DAX Order Date Delivery Date ep.3]

“งั้นถ้าอยากสรุปจำนวนออเดอร์ที่ส่งแล้วและยังไม่ส่ง ควรทำยังไงดีคะ?” น้องนิจิคนงามถามต่อ
“ต้องสร้างตาราง และ 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

บทความนี้แชร์ได้นะครับ ยิ่งมีคนอ่าน คนเขียนยิ่งมีกำลังใจครับ ^_^