タグ別アーカイブ: 分析

移動平均を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へ

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へ

DAXで定量的にランク分けして集計を行う~分析の基本~

【分けることが分析の基本です。DAXを使用して一発で定量的なランク分けを行いましょう!】

今回の記事では修正が面倒なIF式を使わないままDAXで定量的に分類を行います

仮にエクセルで同じことを行うのであれば「VLOOKUP関数(近似値検索)」やIF式を組み合わせて行う必要があります

DAXであれば一つのメジャーをすれば一発で集計できます

使用するテーブル

使用するテーブル:DATAは次のテーブルになります

ランクテーブルの作成

まずはDAXでランクテーブルを作成します

エクセルなどでランクテーブルを作成した上でPower BIに取り込むよりずっと楽です

ランクテーブルの作成には「DATATABLE関数」を使用します

DATATABLE関数は2つのパートに分かれています

1.列の作成⇒列名と列の形式を指定します(例:”NO”、INTEGER:数字形式、STRINGで文字列形式)

2.列の中身の作成⇒{}の中に、更に{}を使用して行を表現します

今回のDAXの中身は次のようになります

RankTable = DATATABLE(

“NO”,INTEGER,”NAME”,STRING,”Min”,INTEGER,”Max”,INTEGER, 

{

  {1,”A”,50,200},  {2,”B”,30,50},  {3,”C”,0,30}

 }

)

ランク分けのメジャーの作成

ランク別にカウント

ここからは前述のランクテーブルを使用してランク別にカウント処理を行います

つまり、DATAテーブルの金額列とランクテーブルの各行を互いに評価する形になります

ですのでFILTER 関数が2つ(DATA、ランクテーブル)出ててきます

