“อยากสร้างรีพอร์ตแสดง Top 5 สินค้าขายดี และแสดงสินค้าที่เหลือเป็น Others ต้องทำยังไงคะ?”

รีพอร์ตที่ว่าหน้าตาประมาณนี้ครับ

รีพอร์ตนี้สร้างไม่ยากถ้าใช้ Excel
แต่สร้างยากมากถ้าใช้ Power BI

ทำไม?

เพราะ Power BI ไม่สามารถเพิ่มบรรทัด (Row) ที่ไม่มีในข้อมูล
ใช่ครับ ผมหมายถึงบรรทัดที่เป็น Others

รายชื่อสินค้า 5 อันดับแรกสามารถดึงจากคอลัมน์ชื่อสินค้าได้ แต่ Others ไม่ใช่ชื่อสินค้า
ถ้าสินค้ามี 100 ตัว Others คือผลรวมของ 95 ตัวที่เหลือ

ในทางปฏิบัติอาจมี slicer เพิ่มเติมเงื่อนไขอื่น เช่น ปี ไตรมาส ประเภทสินค้า ยิ่งเพิ่มความซับซ้อน
ในแง่ของ DAX แล้ว สูตรนี้แทบเป็นไปไม่ได้

แต่… ต้องทำ!

แล้วจะทำยังไง?

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

โชคดีมาเจอเว็บของคุณ Gerhard Brueckl (น่าจะอ่านว่า แกร์ฮาร์ต บรึคล์ จากนี้ขอเรียกว่าคุณแกร์ฮาร์ต) เขียนบทความเรื่องนี้แบบเป๊ะ ๆ
บทความที่พูดถึงคือ บทความนี้

วิธีของคุณแกร์ฮาร์ตบอกคำเดียวว่า โคตรเจ๋ง!

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

เนื้อหาว่าเจ๋งแล้ว คอมเมนต์ใต้บทความก็เจ๋งไม่แพ้กัน ผมนั่งไล่อ่านคอมเมนต์ถึงได้รู้ว่าโลกกว้างจริง ๆ ได้ความรู้เยอะมาก

ถ้าใครยังไม่ได้อ่านบทความของคุณแกร์ฮาร์ด ผมแนะนำให้อ่านก่อน
แต่ถ้าใครอ่านแล้ว ผมอยากชวนคุยและแชร์มุมมองส่วนตัวนิดนึง …

หลังจากแกะสูตรคุณแกร์ฮาร์ตและทดลองอยู่ค่อนวัน

Top Measure ProductSubCategory = 
/* get the items for which we want to calculate TopN + Others */
VAR Items = SELECTCOLUMNS(ALL(Subcategory_wOthers), "RankItem", Subcategory_wOthers[SubcategoryName_wOthers])
/* add a measure that we use for ranking */
VAR ItemsWithValue = ADDCOLUMNS(Items, "RankMeasure", CALCULATE([Selected Measure], ALL(ProductSubcategory)))
/* add a column with the rank of the measure within the items */
VAR ItemsWithRank = ADDCOLUMNS(ItemsWithValue, "Rank", RANKX(ItemsWithValue, [RankMeasure], [RankMeasure], DESC, Dense))
/* calculate whether the item is a Top-item or belongs to Others */
VAR ItemsWithTop = ADDCOLUMNS(ItemsWithRank, "TopOrOthers", IF([Rank] <= [Selected TopN], [RankItem], "Others"))
/* select the final items for which the value is calculated */
VAR ItemsFinal = SELECTCOLUMNS( /* we can only select a single column to be used with TREATAS() */
    FILTER(
        ItemsWithTop, 
        /* need to obey current filters on _wOthers table. e.g. after Drill-Down */
        CONTAINSROW(VALUES(Subcategory_wOthers[SubcategoryName_wOthers]), [TopOrOthers]) 
        /* need to obey current filters on base table */
        && CONTAINSROW(VALUES(ProductSubcategory[SubcategoryName]), [RankItem])), 
    "TopN_Others", [RankItem])
RETURN 
    CALCULATE(
        [Selected Measure],
        TREATAS(ItemsFinal, Subcategory_wOthers[SubcategoryName_wOthers]))

พบว่าสามารถปรับให้สั้นลง และทำให้สูตรมีประสิทธิภาพมากขึ้นได้ นั่นคือ

1 ลดบางขั้นตอน

จากโค้ดเดิมบรรทัดที่ 3-7 ที่สร้าง VAR ชื่อ Items, ItemsWithValue, ItemsWithRank สามารถรวมเป็นขั้นตอนเดียวโดยใช้สูตร

VAR ItemsWithRank = 
    ADDCOLUMNS(
        ALL(Subcategory_wOthers[SubcategoryName_wOthers]), 
        "Rank", 
        RANKX(
            ALL(Subcategory_wOthers[SubcategoryName_wOthers]),
            CALCULATE(
                [Selected Measure],
                ALL(ProductSubcategory[SubcategoryName])
            )
        )
    )

