カテゴリー別アーカイブ: Power BI

メジャー初心者のための人事ダッシュボードの作成~フィルター依存を卒業する~

【フィルター使用は一見、簡単なのですが、後でとても分かりにくくなります・・・】

今回の記事では、メジャーを活用して人事データの要約を示すダッシュボードを作成します

よくフィルターを活用したダッシュボードを見かけますが、色々と問題があります

フィルターを開けないと詳細が分からず、一目で正確性を確認できません

後、フィルターをかけるビジュアルを間違えてしまったり、ミスが起こりやすいです

そして、そもそも、割合などはフィルターでは計算できません

それに対して、メジャーを活用すると、計算過程がとてもシンプルに分かりやすくなります

今回の記事では、メジャーを「シンプル」に活用した人事データのダッシュボードを作成します

ご紹介するメジャーの内容は、実際のダッシュボードを作成する場面でかなり使いまわしていける内容だと思います

但し、今回はメジャーを中心に解説させて頂き、詳細はあまり深く説明していませんので予めご了承ください

ポイント

メジャー専用テーブルの作成

前述のようにメジャー専用のテーブルを作成して、メジャーを集約すると後で分かりやすくなります

まず最初に「データの入力」を行います

仮の値をテーブルに入力しておきます(後で削除)

テーブル名はメジャーを集約するのに相応しい名前にします

こちらのテーブルに新たに作成したメジャーを集約していきます

CALCULATE関数

このDAX関数を知っておくだけで、かなりダッシュボード作りは変わってきます

エクセルのワークシート関数でSUMIFS関数です

SUMIFS関数では、条件を設定して合計を計算します

CALCULATE関数では、合計するSUM関数だけでなく、他の計算を行うDAX関数も使用できます

文法は次のようになっています

CALCULATE(計算方法,条件)

詳細については後述させて頂きます

カード

今回のダッシュボードではメジャーで算出した数値をカードで表示し、数値を強調します

そして、カードのタイトルを使用し、数値の中身を一目で分かるようにします

タイトルはビジュアルの書式設定の「全般」から指定します

作成するダッシュボード

今回は人事データを要約したダッシュボードを作成します

ビジュアルはカードを中心に使用して、要約データを表示します

使用するデータは以下のエクセルファイルを使用します

社員数カード

社員数はテーブルの行数から算出します

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

テーブルの行数は「COUNTROWS関数」により算出します

文法は()の中にテーブル名を入れるだけになります

社員数 = COUNTROWS(‘人事データ’)

男性・女性社員数

男性・女性の社員数はCALCULATE関数にて条件を付けて算出します

先ほどCOUNTROWS関数を紹介したので、CALCULATE関数とCOUNTROWS関数を組み合わせるイメージを持たれた方もいらっしゃると思います

その方法も正解なのですが、前述のメジャーで社員数を算出しているので第一引数にそちらのメジャーを使います

男性社員数 = CALCULATE(‘MEASURE'[社員数],’人事データ'[性別]=”男”)

第二引数では条件(列名=内容)を指定します

男性・女性構成比

構成比は割り算で算出します

割り算は「DIVIDE関数」で算出します

男性社員割合% = DIVIDE(①’MEASURE'[男性社員数],②’MEASURE'[社員数])

①は割り算の分子、②は分母になります

昇進済み・未昇進

未昇進数は「昇進してからの経過年数」が2年以上の人の数で定義します

そして、昇進済みについては「社員数-未昇進」を算出するメジャーを作成します

未昇進

CALCULATE関数の第二引数を>=で条件を指定します

未昇進 = CALCULATE(COUNTROWS(‘人事データ’),’人事データ'[昇進してからの経過年数]>=2)

昇進済み

前述の通り引き算で算出します

昇進済み = [社員数]-[未昇進]

その他

こちらは「年」の表示がデータにはないのに入っています

これはPower Queryエディターで「例からの列」で「年」の表示を入れます

「例からの列」の詳細は過去の記事で紹介していますので、ぜひご活用ください

<まとめ>

今回は、メジャーを活用したダッシュボードの作成方法を紹介しました

メジャーを使用すれば、フィルターを使用するより修正方法などが分かりやすくなり、ミスも減ります

ちなみに、

こちらは画像を使用しています

この画像はパワーポイントで作成し、イメージから挿入しています

画像はパワーポイントの「アイコン」からスライドに挿入して作成します

スライドに画像を挿入した後、ファイルの拡張子を指定して保存します

今回は以上です

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

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

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

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

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

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

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

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

・MAXX_NoCal

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

・MAXX_withCal

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

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

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

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

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

DAXで計算したいこと

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

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

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

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

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の活用の幅が広がります

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

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ 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万以上”))

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

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

<まとめ>

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

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

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

最後に、

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

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

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

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

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

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

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

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

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

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

ポイント

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

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

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

それは影響範囲です

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

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

Power BIの場合は違います

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

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

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

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

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

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

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

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

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

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

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

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

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

矛盾の排除

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

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

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

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

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

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

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

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

ではどうするか?

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

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

単一⇒双方向

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

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

こちらを双方向にします

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

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

<まとめ>

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

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

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

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

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

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

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

【意外と小難しい移動平均を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関数で合計を計算します。その後に割り算をします

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

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

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

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

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

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

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

パラメーターとは?

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

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

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

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

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

メジャーの設定

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

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

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

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

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

<まとめ>

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

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

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

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

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

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

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

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

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

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

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

これが1点目です

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

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

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

合計の位置を変える

テーブル作成

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

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

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

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

リレーション

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

列の並び替え

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

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

縦に並び変える

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

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

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

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

<まとめ>

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

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

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

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

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

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

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

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

