ตามปกติแล้ว ในช่อง Values ของ Pivot Table จะแสดงข้อมูลออกมาได้แค่ตัวเลขเท่านั้นไม่สามารถทำเป็นข้อความได้ ที่เป็นแบบนั้นเพราะมันถูกสรุปด้วยการ Sum, Count, Average, Max, Min ปกติไงล่ะ… แต่ใน Power Pivot เราสามารถเขียน Measure ได้ด้วยฟังก์ชัน DAX อะไรก็ได้ ดังนั้นเราก็สามารถสรุปผลลัพธ์ที่เป็น Text ได้แล้วล่ะ และในบทความนี้ก็จะสอนวิธีทำให้ครับ
สารบัญ
Warning : บทความนี้เริ่มมีความซับซ้อน
ในบทความนี้อาจมีสูตรหลายตัวที่ค่อนข้างมีความซับซ้อน เพราะเกี่ยวข้องกับ Concept หลายๆ อย่างของ DAX ที่ใน Excel ไม่มี เช่นเรื่องของ Table Function หรือสูตรที่ให้ผลลัพธ์ออกมาเป็นตาราง รวมถึง Concept ของการคำนวณ ซ้ำๆ แต่ละแถวของตารางด้วย Expression ที่กำหนด (เรียกว่า Iterate) ซึ่งค่อนข้างใช้จินตนาการสูงพอควร
การจะเข้าใจอย่างลึกซึ้งว่ามันทำงานยังไง คุณอาจต้องศึกษาการทำงานของ DAX เพิ่มเติมด้วยนะครับ เพราะมันอธิบายสั้นๆ ได้ยากพอควร
ซึ่ง Tips ที่ผมอยากแนะนำ คือ เรียน DAX ผ่าน Power BI จะง่ายกว่า Excel เพราะใน Power BI มีตัวช่วยเยอะกว่า ซึ่งเรียนใน Power BI แล้วสุดท้ายก็เอามาใช้ใน Excel ได้อยู่ดี (ผมเองก็เรียน DAX จาก Power BI เหมือนกัน แล้วตอนนี้ผมก็เอามาใช้ใน Excel ได้)
หากใครสนใจ ใครสนใจก็ลองไปดูได้ครับ (ตอนนี้กำลังลดราคาพิเศษอยู่ด้วย จาก 2290 เหลือ 1790 บาท หมดเขต 29 กค. 63)
เอาล่ะ หมดช่วงขายของแล้ว มาเริ่มสิ่งที่น่าสนใจกันจริงๆ ดีกว่า 555
มาดูกันว่าเราสามารถใช้ DAX เขียนสูตรเพื่อทำให้ช่องสรุปแสดงข้อความออกมาในลักษณะไหนได้บ้าง
เอา Transaction ID ล่าสุดมาแสดง
เราสามารถใช้ฟังก์ชัน LASTNONBLANK มาช่วยในกรณีนี้ได้ครับ
LASTNONBLANK ( <ColumnName>, <Expression> )
LASTNONBLANK จะทำงานแบบ Iterator เช่นเดียวกับพวก SUMX แต่มีความสามารถในการเช็ค Expression สำหรับแต่ละแถวของ <ColumnName> โดยเรียงตาม Sort Order ของ Column นั้นๆ แล้วเอาค่าสุดท้ายที่ <ColumnName> และ <Expression> ไม่ Blank กลับมา ซึ่งผลลัพธ์จะกลับมาเป็นตารางที่มี 1 แถว 1 คอลัมน์
Tips : เมื่อ DAX ให้ผลลัพธ์เป็นตารางที่มี 1 แถว 1 คอลัมน์ มันสามารถถูกมองให้เป็นผลลัพธ์เป็น Scalar Value หรือค่าตามปกติได้ด้วย แปลว่าผมสามารถแสดงผลลัพธ์ด้วยค่านี้ได้เลย
แปลว่าผมสามารถให้มันเอาเลข TXID ล่าสุดมาได้เพราะว่า TXID เราเรียงเป็นเลข Running อยู่แล้วจึงใช้ได้
=LASTNONBLANK(TXData[TXID], TXData[TXID])
หรือจะใส่ <Expression> เป็นเลข 1 ไปเลยก็ยังได้ครับ เพื่อให้มันเช็คแค่ TXData[TXID] อย่างเดียว
=LASTNONBLANK(TXData[TXID],1)
แค่นี้เราก็จะได้ Transaction ID ล่าสุดแล้วล่ะ 555
ถ้าเกิดใครอยากได้ Transaction ID แรกสุด ก็จะมีตัวคล้ายๆ กันให้ใช้ นั่นก็คือ FIRSTNONBLANK ( <ColumnName>, <Expression> ) นั่นเองครับ
เอา Product ล่าสุดมาแสดง
สำหรับ “ชื่อProduct” เราไม่สามารถใช้ LASTNONBLANK มาช่วยได้ เพราะ คอลัมน์ “ชื่อProduct” มันเรียงตาม A-Z (เมื่อคอลัมน์นี้ไม่ได้เรียงตามเวลา คำว่า LAST เมื่อใช้กับชื่อProduct มันจึงไม่มี concept ของคำว่า “ล่าสุด” ) แต่เราจะเอาเจ้า [TXID ล่าสุด] ที่ได้จากข้อที่แล้วมาช่วยทำงานต่อไปด้วยการใช้ LOOKUPVALUE แบบนี้
LOOKUPVALUE ( <Result_ColumnName>, <Search_ColumnName>, <Search_Value>)
LOOKUPVALUE สามารถเอาผลลัพธ์ในคอลัมน์ <Result_ColumnName> ที่ต้องการกลับมา โดยค้นหาข้อมูล <Search_Value> ในคอลัมน์ <Search_ColumnName> นั่นเอง
ซึ่งจะเห็นว่า Concept การเขียนสูตรคล้ายๆ ฟังก์ชัน LOOKUP ใน Excel เลย แต่ LOOKUPVALUE มันต้องเจอผลลัพธ์แบบเป๊ะๆ เท่านั้น ต่างจาก LOOKUP ใน Excel ที่ทำการค้นหาแบบ Approximate Match นะ
สรุปแล้ว ใน เคสนี้เราเขียนได้ว่า
=LOOKUPVALUE(TXData[สินค้า],TXData[TXID],[TXID ล่าสุด])แปลว่าให้เอา [TXID ล่าสุด]ไปหาในคอลัมน์ TXData[TXID] จากนั้นให้เอาค่า TXData[สินค้า] ในแถวเดียวกันกลับมา
ซึ่งจะช่วยให้เราได้ผลลัพธ์ที่ต้องการได้แล้ว ซึ่งสามารถประยุกต์ใช้วิธีนี้กับคอลัมน์ไหนๆ ก็ได้ จริงมะ?
เอาชื่อลูกค้าทุกคนมารวมกันแยกด้วยตัวคั่น
เราสามารถใช้ฟังก์ชัน CONCATENATEX มาช่วยรวมข้อมูล Text แล้วใส่ Delimiter เป็น ได้ ซึ่งมันจะทำงานคล้ายๆ SUMX แต่ต่างกันที่ตอนจบ ตรงที่ SUMX เอาข้อมูลใน Expression ทุกตัวมา SUM กัน แต่เจ้า CONCATENATEX นั้นเอา Expression ทุกตัวมาเชื่อมเป็นข้อความเดียวกันแล้วคั่นด้วย Delimiter ซึ่งสามารถเรียงลำดับการเชื่อมได้ด้วยนะ
CONCATENATEX ( <Table>, <Expression>, [<Delimiter>] , [<OrderBy_Expression>] , [<Order>] )
- ฟังก์ชันนี้จะทำการ Iterate แต่ละแถวของ <table> ด้วยการเอา <expression> มาเชื่อมกันด้วย [delimiter]
- สำหรับ <table> นั้นเราใช้อีกฟังก์ชัน นั่นคือ DISTINCT มาช่วย ซึ่งมีความสามารถทำให้เหลือเฉพาะ item ที่ไม่ซ้ำกันเท่านั้นแล้วคืนค่ากลับมาเป็นตาราง (เรียกว่า Table Function ซึ่งใน DAX มีหลายตัวมากๆ)
- โดยในที่นี้ใส่เป็น DISTINCT(TXData[ลูกค้า]) เพื่อให้ได้รายชื่อลูกค้าแบบไม่ซ้ำกันนั่นเอง
สรุปแล้ว เราจะเขียนสูตรแบบนี้
=CONCATENATEX(DISTINCT(TXData[ลูกค้า]),[ลูกค้า],"/")
ถ้าอยากให้เรียงตามคอลัมน์ลูกค้าเอง (ซึ่งก็เรียงตาม A-Z) ก็สามารถระบุคอลัมน์ที่ใช้ Sort ได้ และวิธีการ Sort ได้
=CONCATENATEX(DISTINCT(TXData[ลูกค้า]),[ลูกค้า],"/",[ลูกค้า],ASC)
List ชื่อลูกค้าที่สร้างยอดขายมากสุด Top3
เราสามารถใช้ TOPN มาช่วยสร้างผลลัพธ์เป็น Table ที่ Filter ให้เหลือผลลัพธ์ N ตัวเรียงตาม <OrderBy_Expression> ได้ดังนี้
TOPN ( <N_Value>, <Table> , [<OrderBy_Expression>] , [<Order>] )
จากนั้นเอาไปใช้ในส่วนของ <table> ของ CONCATENATEX
=CONCATENATEX(TOPN(3,DISTINCT(TXData[ลูกค้า]),[TotalSales],DESC),[ลูกค้า],"/",[TotalSales],DESC)และเราสามารถรวมยอดขายเฉพาะ TopN Customer ได้ด้วยการใช้ SUMX มาช่วยดังนี้
=SUMX(TOPN(3,DISTINCT(TXData[ลูกค้า]),[TotalSales],DESC),[TotalSales])
ตัว [TotalSales] จะถูกคิดสำหรับข้อมูลแต่ละแถวของ TOPN ซึ่งจะเกิด Effect ที่เรียกว่า Context Transition ที่จะช่วยให้คิดข้อมูลเป้นยอดขายของลูกค้าแต่ละคนนั้นๆ ได้
สรุปออกมาได้ดังนี้
อย่างไรก็ตามคำสั่ง TOPN ถ้าเกิดมีลูกค้ายอดขายเท่ากันเป๊ะ มันอาจจะออกมามากกว่า N ที่เรากำหนดก็ได้ เช่น
Sales ค ขายของเล่น มีลูกค้าที่มียอดเท่ากันดังรูป
ทำให้ Sales ค ขายของเล่น มัน List ออกมา 4 คน คือ C00004, C00011, C00012, C00013 และได้ยอดรวม TOPN 3,150 บาท
ถ้าเราอยากได้ TOP3 แบบที่ได้ 3 จริงๆ ไม่ให้เกินมา
จะต้องมีการจัดการเรื่องค่าซ้ำ เช่น วิธีที่ง่ายที่สุดคืออาจจะบวกค่า Random ที่น้อยมากๆ เข้าไปเพื่อให้ไม่มีเลขที่เท่ากันเป๊ะๆ เช่น
Listลูกค้าTop3 (แบบได้ 3 จริงๆ)
=CONCATENATEX(TOPN(3,DISTINCT(TXData[ลูกค้า]),[TotalSales]+RAND()/1000,DESC),[ลูกค้า],"/",[TotalSales],DESC)Top3CustSales (แบบมาจาก 3 คนจริงๆ)
=SUMX(TOPN(3,DISTINCT(TXData[ลูกค้า]),[TotalSales]+RAND()/1000,DESC),[TotalSales])
สรุปแล้วจะได้แบบนี้
Sales ค ขายของเล่น จะได้แค่ 3 คน คือ C00004, C00011, C00012 (ซึ่งจะได้ 12 หรือ 13 มันสุ่มเอานะ…)
และนี่คือตัวอย่างของการใช้ DAX เพื่อสรุปข้อมูลออกมาเป็นข้อความครับ จะเห็นว่าสามารถพลิกแพลงได้มากกว่า Pivot Table ธรรมดาๆ เยอะเลย และนี่แหละที่เป็นหนึ่งในสาเหตุที่บอกว่า Power BI นั้นเจ๋งมากๆ ก็เพราะมันมี DAX แบบนี้ยังไงล่ะ
ตอนต่อไป
ในตอนต่อไปเริ่มจะเริ่มทำงานกับตารางที่มากกว่า 1 อัน โดยที่ตารางหลายๆ อันนั้นจะมีการเชื่อมความสัมพันธ์กันเป็นสิ่งที่เรียกว่า Data Model นั่นเอง ใช่แล้วล่ะ เรากำลังจะได้เรียนสิ่งที่เป็น Data Model กันจริงๆ ซักที แล้วคุณจะได้เห็นพลังที่แท้จริงของสิ่งที่เรียกว่า Data Model ว่ามันเจ๋งแค่ไหนครับ
สารบัญซีรีส์ Power Pivot
- วิธีกำหนดให้ Pivot Table แสดงเฉพาะแถว/คอลัมน์ที่ต้องการ
- การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน
- การใช้ Excel Power Pivot ตอนที่ 2 : ทำผลสรุป Value ให้เป็นข้อความด้วย DAX
- การใช้ Excel Power Pivot ตอนที่ 3 : ลาก่อน VLOOKUP สวัสดี Data Model
- การใช้ Excel Power Pivot ตอนที่ 4 : Data Model ทำงานอย่างไร
- การใช้ Excel Power Pivot ตอนที่ 5 : การทำรายงาน Actual vs Target
- Inventory Management ทำรายงานสินค้าคงคลังด้วย DAX และ Data Model ใน Excel
- วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด
- หลากวิธีเอาข้อมูลในกลุ่มเดียวกันไปรวมเป็นข้อความเดียวกัน
