ปัญหาหนักอกของคนที่ใช้ Power BI มาถึงระดับนึงก็คือการเขียน DAX ทั้งนี้เป็นเพราะว่าการเขียน DAX จะต้องคำนึงถึง Data Model และ บริบทต่างๆ ทั้ง Filter Context และ Row Context เป็นอย่างดี จึงจะเขียนได้ถูกต้อง (แค่อ่านเจอ 2 คำนี้หลายคนก็งงแล้ว)
แต่ปัญหาเรื่อง DAX ยากๆ นี้จะลดไปได้มากเลยด้วยการมาถึงของ Visual Calculation ซึ่งเป็น Preview Features ที่เพิ่งเข้ามาใน Power BI ในเดือน Feb 2024 นี้เอง (ณ ขณะที่ผมเขียนบทความนี้เลย)
สารบัญ
Visual Calculations คืออะไร?
มันคือการที่ให้เราสามารถ เขียนสูตรเพื่อสร้าง Value ขึ้นมาใหม่ในกราฟโดยตรง โดยที่สามารถอ้างอิงค่าอื่นๆ ในกราฟที่เรากำลังทำงานได้อย่างง่ายดายและสะดวกขึ้นมาก
การเปิดใช้งาน
ณ ปัจจุบันมันเป็น Preview Features อยู่ จะต้องไปเปิดใน File->Option & Settings -> Options -> Preview Features -> Visual Calculations ซะก่อน
พอเปิดใช้งานแล้วมันจะโผล่มาแบบนี้ คือหลังจากที่เราเลือก Visual ใด Visual หนึ่งไปแล้ว ก็จะสามารถ กดสร้าง “New Calculation” ลงไปใน Visual นั้นๆ ได้
จากนั้น มันจะเจาะลงไปเฉพาะใน Visual นั้น แล้วยอมให้เราสร้าง Calculation ใหม่ ซึ่งจะเขียนสูตร DAX รวมถึงสามารถเรียกฟังก์ชันพิเศษที่มีเฉพาะใน Visual Calculations ได้ด้วย ซึ่งสามารถกดปุ่ม fx เพื่อ “ดู Template เบื้องต้น” ได้
จะพบว่ามี Template ต่างๆ มากมายให้ใช้ โดยที่เวลากดแต่ละ Template มันก็จะเรียกใช้ฟังก์ชันต่างๆ กัน (อาจมีการผสมกับฟังก์ชันอื่นด้วย) ซึ่งมี Template มีดังนี้
- Running sum (ค่ารวมสะสม) = RUNNINGSUM([Field])
- Moving average (ค่าเฉลี่ยเคลื่อนที่) = MOVINGAVERAGE([Field], WindowSize)
- Percent of parent (%เทียบแม่) = DIVIDE([Field], COLLAPSE([Field], Axis))
- Percent of grand total (%เทียบแม่ใหญ่สุด)= DIVIDE([Field], COLLAPSEALL([Field], Axis))
- Average of children (ค่าเฉลี่ยของลูก)= EXPAND(AVERAGE([Field]), Axis)
- Versus previous (ผลต่างกับค่าก่อนหน้า) = [Field] – PREVIOUS([Field])
- Versus next (ผลต่างกับค่าถัดไป)= [Field] – NEXT([Field])
- Versus first (ผลต่างกับค่าแรก)= [Field] – FIRST([Field])
- Versus last (ผลต่างกับค่าสุดท้าย)= [Field] – LAST([Field])
โดยผมจะขอแนะนำ Template แรก ซึ่งก็คือ Running Sum ก่อนนะครับ เพราะเข้าใจง่ายดี
ลองใช้ Visual Calculations
Template : Running Sum
สมมติว่าผมต้องการสร้างการคำนวณค่ารวมสะสม ผมก็เลือกใช้ Template ชื่อว่า Running Sum ได้เลย ซึ่งมันจะขึ้นสูตร Template มาให้แบบนี้
Running sum = RUNNINGSUM([Field])RUNNINGSUM
ซึ่งจะเห็นว่ามีการเรียกใช้ฟังก์ชัน RUNNINGSUM โดยเราจะต้องระบุต่อว่า [Field] ที่ต้องการจะหาผลรวมสะสมจะเป็นอะไร??
ซึ่งสังเกตว่า เวลาที่เราเขียนสูตรใน Visual Calculations มันจะมองเห็นแค่ Field ที่อยู่ใน Visual นั้นๆ เท่านั้นนะ (ไม่ได้มองเห็นใน Data Model เหมือนปกติ)
ซึ่งตรงนี้อาจมองง่ายๆ ว่าเหมือนมีตารางของ Visual นี้ทดขึ้นมา แล้วเราก็ทำงานกับ Data ในตารางตรงนี้ได้เลยโดยตรง ไม่ต้องสนใจ Data Model แล้ว
เช่น ผมจะเลือก Field เป็น Red Qty ที่เป็น Value ดั้งเดิมที่เราใส่เข้าไปใน Visual นี้แต่แรก
Running sum = RUNNINGSUM([Red Qty])มันก็จะคำนวณยอดสะสมให้เลยแบบชิลๆ
เราใช้ DAX ปกติใน Visual Cal ก็ได้
หรือ เราจะเพิ่ม Field ใหม่ แบบไม่ใช้ Template ก็ได้นะ เราอาจใช้ DAX ปกติเลยก็ได้ เช่น ผมจะเอาอักขระ 3 ตัวแรกของ Customer Type ก็ทำได้
CustShort = LEFT([Customer Type],3)
หรือจะอ้างค่ามาใช้ใน Visual Calculations Field ใหม่อีกก็ได้ เช่น
ใช้ IF ก็ทำได้ตามปกติเลย เช่น ถ้าเช็คแล้ว 3 ตัวแรกเป็น Com ให้แสดงว่า “ความลับ” นอกนั้นแสดงว่า [Red Qty]
CustIF = IF([CustShort]="com","ความลับ",[Red Qty])
หมายเหตุ : สังเกตว่า ตรง Total ก็ยังเป็น Red Qty ปกติเนอะ เพราะว่าไม่ใช่ Com
การแก้ไข, ซ่อน, ลบ Visual Calculations
ถ้าจะแก้ไขสูตรก็ให้กด Edit Calculation ตรงนี้ได้
ส่วนการลบ กดรูปกากบาท จะลบตัวที่เป็นต้นทางไม่ได้(ที่ตัวอื่นอ้างไปใช้ต่อ) เช่น
- ถ้าลบ CustIF ทำได้ ไม่มีปัญหา
- ถ้าลบ CustShort จะทำให้ CustIF พัง
- ถ้าลบ Red Qty จะทำให้ทั้ง CustShort และ CustIF พัง
ถ้าจะซ่อน ให้กดรูปลูกตา ซึ่งจะทำให้มองไม่เห็น Field นั้นใน Visual จริงๆ ซึ่งเราสามารถซ่อนได้เสมอ ไม่ต้องกังวลอะไรมากมายเท่ากับการลบทิ้งครับ แปลว่าเราสร้าง Field ทดได้นะ
กลับมาดูรายละเอียดของ Template
อย่างไรก็ตาม การ Running ของมันก็จะไล่ตามลำดับของ Item ใน Visual นั้นๆ ซึ่งอาจดูไม่ Make Sense เท่าไหร่ ถ้าเราเอา Person 2021 ไปอยู่ต่อจาก Company 2023
ดังนั้นถ้าเราลองสลับลำดับ Field ใหม่ดู ก็จะพบว่าแบบนี้ดู Make Sense กว่า จริงมั้ย?
ถ้าเราเปลี่ยน Field Customer Type เป็น Month Name น่าจะเหมาะกับการคำนวณค่าสะสมมากกว่า
ถ้าสังเกตดู มันจะสะสมแหลกแบบทะลุทุกสิ่งทุกอย่างเลย แต่ถ้าเราอยากให้มัน Reset เมื่อขึ้นปีใหม่ เราจะต้องระบุละเอียดขึ้น
สามาารถระบุรายละเอียดฟังก์ชันได้อีก
ความสามรถเต็มๆ ของ RUNNINGSUM ถ้าไปเปิดใน Help คือแบบนี้
=RUNNINGSUM ( <column>[, <axis>][, <blanks>][, <reset>] )
//จาก https://learn.microsoft.com/en-us/dax/runningsum-function-daxแปลว่านอกจาก Field แล้วเรายังเลือกเรื่องของ Axis หรือ แกนที่จะสะสม และวิธีการ Reset ค่าได้ด้วย (หลายๆ ฟังก์ชันก็จะระบุเรื่อง Axis และ Reset ได้เช่นกันนะ)
ในที่นี้เรามีปัญหาเรื่องการ reset การสะสมค่าจึงขอพูดถึงการ reset ก่อนนะครับ
การ Reset
ซึ่งการ reset ระบุได้ 4 แบบดังนี้
- NONE = ไม่มีการ Reset
- LOWESTPARENT = Reset ตามตัวแม่ที่ย่อยที่สุด
- HIGHESTPARENT = Reset ตามตัวแม่ที่ใหญ่ที่สุด
- เลขจำนวนเต็ม = อันดับของตัวแม่ที่ต้องการ Reset
- 1 คือ Reset ที่ตัวแม่ใหญ่อันดับ 1 = HIGHESTPARENT
- 2 คือ Reset ที่ตัวแม่ใหญ่อันดับ 2 = LOWESTPARENT
- -1 คือ Reset ที่ตัวแม่ใหญ่อันดับสุดท้าย
หากเราจะให้ Reset เมื่อขึ้นปีใหม่ เราอาจระบุแบบนี้ได้
Running sum Reset =
RUNNINGSUM([Red Qty],,,1)
หรือ
Running sum Reset =
RUNNINGSUM([Red Qty],,,HIGHESTPARENT)
//โดยที่ 1 หรือ HIGHESTPARENT คือ Reset ที่ตัวแม่ใหญ่สุด
แต่ถ้าเราเอาปีไปไว้ที่คอลัมน์แทน ตัว Running sum ปกติก็จะสะสมแค่บนลงล่างเท่านั้น (ค่า Default)
ถ้าหากเราอยากจะให้สะสมข้ามปีได้ด้วย เราจะต้องกำหนด “แกนการสะสม” ได้ใน parameter ที่ชื่อว่า AXIS
กำหนดแกนใน AXIS
Parameter AXIS ซึ่งสามารถระบุได้ดังนี้
- ROWS = บนลงล่าง
- COLUMNS = ซ้ายไปขวา
- ROWS COLUMNS = บนลงล่างก่อน แล้วขึ้นคอลัมน์ใหม่ บนลงล่างต่อ
- COLUMNS ROWS = ซ้ายไปขวาก่อน แล้วขึ้นแถวใหม่ ซ้ายไปขวาต่อ
แปลว่าผมจะระบุแบบนี้
Running sum 2 axis =
RUNNINGSUM([Red Qty],ROWS COLUMNS)ซึ่งจะได้ผลแบบนี้ทันทีครับ
ลองซ่อน Red Qty ออกไป จะได้ดูแล้วไม่ปวดหัว
ฟังก์ชัน Visual Calculation อื่นๆ
นอกจาก Running Sum แล้ว หากเราลองกด Template อื่นๆ ดู เราอาจจะเจอว่ามันเรียกใช้ฟังก์ชันอื่นๆ อีกพอสมควร (อาจมีการเรียกปนกับฟังก์ชัน DAX ปกติ) เช่น
Template : Versus….
ก็จะมีการเรียกว่าปัจจุบันมาเทียบกับค่าต่างๆ ด้วยฟังก์ชันพวกนี้
PREVIOUS, NEXT, FIRST, LAST
- PREVIOUS = เอาตัวในแถวหรือคอลัมน์ก่อนหน้า
- NEXT = เอาตัวในแถวหรือคอลัมน์ถัดไป
- FIRST = เอาตัวในแถวหรือคอลัมน์แรก
- LAST = เอาตัวในแถวหรือคอลัมน์สุดท้าย
เช่น เราใช้ PREVIOUS ดึงค่าตัวก่อนหน้า 1 ตัว หรือ 2 ตัวได้แบบสบายๆ
Previous1 = PREVIOUS([Red Qty])
Previous2 = PREVIOUS([Red Qty],2)
Template : Moving Average
MOVINGAVERAGE
จะมีการเรียกใช้ฟังก์ชัน MOVINGAVERAGE ซึ่งกำหนดขนาดได้ว่าจะเอา “window size” (จำนวนกี่ค่า) มาหาค่า Moving Average
แบบง่าย
Moving average =
MOVINGAVERAGE ( <column>, <windowSize>)
Moving average =
MOVINGAVERAGE([Red Qty], 3)
แบบเต็มๆ
=MOVINGAVERAGE ( <column>, <windowSize>
[, <includeCurrent>][, <axis>][, <blanks>][, <reset>] )เห็นมะว่า ถ้าเอาเต็มๆ เจ้า MOVINGAVERAGE ก็ระบุเรื่อง Axis และ Reset ได้เช่นกันนะ
Template : Percent of Parent
เวลากดแล้วจะได้ Template สูตรแบบนี้
Percent of parent =
DIVIDE([Field], COLLAPSE([Field], Axis))ซึ่งจะเห็นว่า มีการใช้ฟังก์ชัน DIVIDE (การหารกันปกติ แค่มีดัก error ให้) ซ้อนกับ COLLAPSE ซึ่งเป็นฟังก์ชันใหม่ ซึ่งเป็นหัวใจของสูตรนี้
COLLAPSE, COLLASEALL
เจ้า COLLAPSE มันมีความสามารถในการ ดึงข้อมูลจากระดับแม่ใน Visual มาได้ โดยที่กำหนดได้ว่าจะดึงทิศทางไหน เอากี่ Level (ถ้าไม่กำหนดคือ 1 Level) ส่วน COLLASEALL คือเอาตัวใหญ่สุดมาเลย
แต่ถ้าเอาแบบใช้ง่ายๆ ใน Case นี้ก็เขียนแบบนี้ได้เลย
Percent of parent =
DIVIDE([Red Qty], COLLAPSE([Red Qty], ROWS))
ผลลัพธ์ก็จะออกมาว่า ตัวมันเองเป็นสัดส่วนเท่าไหร่ของตัวแม่ของมันเอง (ออกมาเป็นทศนิยม)
ตัวที่ตรงข้ามกับ COLLAPSE ก็คือ EXPAND ซึ่งอยู่ใน Template Average of children
Template : Average of children
Template นี้เป็นการเอาค่าระดับลูกมาร่วมคำนวณด้วย เช่น ในทีนี้คือเอามาหาค่าเฉลี่ย ซึ่ง Template นี้จะมีการเรียกใช้ฟังก์ชัน EXPAND
EXPAND, EXPANDALL
EXPAND, EXPANDALL คือตัวตรงข้ามกับ COLLAPSE, COLLASEALL นั่นเอง ซึ่งความหมายของการ Expand ก็คือ ก็ใส่ Column รายละเอียดเพิ่มแล้วค่อยคำนวณ
EXPAND ( [<Expression>] [, <Axis>]
[, <Column> [, <Column> [, … ] ] ] [, <N>] )เช่น ถ้าเราจะหาค่าเฉลี่ยของตัวลูก เช่น ที่ระดับ Customer (Total ของ ปี) สามารถโช์ค่าเฉลี่ยของแต่ละปี (ลูกของ Customer) ก็สามารถใช้แบบนี้ได้
Average of children = EXPAND(AVERAGE([Red Qty]),ROWS)
วิธีปรับ Format ให้ Visual Calculation
สมมติเราอยากให้ Percent of Parent เป็นรูปแบบ % ณ ตอนนี้ เราก็ยังกดเปลี่ยน Format ให้มันตรงๆ ไม่ได้ (ในอนาคตอาจจะทำได้ง่ายๆ)
วิธีการคือ ต้องทำการทำแบบอ้อมๆ หรือ Work around ด้วยการใช้ฟังก์ชัน FORMAT กำหนดดังนี้
Percent of parent =
FORMAT (
DIVIDE (
[Red Qty],
COLLAPSE ([Red Qty], ROWS)
),
"0.00%"
)ผลลัพธ์ที่ได้จะแสดงออกมาดูเหมือนเป็น % ได้
แต่เหมือนว่าหลังจากใช้ FORMAT ไปแล้ว หากเอาไปแสดงใน Visual ที่ไม่ใช่ตารางมันจะมองไม่เห็น (ผมคิดว่าเป็นเพราะมันเป็น Text?)
พอไป Search ดูเค้าก็บอกแบบนี้จริงๆ
สรุป
การเข้ามาของ Visual Calculation ทำให้การเขียน DAX เพื่อแสดงค่าบางอย่าง โดยเฉพาะการดึงค่ามาจากค่าอื่นใน Visual นั้นง่ายขึ้นมากๆ อย่างไรก็ตาม การใช้ Visual Calculation อาจยังมีข้อจำกัดหลายอย่าง เช่น เรื่อง Format และการอ้างอิง Parameter ก็ยังทำไม่ได้ (อาจเพราะกำลังอยู่ในช่วงพัฒนา)
นอกจากนี้ Visual Calculations ก็ไม่ได้ตอบโจทย์ทุกอย่างเสมอไป เช่น เขียนแล้วก็เอาไปอ้างอิงใช้ต่อใน Visual อื่นไม่ได้นะ มันแยกกันของใครของมันเลย และบางทีก็คำนวณจากเฉพาะใน Visual ตรงๆ ก็มีความเสี่ยงตอนหาผลสรุปตรง Total ได้ด้วย
ดังนั้นถ้าใครอยากจริงจังกับการใช้ Power BI ยังไงก็ควรเรียนรู้การทำงานของ DAX ขั้นสูงต่อไปด้วยนะครับ
