Power BI ตอนที่ 21 : แสดงข้อมูลสรุปแบบ Top N + Others (ฉบับเทพเอ็กเซล)

วันก่อนผมได้เจอบทความของพี่บิว วิศวกรรีพอร์ต ที่อธิบายเรื่องวิธีการสรุปข้อมูลแบบ TopN+Others ใน ใน Power BI ซึ่งก็คือการแสดง Top N ที่สนใจ แต่สามารถแสดงข้อมูลอื่นๆ ที่อันดับมากกว่า Top N ให้รวมกันเป็น Others ได้ด้วย

ในบทความนั้นพี่บิวบอกว่าได้ดัดแปลงสูตร DAX จากบทความของฝรั่งชื่อ Gerhard Brueckl ที่เขียนแนวทางการทำการสรุปแบบนี้ไว้ ให้มีประสิทธิภาพมากขึ้น ซึ่งบทความของคุณ Gerhard และ พี่บิวก็มีเทคนิค DAX มากมายที่น่าสนใจ แนะนำให้ลองเข้าไปศึกษาดูครับ

หลังจากได้อ่านบทความพี่บิว ผมก็เลยคิดว่า ถ้าผมจะลองทำวิธีอื่น ที่ไม่ซ้ำกับทั้งสองท่านเลยจะทำได้มั้ยนะ? (ซึ่งก็แทบไม่เหลือวิธีอะไรมากแล้วล่ะ… 555)

หลังจากลองอยู่ซักพัก ผลลัพธ์ของมันก็คือบทความนี้นั่นเองครับ หวังว่า Concept ของผมจะทำให้เพื่อนๆ สามารถทำการสรุปแบบ Top N + Others ได้เช่นกันครับ

ไฟล์ประกอบ

ก่อนอื่นก็ต้องบอกว่า ไฟล์ตัวอย่างของผมก็ใช้ไฟล์เดิมที่ทำมาตลอดทั้ง Series นี้นั่นแหละครับ ซึ่งเราจะมาลองทำ ProductSubCategory ให้รองรับการทำ TopN +Others กัน

เพิ่มตารางใน Data Model

ขั้นตอนแรก หากเราต้องการสรุปข้อมูลให้มี ProductSubCategory ที่ชื่อว่า Others ให้โผล่มาใน Report ได้นั้น มันจะต้องมี Value นี้อยู่จริงๆ ซะก่อน ซึ่ง Data ปัจจุบันเรายังไม่มีตัวนี้เลย ดังนั้นเราจะต้องหาทางเพิ่ม Value ตัวนี้เข้าไปใน Field ชื่อ ProductSubCategory ครับ

หากจะเพิ่มไปในตารางเดิม มันจะทำให้ข้อมูลเพี้ยนมากเกินไป ดังนั้นเราจะเขียนสูตร DAX เพื่อสร้างตารางใหม่ขึ้นมา ซึ่งตารางนั้นจะมีแต่ ProductSubCategory แค่นั้นก็พอ แต่จะเพิ่ม Row นึงลงไปเป็นคำว่า Others ด้วย

วิธีเพิ่มแถวใน DAX นั้น เราสามารถใช้ฟังก์ชัน UNION ( <Table>, <Table>) มาทำการรวมตารางที่มีจำนวนคอลัมน์เท่ากันได้ ในขณะที่แถวที่เป็นคำว่า Others สามารถถูกสร้างด้วยฟังก์ชัน ROW ( <Name>, <Expression>) ได้ ซึ่งอันนี้เป็นเทคนิคที่คุณ Gerhard Brueckl ใช้ในบทความ Original เช่นกัน

สรุปแล้วผมใช้ DAX สร้าง New Table ด้วยสูตรนี้

ProductSubOther =
UNION ( ALL ( dProduct[ProductSubcategory] ), ROW ( "ProductSub", "Others" ) )

ซึ่งจะได้ตารางใหม่ที่มีหน้าตาดังนี้ ซึ่งจะ List รายการทุกอันของคอลัมน์ ProductSubCategory ทั้งหมดแบบไม่ซ้ำกันออกมา และต่อด้วยคำว่า Others

