タグ別アーカイブ: Power BI

コンテキスト遷移

コンテキスト遷移～DAXの何故こうなるのか？を解決する～

2024年2月25日 analytic-vba コメントをどうぞ

【このコンテキスト遷移の理解がDAX自体の理解の近道です】

DAXを使用していて「何故こうなるの？」ということに出会うことがあります

「何故こうなるの？」の不思議の一つが「コンテキスト遷移」です

早速、事例で見てみましょう

上の画像の２つの列は「大阪」で値が2300と1200と言う風に違いがあります

実際にメジャーの中身を見ると些細な違いしかありません

・MAXX_NoCal

　　MAXX_NoCal = MAXX(‘担当者’,SUMX(‘売上’,’売上'[金額]))

・MAXX_withCal

　　MAXX_withCal = MAXX(‘担当者’,CALCULATE(SUMX(‘売上’,’売上'[金額])))

MAXXの第二引数にCALCULATE関数があるかないかだけです

ところが、このCALCULATE関数のあるなしがとても大きいのです

CALCULATE関数があるメジャーは2300⇒1200となり、何らかの形でデータの絞り込み（フィルター）が行われています

今回の記事ではこの絞り込みが有効になる「コンテキスト遷移」の仕組みについて解説していきます

DAXで計算したいこと

今回は２つのテーブルを使用します

１つ目は担当者のテーブル、2つ目は売上テーブルです

売上テーブルは同一担当者が複数登場しています

DAXを使用して、この２つのテーブルを組み合わせて、東京と大阪で最大の売上をあげた担当者の金額を計算します

MAX関数での計算

「東京と大阪で最大の売上・・・」というように「最大」というキーワードが出てくるので、MAX関数を使用するのでは・と思った読者の方も多いと思います

では早速、MAX関数で計算してみましょう

MAX = MAX(‘売上'[金額])

そうすると上の画像のように、思ったよりも小さい金額算出されます

これはそもそも集計の単位が違っています

ですので、まずは担当者テーブルで新しい列を作成し、担当者別に売上金額を算出してみます

新しい列で担当者別の売上を算出した上で、MAX関数を使用してみます

これで意図とした通りの結果が出ました

ポイントは新しい列を作成する時、CALCULATE関数を使用していることです

画像に alt 属性が指定されていません。ファイル名: image-63.png

仮に、上の画像のCALCULATE関数を取り除いたらどうなるでしょうか？

全ての行の合計が算出されてしまいます

つまり、CALCULATE関数を使うことで遷移（全ての行⇒行別）が起こっているのです

MAXX関数/行評価

上のMAX関数の事例ではメジャーを作成するまえに、新しい列を作成しました

今度はMAXX関数を使用して、直接メジャーで担当者ごとの最高金額を大阪と東京で算出したいと思います

MAXX関数では単純に最大値を算出するのではなく、行別に式を評価した上で最大値を算出します

他の言葉で言い換えると、条件に合う「行の中」で最大値を算出してくれます

ちなみに、大阪と東京の合計金額は次の画像のようになっています

では、話を戻してMAXX関数を実際に使用して大阪と東京の担当者別の最大値を算出してみましょう

通常の合計金額と同じ結果になってしまっています

つまり、大阪もしくは東京という「行別」だけが考慮された計算金額になってしまっています

MAXX関数の第一引数はMAXX(’担当者’となっているのに「担当者の行別」は考慮されていません

これは、前に例に挙げた下の画像と同じ現象が起こっています

画像に alt 属性が指定されていません。ファイル名: image-65.png

この時はCALCULATE関数を使用して「行別への遷移」を起こしました

実は、今回も同じようにCALCULATE関数を使用すれば正しく算出されます

CALCULATE関数を使用することで、今回もMAXX関数内で「行別への遷移」を起こしているわけです

これで、DAXは何故こうなるのか？の謎が一つが解けたと思います

メジャー使用による行評価

番外編として、次の事例も紹介したいと思います

上の画像の一番右の式はCALCULATE関数を使用していません

MAXX_NoCal_withMEASURE = MAXX(‘担当者’,[売上合計])

その代わり、第二引数に[売上合計]というメジャーを使用しています

ですが、CALCULATE関数を使用したのと同じ結果がでています

これはメジャーを使うこと自体で、CALCULATE関数を使用するのと同等の効果があることを意味します

たとえメジャーの中身と同じ内容を関数の引数で使用しても、違う結果が出てしまうのはこの為です

＜まとめ＞

今回はCALCULATE関数を使用して、行別の評価を関数内で受け渡す「コンテキスト遷移」について解説しました

この概念を理解すると、格段にDAXの活用の幅が広がります

ぜひマスターしておきましょう！！

にほんブログ村

ガッツの日記

脱エクセルについて考える～資金繰り表をPower Platformで作成～第9回

2024年1月3日 analytic-vba コメントをどうぞ

【エクセルは本当に便利なツール。但し、効率と効果を上げるには限界があります】

資金繰り表はエクセルに向いている業務の典型です

それは何故か？

上の画像の黄色のセルには数式が入っており、預金残高の予想残高を計算しています

但し、この数式を必要に応じて実際の預金残高「数値」で上書きすることができます

実はこれってエクセル以外のソフトだと難しいんです

「数式」と「数値」を共存できるエクセルは本当に便利なんです

ではエクセルで業務を続けていたらそれでいいのか？

それはそれで問題があります

下の画像の「2023/1/5」の売上高の欄には120と入っています

入力数字は120でも、実は120という数字を別な表で複数の顧客の数字を積み上げて入力されているかもしれません

それに、表に存在する日付/列単位が膨大になってくると、入力箇所を見つけるのも面倒です

つまり量が膨大になってくると、この資金繰り表の1セルを入力する作業がかなり面倒になってきます

その理由は一般的なエクセルの資金繰り表では「データ蓄積」「作業」「計算」「表示」が分かれていないからです

ですので、作業や計算スペースを作る関係上、データの並びが不規則になってしまったりしますし、入力しやすいようなスペースを作ることも困難です

更に言えば、資金繰り表のシートのデータを活用して他のシートで別の目的の表を作成しようとするとかなり複雑なものになってしまいます

実は、Power BIのDAXを使用すれば預金口座の残高も、実残高と予想残高を共存させながら計算できます

今回の記事ではPower BIとPower Appsを使用して、エクセルの資金繰り表の「データ蓄積」「作業」「計算」「表示」を分けて管理できる仕組みの概要を紹介します

そして、Power Platformの便利さに触れて頂けたら幸いです

ポイント

Power Platformを使って資金繰り表を作成するには、分解が結合がポイントです

データの分解・結合

預金残高は数式と数値を共存できるように、以下にデータを分けて管理します

・預金残高（実残高）

・入金

・出金

更にカレンダーテーブル

上記の４つをDAXで結合して使用します

今回はSharepointリストを使用します

入出力と表示の分解

今回は入出力はPower Appsで行います

Power Appsならページを分けて、売掛金や経費などの入出力専用ページを作成できます

そして、表示や主な計算はPower BIで行います

Power BIは集約計算の簡単にできるし、DAXで複雑な計算も行えますので、Power BIの機能もフル活用します

エクセルで日付毎に複数の顧客の売掛金を合計していた作業は、前述のテーブルを作成しておけば勝手にPower BIが行ってくれます

入出力と表示が分かれていると不便だろうという場合は、後述するPower BI内でPower Appsを使う方法もあります

預金残高を表示するDAXの作成

ここがエクセルの資金繰り表を他のソフトで行う最大の難所です

日付の管理がとても重要です

・実際に預金残高を入力した日付の残高⇒そのまま残高を表示

・預金残高を入力していない日付の残高⇒実際に預金口座を入力した日付以降の入出金を加味して計算

例えば、1月1日の残高が１００、1月2日の入金が１００、1月３日の出金が１００だったとします

この場合は

１月１日残高⇒１００/実績残高

１月２日残高⇒２００（１００＋１００）/予想残高

１月３日残高⇒１００（１００＋１００ー１００）/予想残高

となります

こちらを実際にDAX式に直したのが以下です

＊CashBalance:実際の預金残高、Receipt:入金、Disbursement:出金

預金残高 =

VAR currentDay=MAX(‘Calendar'[Date])

VAR maxDay=CALCULATE(MAX(‘CashBalance'[Date]),FILTER(All(CashBalance),’CashBalance'[Date]<=currentDay))

VAR cashAmount=SUMX(FILTER(ALL(‘CashBalance’),’CashBalance'[Date]=maxDay),’CashBalance'[CashBalance])

VAR cashReceipt=CALCULATE(SUM(‘Receipt'[Amount]),FILTER(All(‘Receipt’),’Receipt'[Date]>maxDay &&’Receipt'[Date]<=currentDay))

VAR　CashDisbursement=CALCULATE(SUM(‘Disbursement'[Amount]),FILTER(All(‘Disbursement’),’Disbursement'[Date]>maxDay &&’Disbursement'[Date]<=currentDay))

RETURN cashAmount+cashReceipt-cashDisbursement

今回の記事では詳細には解説を行いませんが、一見複雑なこの式の最大のポイントは「預金残高の最大日付：maxDay」を算出することです

冒頭のたとえを使って説明すると、今日が１月３日だとしたらその前に預金残高がテーブル：CashBalanceに貴重された日付の１月１日を算出するということになります

最大日付が算出できれば、おのずと入金金額を算出する期間と出金金額を算出する期間が求まります

ただ実際には、フィルターを意識してAll関数をうまく組み合わせていく必要があります

専用アプリによる預金残高表示

Power BIには様々なアプリ/ビジュアルがあります

今回は資金繰り表に活用できるビジュアルを１つ紹介します

その他のビジュアルの取得をクリックして「Calendar」と検索してください

すると「Beyondsoft Calendar」というビジュアルをダウンロードできます

こちらのビジュアルは日々の預金残高をカレンダー上で表示できるという優れものです

残高の状態によって色を変えたりもできます

エクセルではなくPower BIを使用することで、専用アプリを活用することもできるんです

Power BIとPower Appsの連携

Power AppsをPower BI内で使用したい場合は、こちらをクリックするところからはじめます

すると、Power Appsに使用するフィールドの入力ができるようになります

実は、こちらはなんでも構わないんです。取り合えば何か設定すればPower BI内で表示したいPower Appsのアプリを選択できるようになります

＜まとめ＞

今回は脱エクセルについて考えると称して、エクセルの資金繰り表をPower AppsとPower BIを連携させて作成する方法の概要を紹介しました

紹介したのはあくまで概要ですが、エクセル作業が改善されていく可能性を感じて頂けたら幸いです

現実に、エクセルは表計算ソフトですので大量のデータを処理するには向いていません

なので、Power PlatFormの仕組みを利用して「データ蓄積」「作業」「計算」「表示」を分解・結合できるようにすることはとても意義があります

そうは言っても、誰でも手軽に使えるエクセルと違ってPower BIのDAXなどが複雑であったりするので専門知識が必要です

それに当然、手間暇も必要です

ですので、最終的にはどの程度の量の業務をどんな風に変えたいかが一番のポイントなのかもしれませんね

にほんブログ村

分析

購入会員の使用金額によるランク分け＆ランク別使用額の分析

2023年8月21日 analytic-vba コメントをどうぞ

【エクセルの煩雑な作業不要のまま、数字の裏側にあるストーリーを炙り出せます！！】

今回の記事では、Power BIで売上金額を購入会員のそれまでの使用金額別に前期と比較できるようにします

＊上記の使用ランクは年間の金額ランク付けを行っています

購入会員に使用金額のラベルを付けて比較できるようにすることで「売上の増減の原因」「売上の傾向」を分析するのに有用な示唆を得ることができます

この方法のいいところは、気になるところをラベルから辿って深堀できることです

例えば、年間2万円未満の層が減っていると思ったらフィルターで2万円の層の会員番号にアクセスすることができます

しかも、分析作業はエクセルで行うことを考えたら遥かに楽です

エクセルで同じことをおこなおうとしたら、ユニークな会員番号及び使用金額、注文数などを数シートに抽出した上で紐づけ処理を行うしかありません

今回の記事の内容を一言で言い換えるとセグメント分析の方法の解説です

ぜひセグメント分析を効率化して、有用な分析を継続していきましょう！

使用データ

今回使用するのは注文データのみです

会員別データは使用しないまま作業を行えるようにします

*Calendarテーブルも使用します

最終的には、会員別に各値を集計したテーブルを作成します

ポイント

今回のポイントは時系列での日付整理です

日付を整理したうえで、各データを抽出して組み合わせる形になります

・使用月の開始日、終了日

・使用月前の1年間（判定期間）の開始日、終了日

時系列での日付整理にはEOMONTH関数を使用します

この関数を使用して、使用する各日付を変数に設定しておきます

今回の記事では2022年4月（と前年同月の2021年4月）を分析します

分析テーブル作成

SUMMRIZE関数

上記の各日付を使用し、前述のようなテーブルを作成します

画像に alt 属性が指定されていません。ファイル名: image-42-644x151.png

このテーブル作成は、SUMMRIZE関数を使用します

但し、画像右の「使用ランク」「月」は後から列を足します

SUMMRIZE関数のグループ化の切り口は「当月使用した会員番号」になります

こちらについては、変数でCALCULATETABLEを使用して事前に作成しておきます

VAR myTable= CALCULATETABLE
　　　　　　　　(

　　　　　　　　　　VALUES(OrderData1[Customer ID]), 　　　　　　

　　　　　　　　　　‘Calendar'[Date]>=DATEVALUE(firstDay),’Calendar'[Date]<=DATEVALUE(endDay)
　　　　　　　 )

この時に、会員番号の抽出範囲を時系列で整理した日付（firstDay、endday)を使用して指定するのもポイントの一つです

ここからはFILTER関数でうまく抽出範囲を指定しながら、SUMMRIZE関数の各列を作成します

次が実際の数式です

RETURN

SUMMARIZE(

①FILTER(‘OrderData1’,’OrderData1′[Customer ID] in myTable),

②’OrderData1′[Customer ID],

③ “当月使用金額”,

④

SUMX(FILTER( ‘OrderData1’,

　　　　’OrderData1′[Purchase Date]>=DATEVALUE(firstDay) && ‘OrderData1′[Purchase Date]<=DATEVALUE(endDay)

‘OrderData1′[Price]*’OrderData1′[Quantity]

⑤ “年間使用金額”,

SUMX(FILTER(‘OrderData1’,

　 ‘OrderData1′[Purchase Date]>=firstDay_Year && ‘OrderData1′[Purchase Date]<=endDay_Year

　　　’OrderData1′[Price]*’OrderData1′[Quantity]

⑥”年間注文数”,

COUNTROWS( FILTER( ‘OrderData1’,

　　　　’OrderData1′[Purchase Date]>=firstDay_Year && ‘OrderData1′[Purchase Date]<=endDay_Year

　　　　　)　

ここから上の数式のポイントだけを抜粋して解説します

①では使用元のテーブル指定しますが、「in」を使用して当月の会員のみの注文データ（myTable)に絞り込みます

②ではグループ化、集約の切り口を指定します

③④では、SUMX関数を使用して判定期間（当月前）の使用金額を抽出します

⑤⑥では、COUNTROWS関数を会員毎の判定期間での注文数を抽出します

IF関数

SUMMRIZE関数でテーブルを作成したら、IF関数でラベル分けを行います

IF関数は「新しい列」で追加します

IF関数の条件式（金額）はビジネスの状況に応じて適したものにする必要があります

こちらはあくまで事例です

使用ランク = IF(ISBLANK(‘2022年4月購入者分析'[年間使用金額]),”1.新規”,if(‘2022年4月購入者分析'[年間使用金額]<20000,”2.2万未満”,”3.2万以上”))

もし、前年のテーブルも作成して１つのマトリックス表の中で比較したい場合には、UNION関数で２つのテーブルを一緒にします

その際、テーブル内で各テーブルが区分できるように、各テーブルに「月」の列を追加しておきましょう

＜まとめ＞

今回は年間の使用金額でセグメント分析する方法を解説しました

様々なDAX関数を使用しますが、一番ポイントとなるのは「EOMONTH関数」で当月、判定期間（年間）の関連日付を整理することです

ぜひこの点は意識していきましょう

最後に、

今回の手法を使うと会員分析の精度は格段に上がります

更に精度を上げるには継続的に分析を行い、会員ランクの見直しやランク別の特徴の蓄積を行うことです

できたら、ランクを更に分解して「購入商品別」にも分類できるようにすると、もっと分析の精度が上がります

リレーションのツボ～クロスフィルターの方向を単一もしくは双方で調整

2023年8月3日 analytic-vba コメントをどうぞ

【クロスフィルターの方向を調整すると、リレーションの罠を回避することができます】

Power BIを始める時に壁に当たりやすいのが「リレーション」です

リレーションがスパゲティ状態になり、よくわからなくなったことがある人もいらっしゃるのではないでしょうか？

今回の記事ではこのスパゲティ状態を解読する、回避するヒントを「クロスフィルター」の観点で解説したいと思います

尚、当記事は理想のリレーションを作成する方法を解説しようとする意図ではなく、あくまでスパゲティ状態を解読、回避するヒントを「クロスフィルター」に絞って解説しようとしているということを予めご了承お願いします

ポイント

VLOOKUP関数はエクセル関数の中でも代表的な関数です

Power BIのリレーションも基本的には同じ内容です

ただ違う点が１点あります

それは影響範囲です

VLOOKUP関数を使用すると関連するのは２つの表のみです

VLOOKUP関数を使用した表とVLOOKUP関数が参照した表です

Power BIの場合は違います

例えば、次にょうなリレーションがあったとします

上の画像では「店名」と「来客数」はつながっていません

ところが、店名から作成したスライサーを動かすと来客数のマトリックス表も動きます

ここでもう一度、リレーションを確認してみましょう

「店名」と「店長」間は双方向でつながっています

店名から店長に影響を与えることもできるし、店長側から店名に影響を与えることができます

つまり、「店名⇒店長」の関係は存在します

更に「店長」と「来客数」の関係を見てみましょう

「店長」と「来客数」は単一の方向でつながっています

つまり「店長⇒来客数」の関係は存在します

ですので、「店名⇒店長⇒来客数」すなわち「店名⇒来客数」の関係が存在するのです

ここがVLOOKUP関数と違う点です

間接的につながりが表の間で広がっていくようになっています

矛盾の排除

リレーションをいじっていると、思ったようにテーブル（表）の間でリレーションが作成できないケースがあります

それはポイントで解説した「影響の拡大」により矛盾が生じている可能性あります

前述のリレーションを次のように変えてみましょう

「店名」と「店長」間のリレーションを削除し「店名」と「来客数」間でリレーションを作成しました

こうなると、店名と店長のスライサー間ではシンクロが発生しません

ここで、店番と店長間でリレーションを作成しようとすると「破線」になりリレーションが作成できません

これがリレーションの矛盾です

店番と店長間でリレーションができると、来客数へのパス/通り道が２つ存在してしまうからです

ではどうするか？

店名と来客数間のリレーションを削除し、ポイントで解説した内容に戻すことももちろんできます

次の内容では、「店名」と「来客数」のリレーションを残したままで「店名」と「店長」間をリレーションしたいと思います

単一⇒双方向

今、店長と来客数間は単一の方向でクロスフィルターが行われています

方向は「店長⇒来客数」です

こちらを双方向にします

すると来客数を通じて「店番⇒店長」間のリレーションが間接的に成立します

これで２つのスライサーもシンクロします

＜まとめ＞

今回はリレーションを使用していて陥りやすい箇所を、クロスフィルターの観点で回避する方法を解説しました

エクセルワークシート関数のVLOOKUP関数を使用するのと違い、Power BIでは影響箇所が間接的に飛び火していくのも大きなポイントです

逆に飛び火を利用できるようになると、Power BIを使いやすくなります

そうはいっても、まだまだリレーションの箇所は様々なポイントがまだまだあります

今後、また別な機会で「多対多」のリレーションなども含めて解説していきたいと思います

にほんブログ村

分析

移動平均をDAXで算出しつつパラメーターで変動させる

2023年7月23日 analytic-vba コメントをどうぞ

【意外と小難しい移動平均をDAXで簡単に、しかも算出日数をスライサーで変動させる】

移動平均の計算はエクセルなら関数を入れれば簡単に計算できます。ただPower BIだとDAXを使用する必要があります。DAXを使用する代わり、DAXにパラメーター機能を組み込めば算出日数を可変で表示できます

使用データ

下の画像のように、日別に金額が集計されたデータを活用します

同じ日のデータが複数存在するデータを使用する場合については、最後のまとめで解説します

何故、別途に解説するかというと、上記の画像の場合には普通の計算方法だと日別の平均金額が薄まってしまうからです

それでは本題に戻します

DATESINPERIOD関数

移動平均の計算では、基準の日から複数前までの期間が計算の対象になります

複数の日付を計算するのには、DATESINPERIOD関数が適しています

このDATESINPEROD関数を、条件付きで合計を計算するSUMX関数の平均版であるAVERAGEX関数と組み合わせます

下記は7日間の移動平均を算出するメジャー式です

移動平均_7日間 =

AVERAGEX(

DATESINPERIOD(①’Calendar'[Date],

②MAX(‘Calendar'[Date]),

③ -7,

④ DAY ),

[TotalSales])

①ではカレンダーテーブルに日付を指定します

②では計算期間の最終日を指定し、③で算出期間を計算します（30日間の場合は-30で指定します）

④は上記ではDAYで指定していますがMONTHでも指定できます

*[TotaSales]のように事前に合計を計算するメジャーを設定しておきます

パラメーターとの組み合わせ

それでは、上記で作成した移動平均を算出するメジャーをパラーメーターと組み合わせてみましょう

パラメーターでは値の範囲を設定します

パラメーターを設定したら、データの箇所に次のようなデータが追加されます

こちらの「パラメーターの値」を前述のDAXに組み込みます

これで移動平均の算出日数が可変になります

＜まとめ＞

今回はDAXを使用して移動平均を計算する方法を解説しました

更にパラメーターと組み合わせて移動平均の算出日数を可変にしましました

移動平均を可変にすることで、数値の見方も今までと違った切り口で見ることができるはずです

最後に、元データで同一日に複数のデータがある場合です

画像に alt 属性が指定されていません。ファイル名: image-57.png

この場合は、一旦、Calculate関数で合計を計算します。その後に割り算をします

７日間の移動平均を算出する場合には、上記の画像のように７で割ります

但しこの計算方法の場合には、グラフの最初の箇所がいびつになりますので注意が必要です

にほんブログ村

裏技

スライサーではできない操作をパラメーターで行う～メジャー連動～

2023年5月21日 analytic-vba コメントをどうぞ

【パラメーター機能の内容をメジャーに組み込めば、スライサーではできないような操作ができることがあります】

Power BIにパラメーターという機能をご存じでしょうか？例えばグラフの表示数が多すぎて、重要な数字が読み取れないことがあります。そんな時はパラメーターでグラフの表示数を変えることができます

今回の記事ではパラメーターとメジャーを組み合わせた上記の事例を紹介します

パラメーターとは？

パラメーターは一見スライサーと似ていますが、設定するのはあくまで数字の範囲となります

パラメーターはモデリングタブから追加することができます

実際に追加する時には「数値範囲」を指定します

次に開く画面では、出力する数値範囲を設定します

パラメーターを設定すると同時に専用のテーブルも作成されます

メジャーの設定

パラメーターを設定したら、メジャーの中にパラメーターで出力される値を組み込みます

折れ線グラフの各内容はそれぞれメジャーになっています

こちらにIF式を追加します

IF式にはパラメーターとの連動を組み込みます

このIF式によりパラメーターの値が１になった場合には、BLANK＝グラフ表示なしになります

＜まとめ＞

今回はパラメーターを使用してメジャーを操作し、グラフの表示本数をコントロールできるようにしました

パラメーターの操作は今回紹介した「数値範囲」に加えて「フィールド」というのがあります

フィールドには直接メジャーを設定することができます

この場合はパラメーターをスライサーのように使用できます

にほんブログ村

クラウド活用

Power BIで抽出したデータをエクセルに転記するボタンを作成する

2023年5月15日 analytic-vba コメントをどうぞ

【今回はボタン１つでPower BIの内容を既存のエクセルファイルに追加できるようにします】

Power BIはエクセルからデータを取得してダッシュボードを作成できます。実は、Power BI内で整理したデータをエクセルにエクスポートし直すこともできます

上記の画像の「エクセル出力」ボタンを押すと、OneDrive内にダッシュボードに表示されているデータが追加されます

準備すること

事前にOneDrive、もしくはSharepointにエクセルファイルを準備してください

列名は事前に固定する必要があります

そして、Power Automateから操作ができるようにテーブル化をお願いします

Power BI DesktopにPower Automateの追加

まずビジュアルのビルドの下にある「Power Automate」のボタンをクリックします

すると以下のような内容がページに追加されます

ここでPower Automateで出力するデータフィールドが指定できるようになります

Power Automateフローの設定

データフィールドを設定したら、三点リーダーから編集をクリックします

次に開いた画面では「新規」をクリックします

内容はインスタントクラウドフローを選択します

ここから実際にフローを作成するのですが、既にこちらの画像のアクションはデフォルトで設定されてあります

次にエクセルコネクタから「表に行を追加」アクションを追加します

「表に行を追加」アクションでは、まず事前設定したテーブル名までを設定します

後は、事前にPower BIからの抽出項目として設定した内容をそれぞれ設定します

「表に行を追加」アクションの設定が終わると「Apply to each」が自動的に設定されます

そしたら保存適用をクリックし、ボタンがページ内にできていることを確認します

発行処理

Power Automateフローの作成が終わり、ボタンを追加したら発行処理を行い、クラウド上でレポートを開きます

これでPower BIからエクセルにエクスポートできるようになります

＜まとめ＞

今回はPower BIからエクセルにエクスポートする仕組みを解説しました

紹介した内容はとにかくとても簡単です

Power Platformには簡単な事例が詰まっていることがよく分かります

継続的に今回のような有用なネタを投稿していきます

にほんブログ村

裏技

マトリックス表の見栄えをカスタマイズする

2023年5月7日 analytic-vba コメントをどうぞ

【ピボットテーブルのようにマトリックス表も自在に並びを変えれるようにする】

マトリックス表については「こんな風にできないか？」という問い合わせが何件かありましたので、まとめて記事にしました

まず一点目は合計の並びを変えられないかということです

通常、合計は一番右もしくは一番下になります

但し、合計がスクロールしないと見れないような大きい表の場合、まず最初に合計を見れた方がいい場合もあります

これが１点目です

２点目は値を並べる場合に、並びを横ではなく縦にできないか？ということです

確かに、並びが横よりも縦の方が見やすいケースもあります

それでは、上記の２点に分けてそれぞれを解説していきます

合計の位置を変える

テーブル作成

合計の位置を変えるには、下の画像のような並び替え用のテーブルを作成するところからはじめます

真ん中のManyの列で他のテーブルとリレーションを組みます

Oneの列では「Total」にManyの全ての内容が集約されるようにします

そして、OrderでOneの列の「Total」が１番目に並ぶように順番を指定します

リレーション

作成したテーブルは他テーブルとリレーションを作成します

列の並び替え

値を集約した「Total」が一番左（上）にくるように列の並び替えをします

これで、並べ替え用のテーブルをマトリックス表の列や行に配置すれば完成です

縦に並び変える

マトリックス表は通常では次の画像のように値が横に並びます

こちらの横並びを縦並びに変えるには「ビジュアルの書式設定」で「値のオプション」を操作します

上の画像の「値を行に切り替え」をオンに変えます

そうすれば並びが縦に変ります

＜まとめ＞

今回はマトリックス表をカスタマイズする方法を解説しました

Power BIの場合には機能が多く「ビジュアルの書式設定」もとても分かりにくいです

調べた結果「ビジュアルの書式設定」でできることもよくあります

ただ列や行の並び順については、どうしても「ビジュアルの書式設定」では解決できず、別テーブルを作成することになります

にほんブログ村

分析

RFM分析により膨大な会員の状況を可視化する～ツリーマップ使用～

2023年4月27日 analytic-vba コメントをどうぞ

【３つの指標をSCORE化し、オリジナルの観点で見える化を行えるようにしよう】

RFM分析*は昔からある分析手法ですが、Power BIを使用するとまた違った見せ方ができるようになります！

*R（Recency：最終購買日）、F（Frequency：購買頻度）、M（Monetary：累計購買金額）の３つの指標での分析

上のGIF画像では、Power BIの機能を活用してRFM分析にて行った内容を２つのストーリーで可視化できるようにしています

・金額ベース/Manetaryベースと会員数ベースの違い

・上記Manetaryの観点⇒RFMの観点

例えば、こちらのPower BI画面により金額と会員数ベースの違いが明確に分かります

続いて２つのビジュアルの階層を１つ下げると、最終注文からかなり月数が経っている会員（R:長）が多いことに気づけます

上記はあくまで例ですが、RFM分析とPower BIを組み合わせることにより、膨大なデータから「新たな発見」ができるかもしれません

ポイント

まずは従来のRFM分析通りに、「R（Recency：最終購買日）、F（Frequency：購買頻度）、M（Monetary：累計購買金額）」の３つの観点でランク付けします

今回の記事では、３つの観点それぞれを１と２でランク付けします（後で変換します）

更に今回の記事ではそれぞれのランクを組み合わせてSCORE化します

使用RAWデータ

使用するRAWデータは次の画像の注文データです

１年内で１回しか注文しない会員もいれば、複数回注文する会員もいます

ランク付け

まず最初に行うのは、RFMそれぞれの観点でランク付けを行うメジャーを作成することです

ランク付けを行う前に、RFMそれぞれの観点で「値/VALUE」を抽出する必要があります

Recency

⇒R-RANK = SWITCH(TRUE(),’MEASURE’①[R-VALUE]>3,”2″,”1″)

こちらはSWITCH関数を使用します

第一引数をTRUEにすることで、複数の条件式を組み合わせて使用することができます

①DATEDIFF(‘MEASURE’②[dayMax_customer],”2023/03/31″,MONTH)

DATEDIFF関数を使用して、２つの日付の「差」を抽出します

②は会員毎の最終注文日を抽出するメジャーです

こちらは次のように記述します

dayMax_customer = MAXX(FILTER(‘raw_data’,’raw_data'[CustomerID]=’raw_data'[CustomerID]),’raw_data'[OrderDate])

FILTER関数でテーブルから該当会員を絞りこんだ後に、MAXX関数で注文日の最終日を抽出しています

Frequency

⇒F-RANK = SWITCH(TRUE(),’MEASURE’①[F-VALUE]=1,”2″,”1″)

こちらもSWITCH関数を使用します

①は会員毎の注文回数を抽出するメジャーです

F-VALUE = COUNTROWS(raw_data)

COUNTROWS関数で該当する行（該当会員）の数を抽出しています

Monetary

⇒M-RANK = SWITCH(TRUE(),’①MEASURE'[M-VALUE]<100000,”2″,”1″)

①は会員毎の合計金額を抽出するメジャーです

M-VALUE = SUM(‘raw_data'[SalesAmount])

SCORE化

前述の３つのメジャーを通じて、会員毎にそれぞれのランク付けを１と２で行います

更にその後、１と２の文字列を組み合わせてSCOREを作成します

そのためにはSUMMARIZE関数を使用し、会員毎にSCOREを含むテーブル/rfmTableを作成します

SUMMARIZE関数はピボットテーブルと同じように、集計しながらテーブルを作成してくれます

＊注意：メジャーでも新しい列でもなく、新しいテーブルから作成します

文法としては次のように書きます

SUMMARIZE(集計の切り口（会員ID）,作成する列名,集計式・・・）

*SUMMARIZECOLUMN関数と似ていますが別です

rfmTable = SUMMARIZE(‘raw_data’,raw_data[CustomerID],”R-RANK”,’MEASURE'[R-RANK],”F-RANK”,’MEASURE'[F-RANK],”M-RANK”,’MEASURE'[M-RANK],“SCORE”,’MEASURE'[R-RANK]&’MEASURE'[F-RANK]&’MEASURE'[M-RANK],”M-VALUE”,’MEASURE'[M-VALUE])