การคำนวณค่าเฉลี่ยแบบตัดส่วนปลายแบบไดนามิกใน Power BI โดยใช้ DAX: คู่มือฉบับย่อ

เหตุใดคุณจึงต้องใช้ค่าเฉลี่ยแบบตัดทอน

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

\ นี่คือจุดที่ค่าเฉลี่ยแบบตัดทอนมีความจำเป็น มันให้การวัดค่าเฉลี่ยที่แข็งแกร่งกว่าโดยการยกเว้นเปอร์เซ็นต์ที่กำหนดของค่าที่เล็กที่สุดและใหญ่ที่สุดออกจากการคำนวณ

\ แม้ว่าโมเดล Power BI สมัยใหม่จะมีฟังก์ชัน TRIMMEAN ในตัว แต่ฟังก์ชันนี้มักไม่สามารถใช้ได้เมื่อใช้ Live Connection กับโมเดล Analysis Services (SSAS) รุ่นเก่า บทความนี้จะนำเสนอรูปแบบ DAX แบบแมนนวลที่แข็งแกร่ง ซึ่งจำลองฟังก์ชันนี้และยังคงพลวัตอย่างเต็มที่ ตอบสนองต่อ slicers และ filters ทั้งหมดในรายงานของคุณ

โซลูชัน DAX สำหรับค่าเฉลี่ยแบบตัดทอนแบบไดนามิก

เมเชอร์นี้คำนวณค่าเฉลี่ยแบบตัดทอน 20% โดยการลบค่า 10% ที่ต่ำสุดและ 10% ที่สูงสุดออก ก่อนที่จะหาค่าเฉลี่ย 80% ที่เหลือ

\ คุณสามารถวางโค้ดนี้ลงในแถบสูตร "New Measure" ได้โดยตรง

Trimmed Mean (20%) = VAR TargetTable = 'FactTable' VAR TargetColumn = 'FactTable'[MeasureColumn] VAR LowerPercentile = 0.10 // Defines the bottom 10% to trim VAR UpperPercentile = 0.90 // Defines the top 10% to trim (1.0 - 0.10) // 1. Find the value at the 10th percentile VAR MinThreshold = PERCENTILEX.INC( FILTER( TargetTable, NOT( ISBLANK( TargetColumn ) ) ), TargetColumn, LowerPercentile ) // 2. Find the value at the 90th percentile VAR MaxThreshold = PERCENTILEX.INC( FILTER( TargetTable, NOT( ISBLANK( TargetColumn ) ) ), TargetColumn, UpperPercentile ) // 3. Calculate the average, including only values between the thresholds RETURN CALCULATE( AVERAGEX( FILTER( TargetTable, TargetColumn >= MinThreshold && TargetColumn <= MaxThreshold ), TargetColumn ) )

การแยกวิเคราะห์ตรรกะ DAX

สูตรนี้ทำงานใน 3 ขั้นตอนที่แตกต่างกัน ซึ่งทั้งหมดจะดำเนินการภายในบริบทตัวกรองปัจจุบัน (เช่น slicers ใดก็ตามที่ผู้ใช้เลือก)

1. กำหนดตัวแปรสำคัญ

• TargetTable & TargetColumn: เราจะกำหนดชื่อตารางและคอลัมน์ให้กับตัวแปรเพื่อให้โค้ดสะอาดและนำกลับมาใช้ได้ คุณต้องเปลี่ยน 'FactTable'[MeasureColumn] ให้ตรงกับโมเดลข้อมูลของคุณ
• LowerPercentile / UpperPercentile: เรากำหนดขอบเขต 0.10 และ 0.90 หมายความว่าเรากำลังตัดทอน 10% ที่ต่ำสุดและ 10% ที่สูงสุด หากต้องการตัดทอน 5% จากแต่ละด้าน (รวม 10%) คุณจะใช้ 0.05 และ 0.95

2. ค้นหาเกณฑ์เปอร์เซ็นไทล์

• MinThreshold & MaxThreshold: ตัวแปรเหล่านี้จัดเก็บค่าจริงที่สอดคล้องกับขอบเขตเปอร์เซ็นไทล์ของเรา
• PERCENTILEX.INC: เราใช้ฟังก์ชัน "iterator" นี้เพราะมันช่วยให้เราสามารถ FILTER ตารางได้ก่อน
• `FILTER(…, NOT(ISBLANK(…))): นี่เป็นขั้นตอนที่สำคัญ เราคำนวณเปอร์เซ็นไทล์เฉพาะแถวที่คอลัมน์เป้าหมายไม่ว่างเปล่า สิ่งนี้ป้องกันไม่ให้ค่า BLANK() บิดเบือนการคำนวณเปอร์เซ็นไทล์
• ผลลัพธ์คือ MinThreshold เก็บค่าของเปอร์เซ็นไทล์ที่ 10 (เช่น 4.5) และ MaxThreshold เก็บค่าของเปอร์เซ็นไทล์ที่ 90 (เช่น 88.2) สำหรับข้อมูลที่มองเห็นในปัจจุบัน

3. คำนวณค่าเฉลี่ยสุดท้าย

• RETURN CALCULATE(...): ฟังก์ชัน CALCULATE เป็นกุญแจสำคัญในการทำให้เมเชอร์เป็นแบบไดนามิก มันช่วยให้มั่นใจว่าการคำนวณทั้งหมดเคารพตัวกรองที่ใช้โดย slicers หรือ visuals ใดๆ ในรายงาน
• AVERAGEX(FILTER(...)): แกนหลักของการคำนวณ เราใช้ AVERAGEX เพื่อวนซ้ำผ่านตาราง
• FILTER(...): เราจะกรอง TargetTable ครั้งสุดท้าย ตัวกรองนี้คือ "การตัดทอน" มันเก็บเฉพาะแถวที่ค่าใน TargetColumn เป็น:
• มากกว่าหรือเท่ากับ MinThreshold ของเรา
• และ
• น้อยกว่าหรือเท่ากับ MaxThreshold ของเรา
• AVERAGEX(..., TargetColumn): AVERAGEX จะคำนวณค่าเฉลี่ยแบบธรรมดาของ TargetColumn สำหรับแถวที่ผ่านตัวกรองเท่านั้น

บทสรุป

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

---

ขอบคุณที่สละเวลาสำรวจข้อมูลเชิงลึกที่เกี่ยวข้องกับข้อมูลกับฉัน ฉันขอขอบคุณสำหรับการมีส่วนร่วมของคุณ หากคุณพบว่าข้อมูลนี้มีประโยชน์ ฉันขอเชิญคุณติดตามฉันหรือเชื่อมต่อกับฉันบน LinkedIn หรือ X(@Luca_DataTeam) สนุกกับการสำรวจ!👋