จากนั้นทำการผูก Relationship ของ Data Model ของตารางใหม่นี้เข้ากับตาราง dProduct เดิมซะ เพื่อที่ให้มันสามารถ Filter ตาราง dProduct ได้ แล้ว dProduct ก็จะไป Filter ตาราง fSales ต่อได้

พอทำแบบนี้ หากเราลาก ProductSubCategory จากตารางใหม่ลง Visual โดยให้คู่กับ Measure ที่ต้องการ มันก็จะสามารถแสดงผลลัพธ์ออกมาได้ โดยมีคำว่า Others โผล่มาด้วยแล้ว

แต่ถ้าหากเราลาก Measure ที่สนใจลงมา เช่น TotalRevenue จะทำให้ Others หายไป เพราะปกติหาก Measure เป็น Blank มันจะไม่แสดง item นั้นออกมาเลย

เริ่มเขียน DAX Measure ที่จำเป็นในการทำ Top N

ซึ่งไม่เป็นไร เดี๋ยวเราจะเขียน Measure อีกตัว ให้แสดง Rank ของข้อมูลแต่ละ item ออกมา โดย Rank ตาม TotalRevenue

RankSubCat =
RANKX ( ALLSELECTED ( ProductSubOther[ProductSubcategory] ), [TotalRevenue] )

หมายเหตุ : ในสูตรนี้ผมใช้ ALLSELECTED แทน ALL เผื่อว่ามีการ Filter จาก Visual อื่น จะได้แสดงเฉพาะผลที่เหลือจาก Filter นั้นๆ ไม่ปลดออกทั้งหมด

ซึ่งถ้าลากลงมาใน visual จะได้ดังนี้ ซึ่งสังเกตว่า Others จะโผล่มาแล้ว เพราะ Rank แล้วไม่ Blank (Rank แล้วเป็นตัวสุดท้าย เพราะไม่มียอด TotalRevenue เลย)

พอเรามี Measure ที่ Rank แต่ละ ProductSubcategory ได้แล้ว การสร้าง Measure เพื่อ ทำ TopN กับ Others ก็ไม่ยากแล้วล่ะ

Concept คือ ตัวเลขที่จะแสดงออกมานั้นมี 3 กรณี และมีค่าดังนี้

• ถ้า ProductSubcategory เป็น Others : เราจะแสดง TotalRevenue ของกลุ่ม Others ซึ่งผมจะตั้งชื่อว่า TotalRevenueOthers
• ถ้า Rank <=TopN (ซึ่งจะได้ตัว Total ด้วย) : ก็จะแสดงค่า TotalRevenue ของแต่ละ ProductSubcategory นั้นๆตามปกติ นั่นคือ TotalRevenue
• นอกนั้น: จะแสดงค่า Measure เป็น Blank ซะ

ซึ่งสมมติว่าค่า TopN ที่ต้องการคือ 5 ก่อนเพื่อความง่าย (เดี๋ยวจะทำให้ Dynamic ตอนหลัง)

เจ้า TotalRevenueOthers จะเขียนสูตรโดยใช้ CALCULATE เปลี่ยน Filter Context ได้ดังนี้

TotalRevenueOthers =
CALCULATE (
    [TotalRevenue],
    FILTER ( ALLSELECTED ( ProductSubOther[ProductSubcategory] ), [RankSubCat] > 5 )
)

หากลองลากมาลง Visual จะได้เลขเดียวกันหมดสำหรับทุก Rank เพราะเราใช้ CALCULATE เปลี่ยน Filter Context ไปแล้วนั่นเอง

ทีนี้เรามาเขียนเงื่อนไขสำหรับ Measure ที่เราจะนำมาใส่ Visual จริงๆ ให้ครบ 3 เงื่อนไขที่เราคิดไว้ ซึ่งจะใช้ IF ซ้อนกันก็ได้ แต่ผมใช้ SWITCH จะง่ายกว่า ดังนี้

