จากในตอนที่แล้วเราได้ทำความรู้จัก CALCULATE กันไปแล้วว่ามันเป็นฟังก์ชันที่เอาไว้เปลี่ยน Filter Context ได้ตามต้องการ ในตอนนี้เราจะมาทำความรู้จักพลังที่แท้จริงของมัน ซึ่งก็คือ
- CALCULATE สามารถเปลี่ยน Row Context ให้เป็น Filter Context ได้ เรียกว่า Context Transition
- Measure ทุกตัวมี CALCULATE แฝงอยู่ข้างในเสมอ
สารบัญ
ไฟล์ประกอบ
ใช้ไฟล์เดิมจากตอนก่อนหน้าได้เลยนะครับ หรือจะใช้อันนี้ก็ได้ ซึ่งเรามี Data Model แบบนี้
มาดูเรื่องแรกกันก่อนครับ
CALCULATE กับ Context Transition
หากเรานำ CALCULATE ไปใช้ในบริบทที่มี Row Context มันจะเปลี่ยน Row Context นั้นให้กลายเป็น Filter Context … ฟังแล้วไม่เข้าใจเลยซักนิดใช่มั้ยครับ? งั้นมาดูตัวอย่างดีกว่าครับ
สมมติว่าผมไปที่ Data View แล้วไปที่ Table dStores แล้วสร้างคอลัมน์ใหม์โดยที่ผมอยากจะแสดงยอดขายของแต่ Store ไว้ในคอลัมนี้
ผมสามารถทำได้โดยกด New Column แล้วใส่สูตรแบบนี้ได้
StoreRevenue = CALCULATE([TotalRevenue])
ผลลัพธ์ที่ได้มันจะใช้ CALCULATE คำนวณ TotalRevenue โดยเปลี่ยน Filter Context ให้เป็นไปตามค่าในแถวเดียวกับมัน
สมมติว่าเราลองวิเคราะห์ Filter ของบรรทัดแรกจะมีดังนี้
- StoreKey=1
- StoreType=Store
- StoreName=Contoso Seattle No.1 Store
- Status=On
- CloseReason=blank
- EmployeeCount=17
- SellingAreaSize=462
- GeographyType=City
- ContinentName=North America
- RegionCountryName=United Sates
พอมัน Filter ตามนี้ก็จะได้ว่า StoreRevenue = 1,615,303.83
นี่แหละคือการเปลี่ยน Row Context เป็น Filter Context ซึ่งเรียกว่า Context Transition ซึ่งทำให้ผลคำนวณ Revenue แต่ละบรรทัดออกมาไม่เท่ากัน เพราะว่ามันมี Filter ไม่เหมือนกันนั่นเอง
ระวัง! ถ้าคิดดูให้ดู เวลาเราคำนวณแบบนี้ หากในตารางมีคอลัมน์ที่ Unique ไม่ซ้ำกัน ผลลัพธ์ก็จะไม่มีปัญหา แต่ถ้าในตารางดันมีบรรทัดที่ Duplicate กันทั้งแถว ค่าที่ได้จากการคำนวณจะเบิ้ลทันที
Measure ทุกตัวมี CALCULATE แฝงอยู่ข้างในเสมอ
ประเด็นถัดไปก็คือ Measure ทุกตัวมี CALCULATE แฝงอยู่เสมอ อันนี้เข้าใจง่ายครับ
แปลว่า การเขียนสูตรว่า
StoreRevenue = CALCULATE([TotalRevenue])
สามารถเขียนเหลือแค่ Measure ก็ได้ เพราะมันมี CALCUATE อยู่ในตัวอยู่แล้ว เช่น
StoreRevenue = [TotalRevenue]
ซึ่งจะได้ผลลัพธ์เท่าเดิมทุกประการ แต่เขียนสั้นลงไปอีก
และนี่ก็คือความลับอีก 2 ข้อที่เกี่ยวกับ CALCULATE ครับ จะเห็นว่ามันสั้นๆ ดูเรียบง่าย ไม่มีอะไร แต่พอใช้งานจริง หลายๆ คนอาจลืมนึกถึงเรื่องพวกนี้ ลืมนึกไปว่า Measure มี CALCULATE อยู่ และมันก็จะเกิด Context Transition ด้วย
เพื่อให้เห็นความสามารถที่ลึกซึ้งมากขึ้น ลองมาดูตัวอย่างการใช้ CALCULATE กับ Row Context ที่น่าสนใจขึ้นดีกว่าครับ
CALCULATE กับ Row Context ของ Iterator
สมมติว่าผมอยากจะแสดงยอดขายเฉลี่ยของแต่ละ Store โดยแบ่งตาม Product Category เราจะทำยังไง?
ก่อนอื่นผมจะต้องทำ Visual ที่มี Product Category อยู่ที่ Row ไว้ก่อน (เพราะเราจะแบ่งตาม Product Category ไง) ดังนี้
ทีนี้เราจะทำให้ Values หรือการคำนวณ คำนวณแบบไหนล่ะ? เราอยากได้ยอดขายเฉลี่ย ถ้าเราลาก Total Revenue ลงมา มันก็คือยอดขายรวม หรือถ้าเราลาก Field ยอดขายลงมาแล้วกด Average มันก็คือยอดขายเฉลี่ยแต่ละ Transaction ไม่ใช่ยอดขายเฉลี่ยของแต่ละร้านค้า…
ทางออกคือ การสร้าง Measure ยอดขายเฉลี่ยของแต่ละร้านขึ้นมา
Concept คือ เราจะใช้ Iterator เช่น AVERAGEX มาช่วย
AVERAGEX ( <Table>, <Expression> )
AVERAGEX จะทำการคำนวณ <Expression> ในแต่ละแถวของ <Table> (เรียกว่า Iterate) จากนั้นจำไว้ในใจ (สร้างตารางจำลอง) พอครบทั้ง Table แล้วก็เอามา Average กันซะ
สูตรที่จะใช้คือ
AverageStoreSales = AVERAGEX(DISTINCT(dStores[StoreKey]),[TotalRevenue])
Table คือ DISTINCT(dStores[StoreKey]) ซึ่งก็คือ Store แต่ละร้านแบบไม่ซ้ำกัน
Expression คือ [TotalRevenue] ซึ่งคือ Measure และมันมี CALCULATE แฝงอยู่ข้างใน ทำให้เกิด Context Transition ในการคำนวณ [TotalRevenue] ของ StoreKey แต่ละบรรทัดนั่นเอง
พอคำนวณ TotalRevenue ของแต่ละ StoreKey ได้ก็จับมา Average กันให้เรา จบ.. ได้ผลลัพธ์แบบนี้
แต่ละสินค้าก็จะมียอดขายเฉลี่ยต่อร้านไม่เท่ากัน และตรง Total ก็คือยอดขายเฉลี่ยต่อร้านแบบที่ไม่ได้แบ่งตามสินค้า (พูดง่ายๆ ก็คือรวมสินค้าทุกประเภทนั่นแหละ) เลขมันก็เลยเยอะ
การใช้ CALCULATE ผสมกับพวก SUMX AVERAGEX MAXX RANKX เพื่อแปลง Row Context เป็น Filter Context เป็นอะไรที่ใช้ในชีวิตจริงบ่อยมาก
จะเห็นว่า TotalRevenue คำนวณจากตาราง fSales ดังนั้นจึงสามารถ Filter จากตารางอื่นได้เมื่อเราใช้ พวก SUMX AVERAGEX MAXX สร้างตารางจำลองขึ้นมา แล้วเกิด Context Transition ขึ้น
ยกตัวอย่างเช่น
TotalRevenue เฉลี่ยรายร้านค้า
AverageStoreRevenue =
AVERAGEX(DISTINCT(dStores[StoreKey]),[TotalRevenue])
TotalRevenue เฉลี่ยราย Category สินค้า
AvgProductCategoryRevenue =
AVERAGEX( DISTINCT(dProduct[ProductCategory]) , [TotalRevenue] )
TotalProfit ที่มากสุดรายสินค้า
MaxProductCategoryProfit =
MAXX( DISTINCT(dProduct[ProductCategory]) ,[TotalGrossProfit] )
ซึ่งจะเห็นว่าเราสามารถสร้างมุมมองต่างๆ เพิ่มได้ไม่จำกัด หาก Measure หลักนั้นคำนวณจากข้อมูลซึ่งอยู่ที่ Fact Table แต่ถ้า Measure หลักไม่ได้คำนวณจากคอลัมน์ใน Fact Table จะต้องทำตามในบทความนี้ครับ
และนี่ก็คือความเจ๋งของการที่เรารู้ DAX ครับ เพราะเป็นเรื่องที่ PivotTable แบบปกติใน Excel ทำไม่ได้เลย ต้องเขียน DAX Measure ใน PowerPivot หรือ Power BI เท่านั้น ^^
สารบัญ 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
ใครสนใจอยากเรียนเป็นคลิปวีดีโอ ผมมีคอร์สออนไลน์ที่สอน Power BI ตั้งแต่พื้นฐาน สามารถไปดูรายละเอียดได้ที่นี่
