Thep Excel
RANKXจัดอันดับด้วย Iterator Pattern (รองรับการจัดการอันดับเสมอ)
RANKX เป็น iterator function ที่ iterate ทุกแถวใน table เพื่อประเมิน expression แล้วคืนอันดับของค่าใน current context เมื่อเทียบกับค่าทั้งหมด สามารถกำหนดทิศทางการเรียง (ASC/DESC) และวิธีจัดการอันดับเสมอ (Skip/Dense) ใช้บ่อยกับ ALL/ALLSELECTED เพื่อควบคุมชุดข้อมูลที่เปรียบเทียบ เหมาะสำหรับการจัดอันดับสินค้า ลูกค้า ภูมิภาค และ Top/Bottom analysis
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| table | table | ตารางที่ใช้ iterate เพื่อประเมิน expression – RANKX จะวนทุกแถวในตารางนี้ มักใช้ร่วมกับ ALL, ALLSELECTED, ALLEXCEPT เพื่อควบคุม filter context ให้เห็นรายการครบตามต้องการ | |
| expression | expression | นิพจน์หรือ measure ที่ใช้เป็นค่าในการจัดอันดับ – จะถูกประเมินในแต่ละ row ของ table ค่า BLANK ถือเป็น 0 (ถ้าเป็นตัวเลข) หรือ empty text (ถ้าเป็นข้อความ) | |
| [value]ไม่บังคับ | any | ใช้ค่าจาก expression ใน current context | ค่าที่ต้องการหาอันดับแบบระบุชัด – ถ้าไม่ระบุจะใช้ค่าของ expression ใน current context แทน ใช้เมื่อต้องการหาอันดับของค่าที่กำหนดเองโดยไม่ขึ้นกับบริบทปัจจุบัน |
| [order]ไม่บังคับ | number | 0 (DESC – มากไปน้อย) | ทิศทางการเรียงลำดับ: 0 หรือ FALSE = จากมากไปน้อย (DESC, ค่าสูงสุดได้อันดับ 1) / 1 หรือ TRUE = จากน้อยไปมาก (ASC, ค่าต่ำสุดได้อันดับ 1) |
| [ties]ไม่บังคับ | enum | Skip | วิธีจัดการอันดับเสมอ: Skip = ข้ามลำดับเมื่อเสมอ (1, 2, 2, 2, 5) / Dense = ไม่ข้ามลำดับ (1, 2, 2, 2, 3) |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
RANKX เป็นฟังก์ชัน iterator function ที่ใช้จัดอันดับรายการในตารางตามค่าที่คำนวณจาก expression โดยจะ iterate แต่ละ row ใน table แล้วประเมิน expression สำหรับทุกแถว จากนั้นเปรียบเทียบค่าใน current context กับค่าทั้งหมดที่คำนวณได้ เพื่อคืนอันดับของแถว/รายการนั้น
.
สิ่งสำคัญที่ต้องเข้าใจ: RANKX ต้องการ table ที่กว้างพอเพื่อให้เห็นรายการทั้งหมดสำหรับเปรียบเทียบ มักใช้ร่วมกับ ALL, ALLSELECTED หรือ ALLEXCEPT มิฉะนั้น RANKX อาจเห็นแค่รายการในบริบทปัจจุบันและให้อันดับ 1 เกือบทุกแถว 😅
.
Iterator Pattern ใน RANKX: RANKX ทำงานใน row context โดย iterate ทุกแถวใน table argument และประเมิน expression ที่แต่ละแถว จึงสามารถใช้คอลัมน์ได้โดยตรง (เช่น Sales[Amount]) หรือ measure ที่มี context transition ผลลัพธ์คือชุดค่าทั้งหมดที่นำมาจัดอันดับ
.
การจัดการอันดับเสมอ (Ties): RANKX รองรับ 2 วิธีจัดการอันดับเสมอครับ
.
Skip (default) จะข้ามลำดับเมื่อมีอันดับเสมอ เช่น 1, 2, 2, 2, 5 ส่วน Dense จะไม่ข้ามลำดับ เช่น 1, 2, 2, 2, 3
.
⚠️ ข้อควรระวัง: RANKX อาจให้ผลลัพธ์ไม่คาดคิดกับ Decimal Number data type เนื่องจากปัญหาความแม่นยำของ floating-point (IEEE 754) แนะนำให้ใช้ Fixed Decimal Number แทน หรือใช้ ROUND function ก่อนคำนวณอันดับครับ 💡
How it works
จัดอันดับสินค้า/ลูกค้าตามยอดขาย
สร้าง calculated column หรือ measure เพื่อแสดงอันดับของสินค้า ลูกค้า หรือภูมิภาคตามยอดขาย ใช้ ALL เพื่อให้เห็นรายการทั้งหมด
จัดอันดับภายในกลุ่ม (Ranking within Groups)
จัดอันดับสินค้าภายในหมวดหมู่เดียวกัน หรือพนักงานภายในแผนกเดียวกัน ใช้ ALLEXCEPT เพื่อคงตัวกรองของกลุ่มไว้
Top N Analysis
หา Top 10 สินค้าขายดี หรือ Bottom 5 สาขาขายแย่ ใช้ร่วมกับ TOPN หรือ filter ตาม rank <= N
Dense Ranking สำหรับคะแนนสอบ
จัดอันดับนักเรียนตามคะแนนสอบแบบ Dense (ไม่ข้ามเลข) เหมาะกับระบบคะแนนที่ต้องการให้อันดับต่อเนื่องกัน
Examples
ตัวอย่างที่ 1: อันดับสินค้าตามยอดขาย (พื้นฐาน)Product Rank = RANKX( ALL(Products[ProductKey]), [Total Sales] )
ALL(Products[ProductKey]) ทำให้ RANKX เห็นสินค้าทั้งหมด ไม่ถูกจำกัดโดย filter context ปัจจุบัน
[Total Sales] คือ measure ที่ใช้เปรียบเทียบ ค่าเริ่มต้นเป็น DESC (มากไปน้อย) และ Skip ties
เทคนิคนี้เป็น pattern หลักที่ใช้บ่อยมากครับ เพราะถ้าไม่ใส่ ALL สินค้าแต่ละตัวจะเห็นแค่ตัวเอง แล้วได้อันดับ 1 หมด 😅
ตัวอย่างที่ 2: Dense Rank (ไม่ข้ามเลขเมื่อเสมอ)Product Dense Rank = RANKX( ALL(Products[ProductKey]), [Total Sales], , DESC, Dense )
ties argument ระบุเป็น Dense ทำให้อันดับถัดจากอันดับเสมอไม่ข้ามเลข
ถ้ามี 3 สินค้าอันดับ 2 อันดับถัดไปจะเป็น 3 (ไม่ใช่ 5) เหมาะกับระบบที่ต้องการอันดับต่อเนื่อง
ส่วนตัวผมชอบใช้ Dense สำหรับระบบคะแนนหรือรีวิว เพราะดูเข้าใจง่ายกว่าครับ 😎
ตัวอย่างที่ 3: จัดอันดับภายในหมวดสินค้าCategory Rank = RANKX( ALLEXCEPT( Products, Products[Category] ), [Total Sales] )
ALLEXCEPT(Products, Products[Category]) ล้าง filter ทั้งหมดจาก Products ยกเว้น Category ทำให้การจัดอันดับเกิดขึ้นภายในกลุ่มหมวดหมู่เดียวกัน
แต่ละหมวดจะมีสินค้าอันดับ 1 ของตัวเอง นี่คือเทคนิคที่ใช้บ่อยมากสำหรับ Report แบบแบ่งตามกลุ่มครับ 💡
ตัวอย่างที่ 4: Ascending Order (น้อยไปมาก)Customer Low Rank = RANKX( ALL(Customers[CustomerKey]), [Total Sales], , ASC )
order argument ระบุเป็น ASC (หรือ 1 หรือ TRUE) ทำให้ค่าต่ำสุดได้อันดับ 1
ใช้สำหรับหา Bottom performers หรือลูกค้าที่ต้องการความช่วยเหลือ เทคนิคนี้ดีสำหรับระบุกลุ่มเสี่ยงครับ 📊
ตัวอย่างที่ 5: ใช้กับ VAR และ CALCULATE (Advanced)Sales Rank With Context = VAR CurrentSales = [Total Sales] VAR AllProducts = ALL(Products) VAR ProductRank = RANKX( AllProducts, CALCULATE([Total Sales]), Curre…
ใช้ VAR เก็บ CurrentSales และ AllProducts ไว้ก่อน ทำให้โค้ดอ่านง่ายขึ้นและ debug ได้สะดวก
value argument ใช้ CurrentSales (ค่าใน current context) เปรียบเทียบกับค่าทั้งหมดที่คำนวณจาก CALCULATE([Total Sales]) ในแต่ละ product
Pattern นี้ผมแนะนำให้ใช้สำหรับ measure ที่ซับซ้อน เพราะช่วยให้เข้าใจ logic ได้ดีขึ้นเยอะครับ 💡
ตัวอย่างที่ 6: จัดอันดับด้วย Filter Context (Grand Rank)Global Product Rank = RANKX( ALLSELECTED(Products[ProductKey]), [Total Sales] )
ALLSELECTED ใช้แทน ALL เพื่อเคารพ filter ที่ user เลือกไว้ แต่ลบ filter ของ visual ปัจจุบันออก
ทำให้ได้อันดับ "ภายในชุดข้อมูลที่เลือก" เหมาะกับ interactive reports ที่ user สามารถเลือก filter ได้เอง
เทคนิคนี้ผมชอบมากครับ เพราะทำให้ Report มี dynamic ranking ที่เปลี่ยนตาม slicer 😎
FAQs
ทำไมได้อันดับ 1 เกือบทุกแถว?+
นี่เป็นปัญหาที่เจอบ่อยมากครับ 😅 สาเหตุคือ table argument ที่ส่งให้ RANKX ถูก filter context จำกัดมากเกินไป ทำให้แต่ละแถวเห็นแค่ตัวเองในตาราง จึงได้อันดับ 1 ทุกแถว
แก้ไขโดยใช้ ALL, ALLSELECTED หรือ ALLEXCEPT เพื่อให้ RANKX เห็นรายการครบตามต้องการ ส่วนตัวผมแนะนำให้ใช้ ALL เป็นจุดเริ่มต้นครับ
Skip กับ Dense ต่างกันอย่างไร?+
Skip (default) จะข้ามเลขอันดับเมื่อมีค่าเสมอ เช่น 1, 2, 2, 2, 5 (ข้าม 3 และ 4)
ส่วน Dense จะไม่ข้ามเลข เช่น 1, 2, 2, 2, 3 (ต่อเนื่องกัน)
ส่วนตัวผมจะเลือกตาม use case ครับ ถ้าเป็น leaderboard หรือการแข่งขัน ใช้ Skip จะดูเป็นธรรมชาติกว่า แต่ถ้าเป็นระบบคะแนนหรือเกรด Dense จะดูเข้าใจง่ายกว่า 💡
ควรใช้ RANK หรือ RANKX?+
RANKX มีความ flexible มากกว่าครับ เพราะสามารถใช้ expression ที่ซับซ้อนได้ และควบคุม table context ด้วย ALL/ALLEXCEPT ได้ด้วย
ส่วนตัวผมใช้ RANKX เกือบทุกครั้ง เพราะ powerful กว่าและควบคุมได้ง่าย
แต่ระวังปัญหา Decimal data type นะครับ ถ้าใช้กับคอลัมน์ Decimal Number ควรใช้ ROUND function หรือเปลี่ยนเป็น Fixed Decimal Number ก่อน มิฉะนั้นอาจได้ผลลัพธ์แปลกๆ 😅
RANKX กับ TOPN ใช้ร่วมกันอย่างไร?+
RANKX ใช้คำนวณอันดับของทุกรายการ ส่วน TOPN ใช้กรองเฉพาะ N รายการอันดับบนสุด
มักใช้ร่วมกันโดย RANKX คำนวณอันดับก่อน แล้ว filter ด้วยเงื่อนไข RANKX
RANKX ทำงานอย่างไรกับ BLANK values?+
BLANK values ถูกแปลงเป็น 0 (สำหรับตัวเลข) หรือ empty text (สำหรับข้อความ) ก่อนจัดอันดับ
ดังนั้น BLANK จะได้อันดับต่ำสุดใน DESC order หรืออันดับสูงสุดใน ASC order
ถ้าไม่อยากให้ BLANK ถูกจัดอันดับ ต้องใช้ FILTER เอาออกก่อนครับ หรือใช้ IF statement ในการแสดงผล 📝
ทำไมต้องระวัง Decimal Number data type?+
นี่เป็นปัญหาเทคนิคที่น้อยคนรู้แต่สำคัญมากครับ 😅 Decimal Number ใช้ floating-point representation (IEEE 754) ซึ่งมีปัญหาความแม่นยำในการเปรียบเทียบค่าเท่ากัน (==)
ทำให้ RANKX อาจให้ผลลัพธ์ไม่คาดคิดกับค่าที่ดูเหมือนเท่ากัน เช่นอาจได้อันดับ 1, 2, 3 แทนที่จะเป็น 1, 1, 3
แก้ไขโดยใช้ Fixed Decimal Number data type หรือใช้ ROUND function ก่อนส่งเข้า RANKX ส่วนตัวผมแนะนำให้ใช้ ROUND เลยครับ ปลอดภัยกว่า 💡
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ RANKX จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
ALL มีพฤติกรรมแตกต่างกัน 2 แบบ: ใช้เป็น Table Expression จะคืนทุกแถวในตาราง หรือทุกค่าในคอลัมน์ โดยไม่สนใจ Filter ใดๆ เหมาะสำหรับใช้ร่วมกับ FILTER, COUNTROWS, SUMMARIZE หรือใช้เป็น CALCULATE Modifier เพื่อลบ Filter ออกจากตารางหรือคอลัมน์ที่ระบุ ทำให้สามารถคำนวณ Grand Total หรือหาเปอร์เซ็นต์เทียบกับยอดรวมได้
ALLEXCEPT ใช้สำหรับ “ล้างตัวกรองเกือบทั้งหมด” โดยคงตัวกรองไว้เฉพาะคอลัมน์ที่ระบุ เหมาะกับการคำนวณ Sub-total/สัดส่วนภายในกลุ่ม เช่น ยอดขายต่อหมวดหมู่ โดยไม่สนตัวกรองอื่น ๆ แนวคิดหลักคือ ลบตัวกรองของตาราง แล้วคงตัวกรองของคอลัมน์ที่เลือกไว้
ALLSELECTED เป็น DAX function ที่ลบ filter context จาก Visual (row และ column filters) แต่คง filter context จาก Slicer, Page Filter และ Report Filter ไว้ ทำให้สามารถคำนวณ Visual Total ได้ ซึ่งเป็นยอดรวมของข้อมูลที่ผู้ใช้เลือกดูในปัจจุบัน ไม่ใช่ Grand Total ทั้งหมด ฟังก์ชันนี้ทำงานผ่าน Shadow Filter Context ซึ่งเป็น filter context ที่ DAX Engine เก็บไว้ก่อนที่ Visual จะเพิ่ม row/column filter เมื่อเรียก ALLSELECTED จะเรียกคืน shadow context นี้ ใช้ร่วมกับ CALCULATE และ SUM, AVERAGE, DIVIDE เพื่อคำนวณ Visual Total, Percentage of Selected, Dynamic Benchmark, Ranking within Selection และ Time Intelligence ที่เคารพ Slicer ต้องระวังการใช้งานใน iterator functions เช่น SUMX, FILTER เพราะอาจให้ผลลัพธ์ที่ไม่คาดคิด และระวัง Expanded Table Caveat ที่จะลบ filter ของ related table ด้วย
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่