TotalRevenueFinal =
VAR CurrentSubCat =
    SELECTEDVALUE ( ProductSubOther[ProductSubcategory] )
RETURN
    SWITCH (
        TRUE (),
        CurrentSubCat = "Others", [TotalRevenueOthers],
        [RankSubCat] <= 5, [TotalRevenue],
        BLANK ()
    )

พอลากมาลง Visual จะได้ดังนี้ ซึ่งจะเห็นว่าผลลัพธ์ออกมาถูกต้องทุกกรณีเลย

แปลว่าถ้าเราทำให้เหลือแค่ TotalRevenueFinal ก็จะเหลือเฉพาะ TopN กับ Others เท่านั้น

ทำ TopN ให้ Dynamic

เดิมทีเราใช้การใส่เลข 5 เป็นค่าคงที่สำหรับ TopN แต่คราวนี้เราจะให้คนใช้งานกำหนด N ได้ตามต้องการเลย

ก่อนอื่นให้เราสร้าง DAX New Table ใหม่ ให้มีเลข 1-N ตัวตามต้องการ ซึ่งผมใช้ GENERATESERIES ตั้งแต่ 1- จำนวน ProductSubcategory มาช่วยดังนี้

TopNSubCatTable =
GENERATESERIES ( 1, DISTINCTCOUNT ( dProduct[ProductSubcategory] ) )

ซึ่งตารางใหม่นี้เราไม่ต้องไปผูก Relationship ใดๆ ทั้งสิ้น

จากนั้นให้สร้าง Slicer ที่ให้เลือกค่าจาก Field ของตารางใหม่ ซึ่งผมใช้แบบ Less than or Equal to เพื่อความเท่ (ให้เลื่อนได้ว่าจะเอา TopN คือเท่าไหร่)

จากนั้นเราค่อยเขียน Measure เพื่อเอาค่าจาก Slicer โดยเอาค่า MAX มาก็ได้ เช่น

SelectedN= MAX(TopNSubCatTable[Value])

จากนั้นค่อนเอา SelectedN ไปแทนเลข 5 เดิมที่เราทำไว้ใน 2 สูตรเดิม ดังนั้นจะได้สูตรว่า

TotalRevenueFinal =
VAR CurrentSubCat =
    SELECTEDVALUE ( ProductSubOther[ProductSubcategory] )
RETURN
    SWITCH (
        TRUE (),
        CurrentSubCat = "Others", [TotalRevenueOthers],
        [RankSubCat] <= [SelectedN], [TotalRevenue],
        BLANK ()
    )

TotalRevenueOthers =
CALCULATE (
    [TotalRevenue],
    FILTER (
        ALLSELECTED ( ProductSubOther[ProductSubcategory] ),
        [RankSubCat] > [SelectedN]
    )
)

ซึ่งจะทำให้ตารางเปลี่ยน N ได้ตามต้องการ เช่น ถ้า N เป็น 10 จะได้ดังนี้

จัดการเรื่องการเรียงลำดับ

อย่างไรก็ตาม หากอยากจะเรียงตามยอดมากไปน้อย แต่อยากเอา Others ไว้ท้ายสุดด้วย จะต้องสร้าง Measure อีกตัวมาช่วยเรียง ซึ่งใช้เงื่อนไขเหมือน Measure ที่แล้วเลย แต่แสดงผลลัพธ์เป็น Rank ที่ถูกต้องแทน เช่น ถ้าเป็น Others ให้เอา N ไปบวก 1 เลย ดังนี้

RankSubcatwithOthers =
VAR CurrentSubCat =
    SELECTEDVALUE ( ProductSubOther[ProductSubcategory] )
RETURN
    SWITCH (
        TRUE (),
        CurrentSubCat = "Others", [SelectedN] + 1,
        [RankSubCat] <= [SelectedN], [RankSubCat],
        BLANK ()
    )

จากนั้นกดเรียงคอลัมน์ RankSubcatwithOthers ก็จได้การเรียงที่ถูกต้อง

ถ้าอยากแสดงตัวภายใต้ Others จะต้องแก้สูตรเพิ่ม

