###### tags: `Excel` `PowerBI` # PowerBI 資料視覺化 <style> /* 加入Logo */ .toc:{position: relative;} .toc:before{ content: url(https://zoego.tech/img/logo.svg); width:80%; height:80% position:fixed; top:0 ; left:0; } /* 調整 左側NAV */ .markdown-body { max-width: 900px !important;} .markdown-body h1,.markdown-body h2{border-bottom: none;} .ui-toc-dropdown .nav>li>a{ color: #000} .ui-toc-dropdown .nav>.active:focus>a, .ui-toc-dropdown .nav>.active:hover>a, .ui-toc-dropdown .nav>.active>a{ color:#C00000;} /* h1~H5 修改 */ h1{ color:#4C4C6D; } .h2{ color:#52006A; background-color:#A2DBFA; line-height:1.8em;font-size:.9em;display:block; padding-left:10px;border-radius:5px;} h3{ color:#BF1363; } .h4{color:#000099; font-size:1.2em;} .h5{color:#7030A0; font-size:1.2em;} /* 講師資訊 美化 */ .alert-warning { border-width: 2px; width: 60%; margin: auto;} /* 指令 縮圖 ICON圖設定 */ .img3{ width:30px; display:inline-block;margin:0 5px;} .img4{ width:40px; display:inline-block;margin:0 5px;} .img5{ width:50px; display:inline-block;margin:0 5px;} .img6{ width:60px; display:inline-block;margin:0 5px;} /* 圖片尺寸 設定 */ .img20{ width:20%; margin:20px auto; display:block; text-align: center;} .img30{ width:30%; margin:20px auto; display:block; text-align: center;} .img40{ width:40%; margin:20px auto; display:block; text-align: center;} .img50{ width:50%; margin:20px auto; display:block; text-align: center;} .img60{ width:60%; margin:20px auto; display:block; text-align: center;} .img70{ width:70%; margin:20px auto; display:block; text-align: center;} .img80{ width:80%; margin:20px auto; display:block; text-align: center;} .img90{ width:90%; margin:20px auto; display:block; text-align: center;} .img100{ width:100%; margin:20px auto; display:block; } /* 連結URL 調整 */ .urlr{ text-align:right; line-height:1.5em;display:block;} .urll{ line-height:1.5em;display:block;} /* 提供講義標示頁碼 */ .page{display:flex; justify-content: right;} .page span{ background:#ffdd75;border:1px solid #d1a128;border-radius:5px; padding:5px;color:#000;text-align:right; float:right; } /* Slider 外掛 美化 */ iframe{ width:850px; height:478px; display:block; margin:auto; border-width: 0;border:2px solid #000;} /* 設定 pre 及 快速鍵 */ .markdown-body code { border: 1px solid #000; margin:0 5px; } .markdown-body pre>code { font-size: 1.2em; } .markdown-body pre { border: 1px solid #ab0505 !important; margin:10px 0; padding:5px 10px;} /* 快速鍵 > 語法於P中 */ .markdown-body p>code { color:#BF1363 !important; border:none; background:none} /* 表格 設定 */ .markdown-body table { width: fit-content; margin: auto; } .markdown-body table th, .markdown-body table td { border: 1px solid #7F7F7F; } .tableTitle{text-align:center; display:block;} .red{color:#BF1363;font-weight:bold;} .blue{color:#265F92;font-weight:bold;} .step{ padding-left:1.5em;color:blue;font-size:1.1em; } .textc{text-align:center; display:block;} .markdown-body p{ margin-bottom: 0;} .flex{ display:flex; border:2px solid #000; padding:20px; margin:20px; } .ex{color:#333399 ; font-size:1.2em; font-weight:bold; line-height:1em;} /* 置中、圓角框、項目 */ .title{ text-align:center; display:block;color:#766161;font-size:1.2em;font-weight:bold; border:1px solid;line-height:2em;border-radius:5px} </style> :::warning <span class="textc" style="font-weight:bold; font-size:1.2em">課程講師資訊</span>巨匠電腦講師:呂心怡 E-mail:zoego99@gmail.com ::: <br><br> <span style="width:100%; height:50vh ;display:block"> <iframe title="PBI_咖啡銷售數據 - 儀表板" style="width:100%; height:100% ;" src="https://app.powerbi.com/view?r=eyJrIjoiMzBmODhhMjgtNDBlYy00ODBjLThkY2MtZDFhMTRjNjM2MWU4IiwidCI6ImNkNWRmNDkxLTZhMTYtNDkzZS1iNTVhLTkzOGYzYWE5YWRlYiIsImMiOjEwfQ%3D%3D" frameborder="0" allowFullScreen="true"></iframe> </span> <br> ## <span class="h2">主題1:介紹</span> ### 1. PBI軟體間分責說明 在工作任務中,經常需要利用Excel樞紐分析功但不了解Power BI使用者,Power BI Desktop是安裝在本機電腦的免費商業分析工具,可從多種檔案類型匯入資料後,快速建立互動圖表的工具,輕鬆完成大數據的分析! #### <span class="h4">(1) 使用Power BI源由</span> <span style="display:block; padding-left:2em;">使用Excel處理資料時,花費許多時間使用Vlookup公式來合併不同的資料表格。 需要經常手工製作重複性數據報表(每月、每季或每年),只想要自動化刷數據/圖表者。 需要為了進行資料視覺化動態圖表時,Power BI即能滿足需求。</span> #### <span class="h4">(1) PowerBI套件各項軟體間的分責說明</span> <span style="display:block; padding-left:2em;">整體性概觀了解PowerBI各項軟體間的分責運用。</span> <a href="https://hackmd.io/_uploads/HJ-8nRQOF.png"><span class="img100">![](https://hackmd.io/_uploads/HJ-8nRQOF.png)</span></a> ### 2. 參考網站 #### <span class="h4">(1) Power BI官網:</span> &#12288;&#12288;了解PowerBI及軟體下載: &#12288;&#12288;&#12288;👆 https://powerbi.microsoft.com/zh-tw/ &#12288;&#12288;&#12288;👆 https://powerbi.microsoft.com/zh-tw/desktop/ &#12288;&#12288;PowerBI 文件:👆 https://docs.microsoft.com/zh-tw/power-bi/ #### <span class="h4">(2) Power Query M 公式語言:</span> &#12288;&#12288;👆 https://docs.microsoft.com/zh-tw/powerquery-m/ <span style="display:block; padding-left:2em;">M語言是Power Query的後台函數式編程語言,在Power Query界面的操作過程都會記錄下來並翻譯成M語言,將查詢和轉換的結果返回到Excel表或Excel或Power BI數據模型。</span> <span style="display:block; padding-left:2em;">Power Query上手十分容易,通過圖形界面操作可以解決大部分需求,一般情況下,我們不用去深入學習M語言,但是,如果對M語言有了一定的理解可以對操作步驟進行優化,讓其更加智能,同時也能解決一些圖形界面操作不能完成的任務。</span> #### <span class="h4">(3) Power BI Desktop使用DAX函數:</span> &#12288;&#12288;👆 https://docs.microsoft.com/zh-tw/dax/ <span style="display:block; padding-left:2em;">DAX是Power Pivot和Power BI Desktop中使用的公式語言。 DAX使用函數處理存儲在表中的數據。一些DAX函數與Excel工作表函數相同,但DAX具有更多功能來匯總,切片和切割複雜的數據場景。</span> ## <span class="h2">主題2:操作PBI原則程序</span> <a href="https://hackmd.io/_uploads/ryyDpRXuF.png"><span class="img100">![](https://hackmd.io/_uploads/ryyDpRXuF.png)</span></a> ### A. PQ:資料清理 <span style="color:#5E00BC; display:block;font-weight:bold;font-size:1.1em">🔷 課程單元主題</span> <a href="https://hackmd.io/_uploads/B1vA3CmuF.png"><span class="img40">![](https://hackmd.io/_uploads/B1vA3CmuF.png)</span></a> #### <span class="h4">(1) 資料載入及修改原檔連結</span> <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 資料匯入</span> <a href="https://hackmd.io/_uploads/BksR6Cm_Y.png"><span class="img100">![](https://hackmd.io/_uploads/BksR6Cm_Y.png)</span></a> <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 資料重新連結</span> 【檔案】>【選項及設定】>【資料來源設定】 <a href="https://hackmd.io/_uploads/HySg0AXut.png"><span class="img100">![](https://hackmd.io/_uploads/HySg0AXut.png)</span></a> #### <span class="h4">(1) 儲存格拆分</span> <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 S1:分割儲存格</span><span style="padding-left:2em">指令:【轉換】>【分割資料行】>【依分隔符號】</span> <a href="https://hackmd.io/_uploads/rkqEA0XdK.png"><span class="img100">![](https://hackmd.io/_uploads/rkqEA0XdK.png)</span></a> <a href="https://hackmd.io/_uploads/rknK0C7uK.png"><span class="img100">![](https://hackmd.io/_uploads/rknK0C7uK.png)</span></a> <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 S2:樞紐資料行</span><span style="padding-left:2em">指令:【轉換】>【樞紐資料行】> 如圖步驟執行</span> <a href="https://hackmd.io/_uploads/rkI7114dK.png"><span class="img100">![](https://hackmd.io/_uploads/rkI7114dK.png)</span></a> 顯示結果 <a href="https://hackmd.io/_uploads/BJQdkkV_F.png"><span class="img60">![](https://hackmd.io/_uploads/BJQdkkV_F.png)</span></a> #### <span class="h4">(2) 中英文拆分</span> <span style="color:#b71c1c; display:block;">【目的】</span> <a href="https://hackmd.io/_uploads/B1t91yNuY.png"><span class="img60">![](https://hackmd.io/_uploads/B1t91yNuY.png)</span></a> <span style="color:#b71c1c; display:block;">【步驟】</span>S1:抓取中文文字語法,新增【自訂資料行】>公式為【 = Text.Select([產品],{″一″..″龜″})】 ``` = Text.Select([商品],{"一".."龜"}) ``` <a href="https://hackmd.io/_uploads/B1Q-lJEuF.png"><span class="img100">![](https://hackmd.io/_uploads/B1Q-lJEuF.png)</span></a> S2:抓取英文,新增【自訂資料行】><br>公式為【 = Text.Trim ( Text.Select ( [產品] , {″ a ″…″ z ″,″ A ″…″ Z ″,″ ″,″ . ″,″ ‘ ″}))】<br>移除前後多餘空格,因範例中英文有【’】及【.】 ``` = Text.Trim ( Text.Select ( [商品] , {"a".."z","A".."Z"," ",".","'"})) ``` <a href="https://hackmd.io/_uploads/r1K7x14_t.png"><span class="img100">![](https://hackmd.io/_uploads/r1K7x14_t.png)</span></a> <span style="color:#5800cc; font-size:1.2em;">S3:也可以移除中文字,來取得英文</span> ``` = Text.Remove([商品],{"一".."龜"}) ``` <span style="text-align:center; display:block; width:70%;margin:20px auto;">Text.Select ( ) 和Text.Remove ( )</span> |提取項目| M函數| |-|-| |各種特殊符號、0-9數值、<br>所有大小寫英文字母|Text.Select ( [文本資料行] , { ″ ″ .. ″~″})| |數值0-9|Text.Select ( [文本資料行] , { 0 .. 9})| |文字0-9|Text.Select ( [文本資料行] , { ″0″ .. ″9″})| |英文大寫|Text.Select ( [文本資料行] , { ″A″ .. ″Z″})| |英文小寫|Text.Select ( [文本資料行] , { ″a″ .. ″z″})| |英文大/小寫|Text.Select ( [文本資料行] , { ″A″ .. ″z″})| |中文|Text.Select ( [文本資料行] , { ″一″ .. ″龜″})| <br> #### <span class="h4">(3) 比對數據</span> <span style="color:#b71c1c; display:block;">【說明】</span>在Power Query中【合併查詢】,與Vlookup查詢功能特性相同,透過PQ可以創建兩個表之間的關係找出相對應的匹配列,並依要求顯示其不同列表。 <a href="https://hackmd.io/_uploads/HJd6xJE_t.png"><span class="img100">![](https://hackmd.io/_uploads/HJd6xJE_t.png)</span></a> <span style="color:#b71c1c; display:block;">【範例】</span> <a href="https://hackmd.io/_uploads/SyEM-kNut.png"><span class="img100">![](https://hackmd.io/_uploads/SyEM-kNut.png)</span></a> <span style="color:#b71c1c; display:block; background:#fce4ec; text-align:center; line-height:2em">【Youtube】</span> 畫面模糊請自行修改解析度 {%youtube 1ga7jndMp_Q %} <span style="color:#b71c1c; display:block;">【主要步驟】</span> S1:將兩表進行比對 <a href="https://hackmd.io/_uploads/BJlo-1V_F.png"><span class="img100">![](https://hackmd.io/_uploads/BJlo-1V_F.png)</span></a> S2:比對結果,進行展開 <a href="https://hackmd.io/_uploads/SyInWJEdt.png"><span class="img100">![](https://hackmd.io/_uploads/SyInWJEdt.png)</span></a> S3:利用條件判斷,員工到職情形 <a href="https://hackmd.io/_uploads/BkFp-kEOK.png"><span class="img100">![](https://hackmd.io/_uploads/BkFp-kEOK.png)</span></a> ### B. 模型:資料建模 <span style="color:#000099; background:#cce5ff; display:block; line-height:2em;font-size:1.2em">(1) 模型類型介紹</span> <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 維度表(Dimension Table)</span> <span style="display:block; padding-left:2em;">維度表是維度模型的基本元素<br> 例:客戶名單、商品名單、員工名單</span> <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 事實表(Fact Table)</span> <span style="display:block; padding-left:2em;">事實表表示對分析主題的度量。<br>比如一次購買行為我們就可以理解為是一個事實。<br> 每個整體數據結構中,都包含一個或者多個維度數據表<br> 例:訂單銷售情形、員工考績評核表 </span> <span style="color:#000099; background:#cce5ff; display:block; line-height:2em;font-size:1.2em">(2) 模型排版</span> <a href="https://hackmd.io/_uploads/BkzXGJEdF.png"><span class="img100">![](https://hackmd.io/_uploads/BkzXGJEdF.png)</span></a> <span style="color:#000099; background:#cce5ff; display:block; line-height:2em;font-size:1.2em">(3) 課程模型</span> <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 課程模型排列示意圖</span> <a href="https://hackmd.io/_uploads/Hy8SG14dY.png"><span class="img100">![](https://hackmd.io/_uploads/Hy8SG14dY.png)</span></a> <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 課程範例總結構</span> <a href="https://hackmd.io/_uploads/H1ndDwNuK.png"><span class="img100">![](https://hackmd.io/_uploads/H1ndDwNuK.png)</span></a> <span style="color:#fff; background:#b90303;font-size:1.1em;padding:3px; display:inline-block; border-radius:5px;">Step1:</span> <a href="https://hackmd.io/_uploads/Hk1PGyEOY.png"><span class="img100">![](https://hackmd.io/_uploads/Hk1PGyEOY.png)</span></a> <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 加上日期表結構</span> * 加入【日曆表】並將資料庫中日期欄位,進行連結 * 將【日期表】進行PBI告知 <a href="https://hackmd.io/_uploads/rkeoGJ4uK.png"><span class="img50">![](https://hackmd.io/_uploads/rkeoGJ4uK.png)</span></a> ### C. 資料:資料分析計算 <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 新增資料行與量值</span> 有兩個地方可以輸入DAX公式:新增資料行與量值 <a href="https://hackmd.io/_uploads/rJkAz1Eut.png"><span class="img40">![](https://hackmd.io/_uploads/rJkAz1Eut.png)</span></a> <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 兩軟體之間名詞差異</span> <span style="color:#bf0303;">使用PBI計算必須抛棄Excel儲存格計算的思維模式,在PBI中的資料表是以【資料行】方式儲存。</span> |Excel|PowerBI| |-|-| |工作表|資料表| |欄|資料行| ||量值:即以【資料表】或【資料行】進行計算| <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 建議</span> |新增資料行:| |-| |<span style="font-weight:bold;">缺點</span>:使用時浪費記憶體 <br><span style="font-weight:bold;">優點:</span>使用Excel公式習慣的人,傾向使用容易理解<br><span style="font-weight:bold;">使用時機:</span><br>&#12288;▷ 將列內容放入篩選器、交叉分析篩選器、行、列<br>&#12288;▷ 需要利用新增的資料行與其他表建立關係時<br>| |量值:| |<span style="font-weight:bold;">缺點:</span><br>&#12288;▷ 公式書寫較難以理解<br>&#12288;▷ 不可使用於交叉分析篩選器<br><span style="font-weight:bold;">優點:</span><br>&#12288;▷ 不浪費記憶體,只有被拖到【報告】中才會執行運算<br>&#12288;▷ 可以重覆使用的計算式<br><span style="font-weight:bold;">使用時機:</span><br>&#12288;▷ 需要重覆使用的計算式<br>&#12288;▷ 是否根據上下文動態運算| <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 新增資料行</span> 計算方式與Excel公式類似,只是一個公式對應【列】計算 <span style="color:#fff; background:#b90303;font-size:1.1em;padding:3px; display:inline-block; border-radius:5px;">Step2:</span><span style="color:#1b51ff;"> 新增資料行</span> ``` 在 『銷售情形』 中, 單價 = RELATED ( '產品表'[價格] ) 單筆銷售額 = [單價] * [數量] ``` <span style="text-align:right;font-size:1.1em;padding:3px; display:block; border-radius:5px;">👆[連結:Related函式說明](https://hackmd.io/nznWZqdrQ5G6ZJaR70TJGw?both?view#1-Related)</span> <span style="color:#fff; background:#b90303;font-size:1.1em;padding:3px; display:inline-block; border-radius:5px;">Step3:</span><span style="color:#1b51ff;"> 進行欄位確認</span> 儀表板【報告】展現:加入 【<a href="https://hackmd.io/_uploads/H1bwm1E_t.png"><span class="img4">![](https://hackmd.io/_uploads/H1bwm1E_t.png)</span></a>矩陣】,各分區銷售情形 <a href="https://hackmd.io/_uploads/SJYQ71VdK.png"><span class="img60">![](https://hackmd.io/_uploads/SJYQ71VdK.png)</span></a> :::info 討論: * 【報告】界面介紹 * 圖表上方工具列的意義,並如何分層顯示值? * 為什麼【單筆銷售額】欄位,會加總計算? * 為什麼【數量】欄位,無法計算? * 如何調整值的小數位數? ::: <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 量值 (Measure)</span> 量值與新增資料行,在DAX寫法上並沒有本質不同,形式上的不同於量值會依上下文環境的不同而計算結果而不同。 <span style="color:#fff; background:#b90303;font-size:1.1em;padding:3px; display:inline-block; border-radius:5px;">Step4:</span> <span style="color:#1b51ff;"> 量值建立</span> <br> |在 ‘銷售情形’ 中,| |-| |銷售數量 = SUM ( [數量] ) &#12288;&#12288;&#12288;&#12288;▶修改為:&#12288;銷售數量 = SUM(‘銷售資料表'[數量])| |銷售額 = SUMX ( ‘銷售資料表’ , Related ( ‘產品表’ [價格] ) * [數量] )| <span style="text-align:right;font-size:1.1em;padding:3px; display:block; border-radius:5px;">👆[連結:SumX函式說明](https://hackmd.io/nznWZqdrQ5G6ZJaR70TJGw?both?view#4-SUMX)</span> 【報告】加入<span style="text-align:center; margin:20px auto;"><a href="https://hackmd.io/_uploads/rkVZNkEdF.png"><span class="img4">![](https://hackmd.io/_uploads/rkVZNkEdF.png)</span></a></span>【多列卡片】,並設定數值格式 <a href="https://hackmd.io/_uploads/H10VEyVOY.png"><span class="img70">![](https://hackmd.io/_uploads/H10VEyVOY.png)</span></a> <span style="color:#fff; background:#b90303;font-size:1.1em;padding:3px; display:inline-block; border-radius:5px;">Step5:</span><span style="color:#1b51ff;"> 建立量值彙集表</span> <a href="https://hackmd.io/_uploads/BJvuEyEdK.png"><span class="img70">![](https://hackmd.io/_uploads/BJvuEyEdK.png)</span></a> :::danger 討論: * 螢幕右側欄位中,【圖示】意義 * 為什麼量值搬動時,數據卻發生錯誤? <a href="https://hackmd.io/_uploads/r10hNy4uK.png"><span class="img50" style="border:1px solid #000">![](https://hackmd.io/_uploads/r10hNy4uK.png)</span></a> ::: <span style="color:#fff; background:#b90303;font-size:1.1em;padding:3px; display:inline-block; border-radius:5px;">Step6:</span><span style="color:#1b51ff;"> 基本常用量值計算</span> |在 ‘M量值表’ 中,| |-| |分店數量 = CountRowS ( ‘分店’ ) <br>購買會員數量 = DistInctCount ( ‘銷售資料表’ [顧客ID] ) <br>訂單數量 = DistInctCount ( ‘銷售資料表’ [訂單編號] )| ``` 分店數量 = COUNTROWS('T分店') 購買會員數量 = DISTINCTCOUNT('T銷售情形'[顧客ID]) 訂單數量 = DISTINCTCOUNT('T銷售情形'[訂單編號]) ``` <span style="text-align:right;font-size:1.1em;padding:3px; display:block; border-radius:5px;">👆[連結:計數函式說明](https://hackmd.io/nznWZqdrQ5G6ZJaR70TJGw?both?view#2-CountRows)</span> ### D. 報告:儀表板展現 <span style="color:#5E00BC; display:block;font-weight:bold;font-size:1.1em">🔶 討論</span> * 基本圖表美化界面介紹 * 如何標示最大值線平均線 <a href="https://hackmd.io/_uploads/rJFrr14OK.png"><span class="img100">![](https://hackmd.io/_uploads/rJFrr14OK.png)</span></a> <span style="color:#5E00BC; display:block;font-weight:bold;font-size:1.1em">🔶 範例結果</span> ## <span class="h2">主題3:常用技巧</span> ### A. 告知PBI的重要性 <span style="color:#000099; background:#cce5ff; display:block; line-height:2em;font-size:1.2em">(1) 新增日期表</span> 新增日期表,告知PBI的重要性 <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 加入原因</span> <a href="https://hackmd.io/_uploads/H1WqsO_ki.png"><span class="img100">![](https://hackmd.io/_uploads/H1WqsO_ki.png)</span></a> <a href="https://hackmd.io/_uploads/rkDtByE_F.png"><span class="img100">![](https://hackmd.io/_uploads/rkDtByE_F.png)</span></a> <a href="https://hackmd.io/_uploads/rJfoHkNut.png"><span class="img100">![](https://hackmd.io/_uploads/rJfoHkNut.png)</span></a> <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 設定方法</span> S1:在 ‘ 銷售情形 ‘ 中,將【訂單日期】資料行改為日期格式 <a href="https://hackmd.io/_uploads/S116Hy4dt.png"><span class="img100">![](https://hackmd.io/_uploads/S116Hy4dt.png)</span></a> S2:利用Dax語法,加入【日期表】TdateTable <a href="https://hackmd.io/_uploads/SyqCrJ4_F.png"><span class="img70">![](https://hackmd.io/_uploads/SyqCrJ4_F.png)</span></a> ``` TdateTable = ADDCOLUMNS( CALENDAR( MINX('T銷售情形',[訂單日期]), MAXX('T銷售情形',[訂單日期])), "年度", YEAR ( [Date] ), "季度", FORMAT ( [Date], "第Q季" ), "月份", FORMAT ( [Date], "MM" ), "日",FORMAT ( [Date], "DD" ), "年度季度", FORMAT ( [Date], "YYYY" ) & "Q" & FORMAT ( [Date], "Q" ), "年度月份", FORMAT ( [Date], "YYYY/MM" ), "星期英", FORMAT ( [Date], "ddd" ), "星期中", SWITCH(WEEKDAY([Date],2),1,"一",2,"二",3,"三",4,"四",5,"五",6,"六",7,"日") ) ``` S3:將日期表【TdateTable】設定為【標示為日期資料表】並建立模型關係 <a href="https://hackmd.io/_uploads/H1_Z814uY.png"><span class="img90">![](https://hackmd.io/_uploads/H1_Z814uY.png)</span></a> <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 加入原因</span> * 說明建立日期表的優點,並記得建立模型關係 * 如何建立大綱階層及隱藏項目 * 【顯示沒有資料的項目】在PQ篩選與PBI篩選差異 <a href="https://hackmd.io/_uploads/BJAV8kVuY.png"><span class="img100">![](https://hackmd.io/_uploads/BJAV8kVuY.png)</span></a> <span style="color:#000099; background:#cce5ff; display:block; line-height:2em;font-size:1.2em">(2) 依資料行排序</span> <span style="color:#000099; background:#cce5ff; display:block; line-height:2em;font-size:1.2em">(3) 進行資料類型確認</span> <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 未確認欄位格式的影響</span>資料行的資料類型通常會自動判斷,若PBI未判斷到則會影響後續數據計算。 例:範例中【訂單日期】及【數量】,PBI判斷為文字類型。 <a href="https://hackmd.io/_uploads/HJAY8kEOY.png"><span class="img100">![](https://hackmd.io/_uploads/HJAY8kEOY.png)</span></a> 造成日期無法自動計算【年/季/月/日…】;數量無法加總的影響。 <a href="https://hackmd.io/_uploads/Hk8jI1V_Y.png"><span class="img100">![](https://hackmd.io/_uploads/Hk8jI1V_Y.png)</span></a> <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 調整正確資料類型的結果</span> <a href="https://hackmd.io/_uploads/HJFpLkEOY.png"><span class="img100">![](https://hackmd.io/_uploads/HJFpLkEOY.png)</span></a> ### B. 地圖加入 :::danger 執行重點:進行【縣市】欄位設定為【地址】類型確認 ::: <a href="https://hackmd.io/_uploads/rkG8vyEdY.png"><span class="img100">![](https://hackmd.io/_uploads/rkG8vyEdY.png)</span></a> ### C. 圖片加入 :::danger 執行重點: (1) 進行【圖片連結網址】欄位設定為【影像URL】類型確認 (2) 如何在OneDrive圖片連結 ::: <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 欄位類型確認</span> <a href="https://hackmd.io/_uploads/BJJKv1EdF.png"><span class="img100">![](https://hackmd.io/_uploads/BJJKv1EdF.png)</span></a> 頁面顯示結果 <a href="https://hackmd.io/_uploads/Sy7jvyVOK.png"><span class="img100">![](https://hackmd.io/_uploads/Sy7jvyVOK.png)</span></a> <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 如何在OneDrive圖片連結</span> <a href="https://hackmd.io/_uploads/ByD6vJNut.png"><span class="img100">![](https://hackmd.io/_uploads/ByD6vJNut.png)</span></a> ### D. 主題套用 <a href="https://hackmd.io/_uploads/Bk1euJ4uK.png"><span class="img60" style="border:1px solid #000">![](https://hackmd.io/_uploads/Bk1euJ4uK.png)</span></a> ### E. 工具提示 如下圖所示:工具提示,即透過滑鼠懸停於圖表時,自動顯示其數據情形,但如果覺得顯示數據需要更多數據顯示或利用圖表更加優化 <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">A. 增加更多數據</span><a href="https://hackmd.io/_uploads/r1MTt1VOY.png"><span class="img100">![](https://hackmd.io/_uploads/r1MTt1VOY.png)</span></a> <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">B. 利用頁面讓工具提示更加直覺</span>結果如下: <a href="https://hackmd.io/_uploads/H1F-qkE_Y.png"><span class="img100">![](https://hackmd.io/_uploads/H1F-qkE_Y.png)</span></a> S1:設置工具提示頁面:新增頁面,加入想要顯示的數據圖表,設置如下圖所示 <a href="https://hackmd.io/_uploads/HkLr5k4OF.png"><span class="img100">![](https://hackmd.io/_uploads/HkLr5k4OF.png)</span></a> S2:針對想要設定【工具提示】的圖表,進行設定,如下圖所示設置 <a href="https://hackmd.io/_uploads/H1iOqkNut.png"><span class="img100">![](https://hackmd.io/_uploads/H1iOqkNut.png)</span></a> ### F. 互動分析1:交叉分析篩選器 PowerBI提供互動分析功能之一:交叉分析篩選器 :::danger 執行重點: (1) ![](https://i.imgur.com/ALiBTAw.png):是否每頁面都要同步篩選 (2) ![](https://i.imgur.com/9dflFTS.png):是否每頁面都要顯示此交叉分析篩選器 ::: <a href="https://hackmd.io/_uploads/rya6cJNOK.png"><span class="img100">![](https://hackmd.io/_uploads/rya6cJNOK.png)</span></a> ### G. 互動分析2:篩選窗格 PowerBI提供互動分析功能之一:【篩選】提供探索數據之間的關係 |篩選方式|影響範圍|設置位置| |-|-|-| |圖表篩選|點選某一圖表數據,<br>依條件進行篩選|🔹【視覺效果】篩選| |頁面篩選|單一頁面|🔹【交叉分析篩選器】<br>🔹【頁面篩選】| |全報表篩選|整個報告|🔹【交叉分析篩選器】<br>🔹【所有頁面篩選】| <a href="https://hackmd.io/_uploads/BJGZiyVdt.png"><span class="img100">![](https://hackmd.io/_uploads/BJGZiyVdt.png)</span></a> ### H. 鑽研 「跨報表鑽研」功能,您可依據內容,從某個報表跳到相同 Power BI 服務工作區或應用程式中的另一個報表。 也可以使用跨報表鑽研來連接具有相關內容的兩個或多個報表,以及使用跨報表連線來傳遞篩選內容。 S1:設定想要被作為其他報表【作為鑽研目標】的頁面,並加入關鍵欄位 <a href="https://hackmd.io/_uploads/HkmvoyNuF.png"><span class="img50">![](https://hackmd.io/_uploads/HkmvoyNuF.png)</span></a> S2:因有與S1相同的【關鍵欄位】,則可在圖表上按右鍵,找到【鑽研】目標的頁面 <a href="https://hackmd.io/_uploads/ByksikVuY.png"><span class="img70">![](https://hackmd.io/_uploads/ByksikVuY.png)</span></a> ### I. 條件格式設定 此功能與Excel功能相同,達成報表自動化功能,隨時預警提醒數據異常。 <a href="https://hackmd.io/_uploads/SkTCiyV_F.png"><span class="img100">![](https://hackmd.io/_uploads/SkTCiyV_F.png)</span></a> ### J. 其他圖表 <a href="https://hackmd.io/_uploads/rk6Q3JE_t.png"><span class="img100">![](https://hackmd.io/_uploads/rk6Q3JE_t.png)</span></a> <br><br><br> ## <span class="h2">主題4:DAX</span> ### 1. 基礎函式 #### <span class="h4">(1) Related</span> <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">語法</span><span style="display:block; padding-left:2em;">RELATED ( ‘資料表名稱’ [欄位名稱] )</span><span style="padding-left:2em;">[DAX函式官網連結:Related](https://docs.microsoft.com/zh-tw/dax/related-function-dax) </span> <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">目的</span> * 載入非現有資料表的欄位 <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">注意事項</span> * 需要目前資料表與載入的資料表之間有關聯性存在。 * 如果關聯性不存在,則必須建立關聯性。 <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">範例</span> S1:確認資料表間的關聯性 <a href="https://hackmd.io/_uploads/ryMY2yEOY.png"><span class="img70">![](https://hackmd.io/_uploads/ryMY2yEOY.png)</span></a> S2:在【銷售情形】資料表中加入產品表中的[價格]欄位 ``` 單價 = RELATED ( '產品表' [價格] ) ``` #### <span class="h4">(2) CountRows</span> <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">語法</span><span style="display:block; padding-left:2em;">CountRows ( 資料表 )</span><span style="padding-left:2em;">[DAX函式官網連結:CountRows](https://docs.microsoft.com/zh-tw/dax/countrows-function-dax) </span> <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">目的</span> * 包含要計算的資料列資料表名稱,或傳回資料表的運算式 * 計算資料表的列數 <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">注意事項</span> * 只要沒有要彙總的資料列,則函式會傳回空白。 * 但若有資料列,卻都不符合指定的準則時,則函式會傳回 0。 如果找不到符合條件的資料列,則 Excel 也會傳回零。 <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">範例</span> 依【分店】資料表中,計算總共有幾家分店 ``` 分店數量=COUNTROWS('分店') ``` #### <span class="h4">(3) DistinctCount</span> <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">語法</span> <span style="display:block; padding-left:2em;">DistinctCount ( [欄位名稱] )</span><span style="padding-left:2em;">[DAX函式官網連結:DistinctCount](https://docs.microsoft.com/zh-tw/dax/distinctcount-function-dax) </span> <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">目的</span> * 計算資料行中的相異值數目 * 不重覆計數 <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">注意事項</span> * 當函式找不到要計算的資料列時,則會傳回 BLANK;否則會傳回相異值的計數。 * DistinctCount 函式包含空白值。 若要略過空白值,則請使用 DistinctCountBlank 函式 <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">範例</span> 在【銷售情形】資料表中,計算會員有多少位曾經購買商品,但因同一位會員會購買多次,故計算不重覆的筆數 ``` 購買會員數量=DISTINCTCOUNT('銷售資料表'[顧客ID]) ``` #### <span class="h4">(4) SUMX</span> <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">何謂疊代函式</span> * 👆[「疊代」或是在「迭代」?](https://ryo6.medium.com/%E8%AB%8B%E5%95%8F%E5%88%B0%E5%BA%95%E6%98%AF%E5%9C%A8-%E7%96%8A%E4%BB%A3-%E6%88%96%E6%98%AF%E5%9C%A8-%E8%BF%AD%E4%BB%A3-f5bdba4c31eb) * 👆[何謂疊代?](https://zh.wikipedia.org/wiki/%E8%BF%AD%E4%BB%A3) <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">語法</span> <span style="display:block; padding-left:2em;">SumX ( ‘資料表名稱’ , 運算式 )</span><span style="padding-left:2em;">[DAX函式官網連結:SumX](https://docs.microsoft.com/zh-tw/dax/sumx-function-dax) </span> <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">目的</span> * 傳回為資料表中每個資料列得出的運算式加總 <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">注意事項</span> * 只會計算資料行中的數字。 空白、邏輯值和文字則會予以忽略 <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">使用時機</span> * <span style="color:#b10606; ">如果想要篩選所加總的值,則使用 SUMX 函式,並指定要用來加總的運算式。</span> * <span style="color:#b10606; ">SUM 函式類似於 Excel 的同名函式,不同之處在於該函式是以資料行作為參考。</span> * <span style="color:#b10606; ">如果不需要篩選資料行,則使用 SUM 函式</span> <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">範例</span> S1:在【銷售情形】資料表,想要計算[數量]*[價格]後,最後再將其銷售額進行加總,此時會寫入公式(如下範例),但會發生錯誤無法計算,其原為SUM函式內不可寫入運算式,僅能加總既定欄位 ``` 銷售額 = SUM( [價格]*[數量] ) ※產生錯誤,因SUM函數內不可寫入運算式 ``` S2:故改為SUMX函式 ``` 銷售額=SUMX('銷售資料表',RELATED('產品表'[價格])*[數量]) ``` ### 2. SelectedValue <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">語法</span> <span style="display:block; padding-left:2em;">SelectedValue ( 1️⃣ [欄位名稱] , 2️⃣ 選擇性計算 )<br> 1️⃣ 現有資料行名稱,不可為運算式 2️⃣ (選擇性) 當條件篩選為 零 或者出現多個篩選結果時的計算方式。 未提供時,預設值為 Blank( )</span> <span style="padding-left:2em;">[DAX函式官網連結:SelectedValue](https://docs.microsoft.com/zh-tw/dax/selectedvalue-function) </span> <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">目的</span> * 依【單一】參數條件進行篩選,回傳其欄位值 * 若欄位名稱依篩選條件結果,沒有值 或 回傳一個以上值結果,<br>則依 2️⃣ 計算其結果,若 省略則返回空白Blank( ) <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">注意事項</span> * 必須提供【單一】參數條件 <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">使用時機</span> * 可以搭配【交叉分析篩選器】提供單一條件進行篩選 <span style="color:#5E00BC; display:block;font-weight: bolder;line-height:2em;font-size:1.2em;">範例:動態切換數據單位</span> 假設進行銷售額分析,利用【交叉分析篩選器】動態的選擇千元、萬元的單位進行顯示 👆[範例:動態切換數據單位範例連結:連結](https://hackmd.io/nznWZqdrQ5G6ZJaR70TJGw?both?view#1-動態切換數據單位) <br><br><br> ## <span class="h2">主題5:進階技巧</span> ### 1. 動態切換數據單位 S1:【常用】之下【輸入資料】手動建立一個單位表,用於交叉分析選器,可以命名為【CT金額單位】 <a href="https://hackmd.io/_uploads/rJfPT14uK.png"><span class="img100">![](https://hackmd.io/_uploads/rJfPT14uK.png)</span></a> S2:新增度量值,為了能按篩選的單位顯示銷售額 ``` 原先銷售金額計算:   銷售額 = SUMx('銷售情形',RELATED('產品表'[價格])*[數量]) ``` 為了能按篩選的單位顯示銷售額,把度量值如下, ``` 銷售額 單元切換 = [銷售額] / SELECTEDVALUE('T金額單位'[單位值] , 1) ``` 1. <span style="color:#b10606; ">SELECTEDVALUE函數可以根據交叉分析選器的選擇來動態的更換分母。</span> 1. <span style="color:#b10606; ">SELECTEDVALUE函數的最後一個參數設置為1,是防止交叉分析選器沒有選擇時出現錯誤。如果交叉分析選器沒有做任何選擇,就返回1,實際上就是以元來顯示。</span> S3:建立篩選器及表 / 圖 <a href="https://hackmd.io/_uploads/Hyx59TyVOF.png"><span class="img50">![](https://hackmd.io/_uploads/Hyx59TyVOF.png)</span></a>