Count_by Segment = CALCULATE(

    COUNTROWS(‘DATA’),

    FILTER(‘DATA’,   

  NOT(ISEMPTY(

         FILTER(‘RankTable’,NOT(ISBLANK([金額]))&&’RankTable'[Min]<[金額]&&’RankTable'[Max]>=[金額])

        

      

   )
)

上記に出てくる「ISEMPTY関数」「ISBLANK関数」についてはこちらをご参照ください

ランク別の合計金額

ランク別に合計金額を集計する場合にはCALCULATE関数内でSUM関数を使用します

SUM_by Segment = CALCULATE(

    SUM(‘DATA'[金額]),   

  FILTER(‘DATA’,(省略)

<まとめ>

今回はDAXを使用してランク分けする方法について解説しました

DAX式は少し複雑ですが、一度作成してしまえば何度でも使いまわすことができるのでとても便利です

ぜひ有効活用してみてください

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

にほんブログ村

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

お仕事の依頼

 実務を担う現場の方が使い慣れたエクセル。Excellent仕事術は、このエクセルを効果的に活用して、御社の業務改善やお困りごと解決の最適なスキームをご提案/ご提供します!

御社にこんな悩みはございませんでしょうか?

エクセル作業がブラックボックス化しており、膨大な人手がかかっている

分析資料は沢山あるが、業績改善に向けた課題や傾向が全く把握できない

プレゼン資料にどこかインパクトが無い

 Excellent仕事術では、これらの悩みに対して「現場重視」で現場の声を反映させながら解決策を提案します!

 ご相談自体は無料でうけたまわります。エクセルに関することであれば、どんなことでも幅広く「Excellent仕事術のガッツ鶴岡」へご相談ください!

プロトタイプ(試用版)の開発についても低価格で請負ます!

お問合先

Excellent仕事術の得意分野

VBA/マクロ開発

エクセル作業がブラックボックス化する理由は、大きく分けて3つあります。

エクセルシートのスパゲティ化

データのバケツリレー

エクセルファイルの共有

 Excellent仕事術では従来のIT部門では対応できなかったこの3つの問題を、これまで培ってきたマクロ/VBA開発力で解決します。

エクセルシートのスパゲティ化

 エクセルシートのスパゲティ化は、1シートに「データ」「計算」「作業スペース」を詰め込むことで起こります。

 異なる内容が詰め込まれている為、「どこの範囲がどの内容」で、「どの範囲とどの範囲がどのように絡み合っているのか?」が第3者に分かりにくくなります。

 スパゲティ化したエクセルシートは、作成した担当者しか扱えないだけでなく、データが不規則に蓄積されているため有効活用ができず効率化が行えません。

 スパゲティ化したエクセルシートを改善するポイントは、「データ」「計算」「作業スペース」を1シートに混ぜず、それぞれシート毎に集約することです。

 Excellent仕事術ではスパゲティ化したエクセルシートを、3つのステップで劇的に改善します。

・Firstステップ

   ➡ ヒアリング及びシートの中味の分析

・Secondステップ

   ➡ シートを「データ」「計算」「作業スペース」などの目的別に作成

・Thirdステップ

   ➡ 作業スペース・シートでの作業をVBAにより自動化

 改善したエクセルシートでの作業は効率化されているだけでなく、特定の担当者以外でも引継ぎの負担なく作業が行えるようになります。

データのバケツリレー

 エクセルファイルでデータを外部の会社や他部署とやり取りすることは、良く行われていると思います。

 データ活用の目的が外部の会社や他部署と違う為、「エクセルファイルの交換」はファイルやシート間での膨大な「データの転記処理」を発生させることになります。

 実は、VBA/マクロを活用すれば複数のファイルやシートの「データ取込み・変換処理」 を、一括で行うことができます。

一括処理のイメージ

 一括で「データ取込み・変換処理」が行えれば、膨大な転記処理から解放されるだけでなく、取り込んだデータを活用して「周辺業務」の効率化も行えるようになります。

 Excellent仕事術では、現場の声をお聞きして「データ取込み・変換処理」から「周辺業務の改善」まで徹底的にサポート致します。

エクセルの共有

 エクセル1ファイルで大量のデータを扱う場合によく出てくる問題として「共有」の問題があります。

 エクセルは表計算ソフトであり、本来は大量のデータを扱うには不向きです。1シートに蓄積できる行数に制限があり、蓄積データが大量だと動きが重たくなります。

 しかも、 1ファイルを複数の担当者で同時に共有して使用できない為、担当者に待ち時間が発生してしまい、どうしても業務効率が上がらなくなります。

 Microsoft社の製品では、データベースソフトとして「Access」があります。Accessであれば、行数に関係なく大量のデータを扱うことができ、複数の担当者で共有して使うことも可能です。

 ところが、Accessだと現場では行えない業務も中にはあります。特に「大きなクロス表での作業」などは、エクセルのような表計算ソフトを業務用にカスタマイズした表でないと効率があがりません。

 こういったエクセルでないと行えない業務を、複数担当者でエクセルファイルを共有しながら作業したい場合には抜け道があります。

エクセルとAccessをVBA技術により連携して使うという方法です。

 この方法であればエクセルとAccess、互いのメリットを組み合わせて業務を行うことが可能です。

 このエクセルとAccessn連携する仕組みでは、データはAccessに一元管理し、エクセルには都度、必要なデータだけ呼び出して活用します。

 このエクセルとAccessの連携を行う場合にポイントとなるのは「エクセルの役割」と「Accessの役割」の切り分けです。

 Excellent仕事術では、連携の仕組みを構築するだけでなく、業務を理解させて頂いた上で「連携のポイント」についても提案させて頂きます。

事例①

データ分析/統計処理

「数値/分析資料は沢山あるが、業績改善に向けた課題や傾向が全く把握できない」ことの主な原因は、鳥の目全体像を捉えることができていないことが考えられます。

 例えば「前期と比べて注文単価が何故下がったか?」について、注文単価自体の推移を見ても、単位荒すぎて「何故下がった」かはよく分からないはずです。

 逆に注文台帳を見ても、今度は単位細かすぎて「注文単価が前期と比べて何故下がったか?」はよく分からないはずです。

 では下に記述したように、注文を単価別にグループ分けして件数の推移を確認した場合はどうでしょう?

・低単価の注文件数の推移

・中単価の注文件数の推移

・高単価の注文件数の推移

 低単価の注文内容と高単価の注文内容では、注文に含まれる商品の違いや、注文した会員の行動や属性に違いが必ずあるはずです。

 低単価の注文件数の構成が高くなっているのか、もしくは高単価の注文件数の構成が低くなっているのか、を注視すれば「注文単価が下がった原因」はある程度見えてくるはずです。

 このように業績改善に向けた課題や傾向を捉えるには、必ず鳥の目でグループ別の推移を捉えることが必要です。

 このグループ別の推移を捉えるのに、必ずしも「データアナリスト」や「CRMソフト」が必要とは限りません。エクセルの集計機能と統計機能を駆使すれば、エクセルでも対応は可能です。

 Excellent仕事術では、これまで培ってきたエクセル集計技術と統計処理・技術を活用して、御社の数字分析のお手伝いをします。

会員分析

会員分析のポイントは大きく分けて3つあります。

・新規会員の獲得状況 ➡会員数自体の分母を増やせているか?

・新規会員の2回目のリピート状況 ➡新規会員が定着しているか?

・既存会員のリピート状況 ➡優良会員が確実に育成できているか?

 つまり、まずは「新規・既存」と「優良・非優良」の2つの切り口で会員のグループ分け(セグメント)を行った上で、会員のグループ間の移行状況を把握することが必要です。

 Excellent仕事術では上記の2つのグループの切り口から、更に深堀した切り口でグループ分けを行います。

そして、基本の「新規・既存」「優良・非優良」のグループに加えて、更に深堀りしたグループ間の移行率を把握するためのツールを提供します!

事例②

その他の分析

 Excellent仕事術では、発注・在庫分析や予算作成・中期計画のシミュレーションなども得意分野としております。

ぜひ、この分野についての悩み事もExcellent仕事術にお問い合わせしてみてください!

ダッシュボード化

 数字分析を行った内容については、社内で共有し、次の行動につなげていくことも大事なことです。

 Excellent仕事術では社内の効率的な業績報告、情報共有のために一目で現状を掴むことができるダッシュボードの作も行います 。

エクセルダッシュボード

プレゼン・資料作成

 競合他社とのプレゼン競争に勝つためには、「数字の魅せ方」に突破口があります。海外のエクセル活用事例をうまく取り入れた「数字の魅せ方」こだわりましょう。「数字なんてただの数でしょ?」。そんなことはありません。「目で見て、数字の変化がリアルにわかる」効率的な資料作成術を、Excellent仕事術/ガッツ鶴岡がご提供します

-シンプルで誰の目も釘付けにするグラフ関連資料の作成をいたします。

Excellent仕事術/ガッツ鶴岡の強み

業務分析の経験が豊富

-上場企業から創業期のベンチャー企業まで、様々な事業規模での業務分析の経験が豊富

-内部監査も含めた内部統制対策をとおして、業務フロー図等の作成・業務分析の経験多数あり。

数値管理の経験が豊富

-銀行や証券会社等の金融機関への数値説明・交渉の経験が豊富

-EC通販での予算管理や中期計画作成、各種分析などの経験が豊富

最新のエクセル技術

 Excellent仕事術/ガッツ鶴岡は、世界の最先端のエクセル技術を常にキャッチアップしています!

-海外では日本と違い、エクセルの新機能/便利機能をうまく使いこなして業務の効率化に成功している事例が沢山あります

-小難しいVBAコードや複雑な関数は必要ありません。Excellent仕事術/ガッツ鶴岡がお教えする世界最先端のエクセルの新機能を使って、効率よく簡単にエクセル作業ができるようになります。

-エクセルは元々、表計算ソフトです。データベースソフトではありません。しかしパワークエリという新機能を使えば、エクセルをデータベースソフトとしても使いこなせます

 Excellent仕事術のガッツ鶴岡がパワークエリとの連動を作成します/お教えします。

事例①

<商品管理システム/エクセルとAccessの連携>

 業務メンバー各自が業務に即したエクセルのクロス表を使用しながら、エクセルへの入力データはAccessファイルに蓄積して各自で共有する仕組みを構築

<改善前・課題>

★1つのエクセルシートに様々な業務データと入出力表が混在

・商品の特性上、入荷した商品を手動で分割・並べ替えて管理する必要があった

・商品の並べ替え順を各業務で使い回すため、1シート上に様々なデータが混在

データ蓄積型式が不規則になり、VBAなどの自動化手段の活用が不可能

・1ファイルをメンバー全員で共有して使用するため、業務メンバーに待ち時間が発生

<改善後・効果>

★業務データはAccessに一元管理し、エクセルの入出力画面を業務毎に分散

・データはAccessに一元管理し、ADOという技術を使用して、都度、必要なデータのみエクセルファイルに呼び出す仕組みを構築。このADOの技術により、1つ1つの業務に即した画面作成が可能になった。さらに他の業務メンバーがファイルを使っている間に発生していた待ち時間も削減できた。

・Accessに一元管理したデータを、RPAに連携して会計システムへの入力を自動化した。これにより、残業時間が1月あたり200時間減らすことに成功

事例②

EC通販における会員のセグメンテーション>

 会員の1年間の購買履歴とアンケートの集計結果を分析し、会員のセグメンテーションを実施。それまで一律にマーケティング施策を行っていたが、会員のセグメント毎の施策(例:セグメント別の割引率設定など)を実施できるようになった

実績

-商社・畜産部門向け商品管理システムの開発

 ➡エクセルとAccess及びRPAと連携処理

-客先マスタ登録業務用システム開発/エクセル・RPA連携

 ➡エクセルVBAにてRPA入力用データを成型

-EC通販会社での会員分析・マーケティング施策立案

 ➡エクセル分析ツールにて会員の購買履歴を統計処理にて分析

-EC通販会社向け在庫管理システム

 ➡適性発注率をエクセルを使用して分析

ストリートアカデミーで講師もしています

経歴

Excellent仕事術運営者:鶴岡 敦(ガッツ鶴岡)

 新卒で総合商社に入社後、ユニクロに転職。更にEC通販会社に経営企画室長として入社。

 EC通販会社にて膨大なデータ量に苦戦。必要に迫られて自らエクセル学習を重ねるうちに、 海外の活用例などを知るようになり、エクセルの可能性に目覚める

1993年慶應義塾大学商学部卒

2002年USCPA合格

大学在学中にプロボクサーとしてデビュー

現在もボクシングジムで練習を続けている

 

 

IT未経験からDX推進!

 私は30代半ばからの10年間、朝から終電まで退屈なエクセル作業をして過ごしましました。それからシステム会社に45歳で転職し、RPAと出会いました。
 ITの世界の常識が大きく変わる予感がしました。業務を理解している担当者自身が、システムエンジニアの力を借りずに、システム開発を効率的に推進していくことができるのではと考えました。
 そこから更にPower Queryなどの「モダンエクセル」、Power BIPower AutomatePower Appsなどの「Power Platform」が登場し、誰もが手軽にデータを有効活用できる世の中が来ることを確信しました。
 「このエクセル作業が効率化できたらいいのに・・・」「このエクセルデータから有効な情報を引き出せたらいいのに・・・」と頭を悩ませている人は数多くいらっしゃいます
 IT未経験者でもモダンエクエル、Power Platformを正しく活用すれば、自身の退屈なエクセル業務を削減しつつ、データを活用した有意義なビジネスライフが送れるようになります。
 私自身は最初はITの世界に飛び込んだ時は用語さえわからず、相当苦労しました。この時に味わった苦労の一つ一つがこれからエクセル作業を改善していこうとしている皆様のお役に立つと思います。
 
ところで、皆様はブルース・リーをご存知でしょうか?
 ブルース・リーはカンフーの神様、先駆者と呼ばれ、ハリウッドで大活躍し、未だに多くのハリウッドスターに尊敬されている香港生まれのアクションスターです
そのブルース・リーがこんな言葉を残したそうです。

「Don’t think.Feel!(考えるより、まずは感じること)」

ぜひ一緒に手を動かしてITを楽しんでいきましょう!

Excellentなレッスン

ストアカにてレッスンを定期的に行っています

Don’t think.Feel!(考えるより、まずは感じること)」をモットーに丁寧に解説を行います

オンラインに加えて対面レッスンも行っています

🌟ここまでできるPower BI~計算、分析、操作、魅せる~

Power BIの脱初心者になりたい人向け。動くグラフは必見です!

*ブログ読者優待割引あり紹介記事はこちら詳細

🌟Power BIをはじよう~マンツーマン/対面・オンライン両方可

これからPower BIを始めたい人向け

🌟Powerシリーズならなんでもマンツーマンレッスン

30分からの時間制講座です。*内容・日時等を事前に問合せをお願いします(提供が可能でないこともあります)

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

にほんブログ村

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

DAXを使用して注文数毎に会員を分類する~会員のセグメント~

【IF式をわざわざ重ねなくても、DAXを使用すれば注文数で会員をセグメントできます】

下の画像のようなデータから会員/Customer_IDを注文数でセグメントする場合はIF式やピボットテーブルを組み合わせる必要がありました

OrderData1

DAXとパラメーターを使用すれば、簡単にセグメントが行えます

ポイント

今回は注文データからセグメントを行いますので、一旦、SUMMARIZE関数を使用して会員番号毎に注文数を紐づけるテーブルを作成するのがポイントです

上記のテーブルからFILTER関数を使い、注文数毎に会員数を集計します

パラメーターの作成

まずはFILTER関数内で、注文数のフィルターに使用するパラメーターを作成します

次の画像で、フィルターに設定する数値範囲を設定します

上記の数値範囲を設定すると2つのものが作成されます

1つは数値範囲を設定したテーブルです

もう一つはメジャーです

こちらのメジャーをFILTER関数内で使用します

セグメントをするメジャーの作成

顧客別注文数 =

①VAR CustomerOrders=   

SUMMARIZE(

‘OrderData1’,

OrderData1[Customer ID], 

“注文数”, 

COUNT(OrderData1[Order ID])

)

RETURN

②COUNTROWS(   

FILTER(

CustomerOrders,

[注文数]=③[パラメーター 値]

)

)

③は前述のパラメーターを作成した時のメジャーになります

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

パラメーターで設定した値(1~10)毎(行毎に)に会員を抽出する形になります

FILTER(CustomerOrders,[注文数]=③[パラメーター 値])

会員を抽出したら②の「COUNTROWS関数」でセグメント毎の会員数を集計します

マトリックスの作成

最後にマトリックスを作成します

行には下の画像のパラメーターを使用します

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

<まとめ>

今回は注文データから、注文数で会員をセグメントしたデータを作成します

エクセルのワークシートで行おうとすればIF関数やピボットテーブルを組み合わせる必要があります

DAXを使用する場合は、FILTER関数をうまく使えば簡単に行えます

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

にほんブログ村

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

DAXを使いコホート分析~顧客のリピート状況を把握~

【もしエクセルで行おうとしたら、非常に手間なリピート状況の把握をDAXを使用して一発で解決】

前回は、新規と既存をDAXで集計しました。今回は同じ条件で顧客を並べた上でリピート状況をDAXで把握できるようにします!

上の画像のマトリックス表では、縦に顧客を初回受注月で並べ、横は初回受注月の経過月(1~12)別にリピート率を並べています

下の上の画像のズームアップを見てください

例えば、初回の受注月が2019年4月だった会員がもし100名いたとしたら、その100名のうち次の月に受注があったのは33名ということになります

このマトリックス表を作成することで、単に金額の増減を把握するだけではわからない、具体的な増減の要因をつかめるようになります

使用データ

使用するデータには、会員別の受注データを使用します

OrderData

ポイント

作成は3段階になります

1段階目で、新しい列を追加します

OrderData

会員毎の初回受注日を抽出する列を作成し、前述のマトリックス表の縦軸にします

2段階目で横軸にする連番をテーブルで作成します

3段階目でリピート率を計算するDAXを作成します

こちらのDAXは1段階目、2段階目で作成した縦横の軸を組み入れます

会員毎の初回受注日の抽出

CALCULATE関数とMIN関数を使用して、会員毎の初回受注日を抽出する列を作成します

CALCULATE関数の第二引数の条件にはFILTER関数を使用して、会員毎のテーブルを繰り返し作成し、会員毎の初回受注日を繰り返し抽出します

ただリピート率の計算は月単位で行うので、最小受注日は月末に揃えます

月末に揃える作業はEOMONTH関数を使用して行います

FirstOrderDate =

①VAR CurrentCustomer=’OrderData'[会員番号]
RETURN
②CALCULATE(

EOMONTH(MIN(‘OrderData'[受注日]),0),

FILTER(‘OrderData’,’OrderData'[会員番号]=①CurrentCustomer)

)

EOMONTHは第二引数に0を指定することで、第一引数の日程の月末日を抽出します

連番テーブルの作成

マトリックス表の横軸になる連番(0~12,増分は1)を作成します

こちらは新しいテーブルの作成となります

テーブルの作成にはGENERATESERIES関数を使用します

MonthAfter = GENERATESERIES(0,12,1)

リピート率を計算するDAXの作成

変数の作成

まず縦横の位置を変数で指定します

A: VAR CurrentMonthAfter=SELECTEDVALUE(MonthAfter[Value]) 

B: VAR CurrentFirstMonth=SELECTEDVALUE(OrderData[FirstOrderDate])

マトリックスの位置の行を抽出

テーブル:OrderDataから前述のAとBを組み合わせた位置に該当する行を、FILTER関数を使用して抽出します

この時に、EOMONTH関数を使用して月末日に揃えて行を抽出するのもポイントです

FILTER(‘OrderData’, EOMONTH(‘OrderData'[受注日],0)=EOMONTH(B: CurrentFirstMonth,A: CurrentMonthAfter))

CALCULATE関数による会員数抽出

上記のFILTER関数により抽出した行から、CALCULATE関数とDISTINCTCOUNT関数を使用して該当する会員数を抽出します

 CALCULATE(         

DISTINCTCOUNT(‘OrderData'[会員番号]), 

FILTER(‘OrderData’,

EOMONTH(‘OrderData'[受注日],0)=EOMONTH(CurrentFirstMonth,CurrentMonthAfter)

) ),

DIVIDE関数による率の計算

DIVIDE関数によって、分子:マトリックスの位置の会員数と分母:全体の会員数(実際には行でフィルターされる)で割ります

CustomerRetension% =  

A: VAR CurrentMonthAfter=SELECTEDVALUE(MonthAfter[Value]) 

B: VAR CurrentFirstMonth=SELECTEDVALUE(OrderData[FirstOrderDate])

RETURN  

DIVIDE(   

CALCULATE( 

DISTINCTCOUNT(‘OrderData'[会員番号]),

FILTER(‘OrderData’,

EOMONTH(‘OrderData'[受注日],0)=EOMONTH(B: CurrentFirstMonth,A: CurrentMonthAfter) 

),

DISTINCTCOUNT(OrderData[会員番号]) 

)

マトリックスの作成

実際にマトリックス作成の際には、2つの作業を事前に行います

日付の変更

個人の好みにもよりますが、そのままですと縦軸の初回の受注月はJan、Febなどの英語名になります

ですので、事前に日付を変換しておくことをお勧めします

仮メジャーの作成

マトリックスの「行」には上記の画像のFirstOrderDate、もしくは変換した内容、列には2段階目で作成した0~12の連番を指定します

値には普通にいけば、リピート率を配置します

ただ、このケースの場合には一旦、仮で作成したメジャーを一旦配置します

この仮メジャーを配置した後、リピート率を計算するDAXを配置します

<まとめ>

今回の記事では、初回受注月で会員を並べ、横軸に経過月毎にリピート率を並べるコホート分析のためのDAXの作成方法を解説しました

実際の作成はマトリックスの縦軸、横軸と値の3段階で行います

DAX関数は様々な関数を組み合わせて作成しますが、特にFILTER関数をどう使うかがポイントとなります

FILTER(‘OrderData’,’OrderData'[会員番号]=①CurrentCustomer)

FILTER(‘OrderData’,

EOMONTH(‘OrderData'[受注日],0)=EOMONTH(B: CurrentFirstMonth,A: CurrentMonthAfter) 

上記2つのFILTER関数はCALCULATE関数と組み合わせますが、この組み合わせは色々な場面で使用できるのでぜひ覚えておきましょう!

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

にほんブログ村

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

超高速!パレード図を1つのメジャー一発で作成する方法

【分析を語る上で一番よく出てくるのがパレード図だと思います。実は実務上ではかなり作成が面倒なのですが、DAXを使用すれば一発で作成できます】
パレード図の便利さの一つは、分析の優先順位を付けれる点です

物理的には全ての客先、全ての商品を同じように対策を打つことはできません

パレード図では横棒グラフの並びと、累計の割合を示す折れ線グラフとの組み合わせにより、優先的に対策を打つべき箇所が分かります

ところが、エクセルのワークシートでパレード図を作ろうとするととても面倒です

特に累計の構成比を算出するのが面倒です

データの並び替え⇒構成比の算出⇒累計の構成比の算出などが「累計の構成比」の算出には必要です

仮に、データの追加があった場合には最初からやり直しになります

この累計の構成比メジャーで作成します

今回は以下の売上データから、客先別のパレード図をPower BIにて前述のDAX関数を組み合わせて作成します

ポイント

累計の構成を計算するので、数式は以下のようになります

・分子⇒累計金額

・分母⇒全合計金額

分母は問題になりませんが、分子が厄介です

エクセルワークシートであれば、下の図のように、機械的に上のセルを足して累計金額を計算します

メジャーで計算する場合にはポイントが2つあり、1つ目は個別金額のグループ化、2つ目は不等式を使う点です

仮に、金額が右のようになっているとしたら、A>B>C>D>E

Dの箇所の累計は>=Dのもの、つまりA+B+C+Dのグループを合計します

一番大きいAであれば、Aだけ、一番小さいEの場合には全てをグループ化して合計します

不等式を使うためには、AからEまで各々の合計額(複数)を事前に計算してグループ(複数)を準備しておくことが必要です

全体金額の計算

累計の構成比はmyParetoとして計算します

まずは、分母の金額を計算します

myPareto = VAR TotalRevenue=CALCULATE(SUM(‘売上データ3′[売上金額]),ALLSELECTED(‘売上データ3’))

単にSUM関数で計算するのではなく、スライサーなどによるフィルター処理にも備えて、CALCULATE関数とALLSELECTED関数の組み合わせで計算します

*VARとRETURNの使い方はこちらから

*ALL関数とALLSELECTED関数の使い分けはこちらから

都度金額の計算

後で不等式の中で比較のために使う都度金額/CurrentRevenueを計算しておきます

こちらは前述の画像の>=の右側の部分(D)になります

VAR CurrentRevenue= SUM(‘売上データ3′[売上金額])

個別金額のグループを計算

個別金額のグループは、SUMMARIZE関数を使用して作成します

*SUMMARIZE関数の使い方はこちらから

該当するのは前述の画像の「A~E」の部分です

話を分かりやすくするために、SUMMARIZE関数を使用して新たなテーブルを作成してみます

mySummarize = SUMMARIZE(‘売上データ3’,’売上データ3′[客先],”Revenue”,SUM(‘売上データ3′[売上金額]))

この関数により客先を重複なく並べた列を作成した上で、新たに「Revenue」という列で客先別の合計金額を並べています

こちらのSUMMARIZE関数を前述の式の中で使うと次のようになります

不等式を使った累計の計算

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

*SUMX関数の使い方はこちらから

前述のように、累計は不等式を使ってグループを作成し、グループを合計して算出します

SUMX関数は2つ引数を使用しますが、1つ目の引数はグループを計算するFILTER関数になります

 FILTER(SummarizeTable, [Revenue]>=CurrentRevenue)

上記の式の[Revenue]は、前述のSUMMARIZE関数で算出したグループの各々の金額と考えてください

このFILTER関数により各々の金額のCurrentRevenue以上の金額がグループ化されます

そして、SUMX関数の第二引数とてして、合計する列[Revenue]を指定します

これでメジャー内の変数の指定が終わったので、Returnを指定します

Returnは「CumulativeSum/TotalRevenue」となります

グラフの作成

これで問題だった累計構成比が計算できましたので、最後にグラフを作成します

グラフは横棒グラフと折れ線グラフの組み合わせを選択します

X軸は客先、列のY軸は売上金額の合計を選択します

線のY軸には、先ほど作成したメジャーを設定します

作成したメジャー内では、分母にALLではなくALLSELECTED関数を使用しています

ですので、スライサーでフィルターしても、折れ線は最終的に100%になります

<まとめ>

今回はDAX関数を使用して、Power BIにてパレード図を作成する方法を解説しました

作成するDAX関数は複数の関数の組み合わせになり、複雑な面もありますが、一度作成してしまえば使いまわしていけます

エクセルワークシートで同じことを使用としたら、行数が可変になるので、相当面倒な作業になります

最後に、今回使用したDAX関数をメモ帳にて添付します

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

Power BIでWaterFallグラフを作成する~増減が線でわかる~

このページの読者の中には、どうやって値の増減をビジュアル化するか?について頭を悩ましたことがある方も多いと思います

下の画像を見てみてください。こちらは通常の縦棒グラフです

9月末残高が増減を伴った結果、一体どうなったのかがよくわかりません

今度は、この記事で紹介するWaterFallグラフです

値の増減と左の棒グラフがどうなったのかが点ではなく、線で理解ができます

増減が表示されるだけでなく「合計」という元データにない項目も自動で表示されるのも特徴です

このWaterFallグラフの作成は、あまり難しくはないですが、1点だけ問題があります

それは項目の並び替えの問題です

この問題は後で後述したいと思います

元データ

今回はこちらの増減を含むデータが元データになります

ビジュアルの選択

WaterFallグラフのマークをクリックします

データの配置

WaterFallグラフでは「カテゴリ」と「Y軸」を指定します

ここまでは、普通のグラフと同じですが、なかなか並び順が思うようにならなかったりします

並び順を調整する方法をこれから解説します

並び順の調整方法

並び順の調整はPower Queryエディターで行います

エディター画面でインデックス列を追加します

ここからデータ管理画面に移ります

この画面で「項目」列をインデックス列をもとにして並び替えを行えるようにします

ちなみにこの画面通りにレポート画面で並ぶとは限りません

並び替えは「列で並び替え」で行います

この時、項目列を指定したままにしておいてください

これでレポート画面でインデックス列通りに並び替えが行われます

<まとめ>

グラフはシンプルなものが一番です

このWaterFallグラフでシンプルにできるシーンも多いとおもいますので、ぜひ有効活用してください

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

にほんブログ村

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

分布図の作成及びグループ化~データを見やすく分解する

ピボットテーブルグループ化機能を使うと、数字が見やすくなるため重宝しておりますが、Power BIでも同じようなことが行えます

今回は、Power BIにおけるグループ化機能を量/ヒストグラムと質に分けて解説します

分布の把握は数字分析の基本なので、分析を行う必要のある方には特に有用な情報だと思います

量によるグループ化

今回は下の画像のデータからヒストグラムを作成したいと思います

売上金額の範囲区分を一定の金額で設定し、レコード数(行数)747件の件数を範囲区分別に縦棒グラフで表示します

まず、レポート画面のフィールド欄で「売上金額」上で右クリックします

すると「新しいグループ」という表示が見えますので、こちらをクリックします

「新しいグループ」をクリックした後は、次の画像の画面が開きます

ピンのタイプはデフォルトで「ピンのサイズ」になっていますが、こちらは「ピンの数」に変更します

ここで「ピンの数」とは縦棒の数になります

OKボタンを押すと、新たなフィールドができます

こちらをX軸に配置し、Y軸に売上金額のカウントを配置します

すると、縦棒グラフがヒストグラムとなります

ヒストグラムのデータ区分の範囲はピンのサイズとなります

ちなみにY軸を合計に変えるとデータ範囲別に合計金額を表示することができます

質によるグループ化

こちらのグループ化はレポート管理画面の一つ下のデータ管理画面で行います

下の画像がグループ化を行うデータです

まず、グループ化の対象になる「都道府県」の列を選択します

すると上のタブが「列ツール」に切り替わり、「データグループ」が表示されます

ここで「データグループ」の▼マークをクリックすると次の画面が表示されます

「新しいデータグループ」をクリックすると表示が次のように切り替わります

ここでグループ化するものは、Ctrlキーを押しながら選択し、グループ化をクリックします

この選択、グループ化の処理によりグループ化が実施されます

仮に、この状態のまま画面右下のOKボタンを次のような列ができます

ですので、列名およびグループ名はOKボタンを押す前に修正しておきます

<まとめ>

今回は量と質の双方の観点からグループ化を行う方法を解説しました

Power BIではグループ化により、分析可視化だけでなく、分析作業も行えます

ヒストグラムについては、X軸の範囲が良く見えない点について不便に感じる方もいらっしゃると思います

その場合には、「その他のビジュアルの取得」から「histogramのアプリ」を探す方法もあります

ヒストグラム/histogramは複数種類がありますので、ぜひ試してみてください

上の画像の「Histogram Chart」ならばX軸も下の画像のように明確に表示されます

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

にほんブログ村

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