วิธีของผมที่ทำมาตอนแรก ยังไม่เหมาะกับการแสดง item ภายใต้ Others เพราะถ้าลาก ProductSubcategory จากตาราง Product มาซ้อนกัน ภายใต้ใน Others มันจะแสดง TotalRevenueFinal เป็น Blank หมดเลย

ถ้าจะแก้ไขก็ต้องสร้างสูตรเพิ่มอีกพอสมควรเลย เท่าที่ผมลองมันก็พอทำให้ผลลัพธ์ออกมาได้ แต่วิธีอาจจะดูแปลกๆ หน่อยครับ ถ้าจะไม่ใช้วิธีซ้ำกับของคุณ Gerhard หรือของพี่บิว

ก่อนอื่นเราทำการสร้าง Measure TotalRevenue อีกตัวที่ทำการปลด Filter จากตาราง ProductSub ที่สร้างใหม่ออกซะ เพื่อให้สามารถแสดงค่า TotalRevenue ของตัวลูกได้โดยไม่ Blank

TotalRevenueAllSubCat =
CALCULATE (
    [TotalRevenue],
    ALLSELECTED ( ProductSubOther[ProductSubcategory] )
)

ทีนี้เราก็ทำRank ขึ้นมาใหม่อีกตัว โดยใช้ Measure ตัวใหม่นี้ของเราในการ Rank โดยที่จะ Rank ใน ProductSub ของตาราง Product Original ของเรา

RankSubcatNoOthers =
RANKX ( ALLSELECTED ( dProduct[ProductSubcategory] ), [TotalRevenueAllSubCat] )

ซึ่งจะทำให้เห็นผลลัพธ์แบบนี้

จากนั้นเราต้องหาวิธีแยกว่า เรากำลังดู Level ที่มี Others หรือ ดู Level ลูกอยู่ ซึ่งวิธีนึงที่ทำได้คือใช้ ISFILTERED ดังนี้

IsChild = ISFILTERED(dProduct[ProductSubcategory])

ซึ่งจะได้ผลออกมาดังนี้

ทีนี้เราจะทำ RankFinal ขึ้นมา โดยที่ตัวลูกของ Other จะต้องเอามาเฉพาะ RankSubcatNoOthers >=RankSubcatwithOthers เท่านั้น นอกนั้นจะต้องไม่แสดงขึ้นมา

ดังนั้นจะเขียนสูตรว่า

RankFinal =
SWITCH (
    TRUE (),
    [IsChild] = FALSE (), [RankSubcatwithOthers],
    [IsChild] = TRUE ()
        && SELECTEDVALUE ( ProductSubOther[ProductSubcategory] ) = "Others"
        && [RankSubcatNoOthers] >= [RankSubcatwithOthers], [RankSubcatNoOthers],
    BLANK ()
)

และก็แก้ไข TotalRevenueFinal ใหม่ ให้ครอบคลุมขึ้นดังนี้

หลักการคือ เลือกเลขมาใช้ให้ถูกตัว ถูกเงื่อนไขเสมอ

TotalRevenueFinal =
VAR CurrentSubCat =
    SELECTEDVALUE ( ProductSubOther[ProductSubcategory] )
RETURN
    SWITCH (
        TRUE (),
        CurrentSubCat = "Others"
            && [IsChild] = FALSE, [TotalRevenueOthers],
        ISBLANK ( [RankFinal] ), BLANK (),
        [RankFinal] > [SelectedN], [TotalRevenueAllSubCat],
        [TotalRevenue]
    )

จากนั้นทำให้เหลือแค่ TotalRevenueFinal กับ RankFinal

ซึ่งจะเห็นว่า สามารถแสดงรายละเอียดใน Others ได้แล้วล่ะ (ยุ่งยากจัง 555)

ถ้าเราปรับ N เล่น ก็จะยังทำงานได้อยู่เช่นเดิมครับ

และแน่นอนว่าใช้กับ Visual ไหนก็ได้ครับ

เจาะดูตัวลูกได้ด้วยการ Expand อีก Level ออกมา

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

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