2 ใช้ IN แทน CONTAINSROW

ฟังก์ชัน CONTAINSROW สามารถใช้ IN แทนได้ เช่น เปลี่ยนจาก

CONTAINSROW(VALUES(Subcategory_wOthers[SubcategoryName_wOthers]), [TopOrOthers]) 

เป็น

[TopOrOthers] IN VALUES(Subcategory_wOthers[SubcategoryName_wOthers])

ผลลัพธ์ที่ได้เหมือนกัน แต่แบบหลังเขียนง่ายกว่า เข้าใจง่ายกว่า

อ้างอิงการใช้ IN แทน CONTAINSROW จาก บทความนี้

3 เลี่ยงการใช้ฟังก์ชัน TREATAS

ตามความเข้าใจของผม ฟังก์ชัน TREATAS ทำให้การประมวลผลช้า

(อ้างอิงข้อเสียของการใช้ TREATAS จากบทความนี้ )

จากโค้ดบรรทัดที่ 13-18 (โค้ดเดิม) ถ้าไม่ใช้ TREATAS ก็ไม่จำเป็นต้องใช้ SELECTCOLUMNS หรือปรับ ItemsFinal เป็นแบบนี้

VAR ItemsFinal = 
    FILTER(
        ItemsWithTop, 
        [TopOrOthers] IN VALUES(Subcategory_wOthers[SubcategoryName_wOthers])
        && [SubcategoryName_wOthers] IN VALUES(ProductSubcategory[SubcategoryName])
    )

พอปรับ ItemsFinal เป็นแบบนี้ ก็ใช้ SUMX แทน CALCULATE ได้เลย หรือปรับสูตรหลังคำว่า RETURN เป็น

SUMX(ItemsFinal,[Selected Measure])

มีคอมเมนต์นึงจากคุณ AkhilAshok ก็คิดคล้าย ๆ กัน

จริง ๆ แล้วคุณแกร์ฮาร์ตอธิบายเหตุผลที่ไม่ใช้ SUMX (ใน RETURN) ว่า [Select Measure] อาจเป็นรูปแบบอื่น เช่น DISTINCTCOUNT, AVERAGE
แต่ในทางปฏิบัติแล้วโอกาสเจอน้อยมาก ส่วนใหญ่มักเป็นการ SUM เสมอ

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

TopMeasureProdSubCat = 
/* add a column with the rank of the measure within the items
need to apply ALL in CALCULATE to show details of Others */
VAR ItemsWithRank = 
    ADDCOLUMNS(
        ALL(Subcategory_wOthers[SubcategoryName_wOthers]), 
        "Rank", 
        RANKX(
            ALL(Subcategory_wOthers[SubcategoryName_wOthers]),
            CALCULATE(
                [Selected Measure],
                ALL(ProductSubcategory[SubcategoryName])
            )
        )
    )
// calculate whether the item is a Top-item or belongs to Others
VAR ItemsWithTop = 
    ADDCOLUMNS(
        ItemsWithRank, 
        "TopOrOthers", 
        IF([Rank] <= [Selected TopN], [SubcategoryName_wOthers], "Others")
    )
// select the final items for which the value is calculated
VAR ItemsFinal = 
    FILTER(
        ItemsWithTop, 
        [TopOrOthers] IN VALUES(Subcategory_wOthers[SubcategoryName_wOthers])
        && [SubcategoryName_wOthers] IN VALUES(ProductSubcategory[SubcategoryName])
    )
RETURN SUMX(ItemsFinal,[Selected Measure])

(เพื่อให้ไม่สับสน ขอสร้างเป็น Measure ใหม่ ใช้ชื่อว่า TopMeasureProdSubCat)

ได้ผลลัพธ์เหมือนสูตรเดิมของคุณแกร์ฮาร์ตทุกประการ
การใช้ SUMX ทำให้ไม่ต้องใช้ TREATAS และ (น่าจะ) ประมวลผลเร็วขึ้น

สูตรนี้ทำงานยังไง?

เป็นคำถามที่ดีมาก ขออธิบายแบบนี้ละกัน ลองนึกภาพตามนะครับ

การคำนวณทุกครั้งจะสร้าง “ตารางเสมือน” ขึ้นมา ไม่ว่าสินค้านั้นจะอยู่อันดับใด
ตารางเสมือนที่ว่า หน้าตาประมาณนี้

ถ้าสินค้านั้นเป็นอันดับ 1 ก็จะดึงค่าของอันดับหนึ่ง (Rank 1) ไป

ถ้าเป็นอันดับ 2 ก็จะดึงค่าของอันดับสอง (Rank 2) ไป