*R(Recency:最終購買日)、F(Frequency:購買頻度)、M(Monetary:累計購買金額)の3つの指標での分析

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

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

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

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

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

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

ポイント

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

今回の記事では、3つの観点それぞれを1と2でランク付けします(後で変換します)

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

SCOREテーブル

使用RAWデータ

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

raw_data

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

ランク付け

まず最初に行うのは、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関数を使用して、2つの日付の「差」を抽出します

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

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

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化

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

更にその後、1と2の文字列を組み合わせて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])

上記の”SCORE”列はランクを含む列を&で組み合わせて作成しています

rfmTableを作成したら「SCORE」テーブルとでリレーションを作成します

ツリーマップの作成

ツリーマップでは四角形の大きさで数字の大小を表示します

数字の大きいものが「右より左」「下より上」に並ぶのも特徴の一つです

今回のツリーマップでは階層を複数で作成するのがポイントです

これでツリーマップの階層をコントロールできるようになります

<まとめ>

今回はPower BIの機能を活用してRFM分析の見せ方を進化させる方法を解説しました

紹介した内容はあくまで、解説しやすくするためにかなり簡素化しました

例えば、ランクは1と2の2つで付けましたが3つや4つに増やした方が分かりやすいケースもあります

またSCORE化もSCORE自体に「超VIP」「ご無沙汰優良」などのネーミングを付けて定点観測するといいかもしれません

ぜひオリジナル方法を見つけて活用してみてください

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

ワッフルチャートで構成率をオシャレに表現する

今回の記事ではエクセルのグラフ編でも紹介したワッフルチャートを、Power BIで作成する方法を解説します

エクセルの場合だと、条件付き書式を使用して作成します

Power BIの場合だと、エクセルと同じように個別の作りこみはできません

但し、エクセルよりはかなり楽に作成することができます

使用データ

上の画像の商品別に「数量」での構成比を、商品別のワッフルで表現します

Waffle Chart(その他のビジュアル)

ワッフルチャートは、ビジュアルのビルドの下にある「三点リーダー」の「その他のビジュアルの取得」から取得します

「その他のビジュアルの取得」の次の画面にて開いた画面で「Waffle」と検索します

「Waffle」と検索すると「Waffle Chart」がレポートビュー画面に追加できるようになります

メジャーの準備

取得した「Waffle Chart」を使用するには、「Waffle Chart」仕様のメジャーを作成する必要があります

「Waffle Chart」仕様と言っても構成率に100を乗じるだけです

構成率 = DIVIDE(SUM(‘DAX1′[数量]),CALCULATE(SUM(‘DAX1′[数量]),All(‘DAX1′[商品])))*100

100を乗じないとかなり小さい数字がワッフルチャートに表示されますので注意が必要です

グラフの作成

では実際にグラフを作成してみます

グラフの「Category Data」の欄には「商品」を配置します

Valueは前述のメジャーを使用します

これで完成です

<まとめ>

今回は「その他のビジュアルの取得」から取得した「Waffle Chart」を使用して、ワッフルチャートを作成しました

使用するメジャーは100を乗じることが必要ですので、その点はご注意ください

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

グラフの並び順を変える3つの方法~条件列、DAX、リレーション~

【グラフのX軸が思うように並んでくれない、そんな悩みは今回の記事で解決できます】

Power BIのグラフの並び変更は、通常だとグラフの右上の三点リーダーをクリックした後に下記の画像の箇所で変更します

今回は上の並べ方では解決できない場合の対処方法を3つ紹介します

いずれの方法もデータビュー画面・列ツールにて「列で並べ替え」機能を使用します

まずは「1.並び変えたい列を選択」した後「列ツール」の「列で並べ替え」にて、「2.順位を記述した列」を指定します

読者の方の中には「その方法は知っている、だけどDAXを書くとエラーになった」という方もいらっしゃると思います

今回の記事ではエラーを回避する方法も解説しています

やりたい事

今回使用するデータは次の画像のデータです

こちらの商品の並びを次のようにします

A⇒B⇒C⇒D⇒E ➡ C⇒D⇒E⇒A⇒B

DAX1

一つ目の並べ替え方法はDAXのSwitch関数で変える方法です

下は別データで並び変えた事例です

*過去記事⇒こちら

上の画像ではMONTHの並び順を1月ではなく、4月からはじまるように変えています

では、今回の場合はどうかというと、次のようなSwitch関数の式を書きます

ところが、今回の場合は実際に並び替えるとエラーが発生します

前述の月の並び替えの場合は、並び替え対象が「MONTH列」で順位列の式が「順位 = Switch(MONTH([Date]),1,10,・・・」となっています

つまり、Switch関数の中身が「並び替え対象」ではなく「Date列」となっています

今回の場合は、Switch関数の中身に「並び替え対象」が入っています

順位_DAX = SWITCH(‘Data'[商品],・・・

ですので、次のようなエラーがでます

これはSUM関数をSUM関数の対象範囲に含めるような形になります

ですので、今回は別な方法も紹介します

条件列

Power Queryエディタ画面で条件列を作成する方法も使えます

この条件列にて順位を指定すると次のようなグラフになります

別テーブルを手動で作成(DAX2)

最後に紹介する方法はこちらの画面:データの入力でテーブルを作成します

内容はこんな形で入力します

こちらで作成したテーブルはリレーションも組んでおきます

次にデータビュー画面でDAX(RELATED関数)を作成し、順位列を作成します

この順位列であれば循環の問題は発生しません

<まとめ>

今回は、グラフの並び順を変える方法を3つ紹介しました

DAXで順位列を作成する場合には「循環参照」の問題は常に意識しておきましょう

個人的にはPower Queryエディで条件列を作成するより、DAXで作成した方が手軽だと感じています

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