แต่ถ้าไม่อยู่ในอันดับ Top N ก็จะรวมค่าที่ระบุเป็น Others เข้าด้วยกัน

นี่จึงเป็นเหตุผลว่า ทำไมถึงใช้ SUMX เป็นสูตรสุดท้าย ^__^

4 เรียงลำดับให้ Others อยู่ล่างสุดไม่ได้

นี่คือปัญหาใหญ่ เพราะในทางปฏิบัติ มักต้องการให้ Others อยู่ล่างสุดเสมอ แต่สูตรของคุณแกร์ฮาร์ตทำแบบนั้นไม่ได้

เช่น ถ้าเลือกเป็น Top 3
Others มีค่าน้อยสุด ก็แสดงผลด้านล่าง ไม่มีปัญหา

แต่ถ้าเลือกเป็น Top 5
Others ไม่ใช่ค่าน้อยสุด จะมีปัญหาทันที

งั้นทำไงดี?

โชคดีมีคอมเมนต์จากคุณเดวิด (David Cadman on 2019-10-30 at 16:09)

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

IF( 
    HASONEVALUE(Subcategory_wOthers[SubcategoryName_wOthers])
    && SUMX(ItemsFinal,[Selected Measure])>0,
    FIRSTNONBLANK(
        SELECTCOLUMNS(ItemsFinal,"Rank",[Rank]),
        TRUE()
    )
)

หรือสูตรเต็ม ๆของ Measure ตัวใหม่นี้ (ตั้งชื่อว่า ItemRank) คือ

ItemRank = 
// To sort [TopMeasureProdSubCat] and always put Others last
VAR ItemsWithRank = 
    ADDCOLUMNS(
        ALL(Subcategory_wOthers[SubcategoryName_wOthers]), 
        "Rank", 
        RANKX(
            ALL(Subcategory_wOthers[SubcategoryName_wOthers]),
            CALCULATE([Selected Measure],ALL(ProductSubcategory[SubcategoryName]))
        )
    )
VAR ItemsWithTop = 
    ADDCOLUMNS(
        ItemsWithRank, 
        "TopOrOthers", 
        IF([Rank] <= [Selected TopN], [SubcategoryName_wOthers], "Others")
    )
VAR ItemsFinal = 
    FILTER(
        ItemsWithTop, 
        [TopOrOthers] IN VALUES(Subcategory_wOthers[SubcategoryName_wOthers])
        && [SubcategoryName_wOthers] IN VALUES(ProductSubcategory[SubcategoryName])
    )
RETURN 
IF( 
    HASONEVALUE(Subcategory_wOthers[SubcategoryName_wOthers])
    && SUMX(ItemsFinal,[Selected Measure])>0,
    FIRSTNONBLANK(
        SELECTCOLUMNS(ItemsFinal,"Rank",[Rank]),
        TRUE()
    )
)

แล้วเอา Measure นี้ (ItemRank) ไปใช้ยังไง?

หลักการคือ ให้ Sort ข้อมูลตาม ItemRank แบบ ascending

เช่น ถ้าสร้าง Table ก็จัดองค์ประกอบแบบนี้

จากนั้นปรับให้ Sort by ItemRank แบบ ascending

มาดูผลลัพธ์กัน

Others อยู่ล่างสุดทุกครั้งใช่ไหมครับ ^__^

แล้วถ้าเป็นกราฟล่ะ?

ถ้าเป็นกราฟจะทำได้เนียนกว่า เพราะมีเทคนิคที่ไม่ต้องแสดงค่า ItemRank ในกราฟ แต่สามารถใช้ ItemRank เรียงข้อมูลได้

ทำยังไง?

นำไป ItemRank ไว้ที่ Tooltips
ส่วน TopMeasureProdSubCat ก็ใช้เป็น Value แบบปกติ
หรือจัดองค์ประกอบแบบนี้

จากนั้นก็ปรับให้ Sort by ItemRank แบบ ascending

มาดูผลลัพธ์กัน

แล้วถ้าอยากรู้ว่า Others มีอะไรบ้างล่ะ?

ทำได้ตรง ๆ เลย

เช่น ถ้าต้องการแสดงเป็นตาราง ให้ใช้ Matrix

คอลัมน์ที่มีคำว่า Others คือ SubcategoryName_wOthers ให้นำไปไว้ที่ Rows อันแรกสุด
คอลัมน์ที่ไม่มีคำว่า Others (SubcategoryName, คอลัมน์ดั้งเดิม) ให้นำไปไว้ที่ Rows อันที่สอง จุดประสงค์เพื่อต้องการแสดงว่า Others มีอะไรบ้าง

ส่วน ItemRank และ TopMeasureProdSubCat ยังคงใช้เป็น Value (เหมือนตอนทำ Table)

จากนั้นก็ปรับให้ Sort by ItemRank แบบ ascending (เหมือนเดิม)

หน้าตาที่ได้คือแบบนี้ จะพบว่ามีไอคอนเครื่องหมายบวก

ถ้าคลิกเครื่องหมายบวกตรง Others ออกมา จะกลายเป็นแบบนี้

ทำไมถึงแสดงรายละเอียดได้ใน Others ได้ล่ะ?

ทริกอยู่ที่โค้ดบรรทัดที่ 12 และ 28

TopMeasureProdSubCat = 
/* add a column with the rank of the measure within the items
need to apply ALL in CALCULATE to show details of Others */
VAR ItemsWithRank = 
    ADDCOLUMNS(
        ALL(Subcategory_wOthers[SubcategoryName_wOthers]), 
        "Rank", 
        RANKX(
            ALL(Subcategory_wOthers[SubcategoryName_wOthers]),
            CALCULATE(
                [Selected Measure],
                ALL(ProductSubcategory[SubcategoryName])
            )
        )
    )
// calculate whether the item is a Top-item or belongs to Others
VAR ItemsWithTop = 
    ADDCOLUMNS(
        ItemsWithRank, 
        "TopOrOthers", 
        IF([Rank] <= [Selected TopN], [SubcategoryName_wOthers], "Others")
    )
// select the final items for which the value is calculated
VAR ItemsFinal = 
    FILTER(
        ItemsWithTop, 
        [TopOrOthers] IN VALUES(Subcategory_wOthers[SubcategoryName_wOthers])
        && [SubcategoryName_wOthers] IN VALUES(ProductSubcategory[SubcategoryName])
    )
RETURN SUMX(ItemsFinal,[Selected Measure])

โค้ดบรรทัด 12 ใช้ ALL ยกเลิก filter context ของ ProductSubcategory[SubcategoryName])
(ไม่ใช่ยกเลิก filter context ของ Subcategory_wOthers[SubcategoryName_wOthers])

เพื่อให้ CALCULATE คำนวณ Others เป็นผลรวมของที่เหลือจริง ๆ
(ไม่งั้นจะคำนวณแยกตาม SubcategoryName_wOthers และ Others จะกลายเป็นศูนย์)

ส่วนโค้ดบรรทัดที่ 28 หรือโค้ดนี้

[SubcategoryName_wOthers] IN VALUES(ProductSubcategory[SubcategoryName])

ใช้เพื่อรับ filter context จาก ProductSubcategory[SubcategoryName] (ที่ใช้เป็น Row อันดับสอง)

ทริกนี้บอกเลย สุด!
อ้อ.. ผมไม่ได้คิดเองนะครับ คุณแกร์ฮาร์ตเป็นคนคิดครับ ^__^

ถ้าเป็นกราฟก็ใช้เทคนิดเดียวกัน
ใช้ Axis 2 คอลัมน์ นั่นคือ SubcategoryName_wOthers และ SubcategoryName เพื่อให้สามารถ Drill down ได้

มาดูผลลัพธ์การ Drill down แบบนี้กัน

หรือถ้านำมาสร้างกราฟวงกลมก็ใช้หลักการเดียวกัน

ถ้าใช้ทั้ง Interaction และ Drill Down ร่วมกัน มันก็จะเจ๋งมาก แบบนี้

ว้าว! เจ๋งมากเลยใช่ไหมครับ ^__^

สำหรับใครที่สนใจไฟล์ตัวอย่าง สามารถดาวน์โหลดเพื่อศึกษาเพิ่มเติมได้จาก ลิงก์นี้
ไฟล์นี้ผมอ้างอิงมาจากไฟล์ที่คุณแกร์ฮาร์ตแชร์ไว้ในบทความ แล้วนำมาปรับปรุงเพิ่มเติม

เป็นยังไงกันบ้าง บทความนี้เทคนิคเยอะมากเลยเนอะ

ถ้าใครอ่านแล้วงงไม่ต้องกังวล เพราะตอนผมอ่านบทความของคุณแกร์ฮาร์ตก็งงเหมือนกัน

ไม่สิ เรียกว่า โคตรงง จะเห็นภาพกว่า!

ใช้เวลาหลายชั่วโมงแกะสูตร ทดลองปรับเปลี่ยนนู่นนั่นนี่จนเข้าใจ ถึงได้รู้ว่าสูตรนี้เจ๋งจริง ๆ บทความของคุณแกร์ฮาร์ตทำให้ผมเข้าใจ DAX ขึ้นเยอะมาก

แต่รู้คนเดียวไม่มีประโยชน์ จึงเป็นที่มาของบทความนี้ครับ ^__^

Dear Gerhard,

Your DAX is awesome!

I love your method. It solved a problem in another angle which I couldn’t think about. It’s straightforward and crystal clear. Thanks to share this article. You enlightened my DAX world!

Credit: https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/