カテゴリー別アーカイブ: 分析

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

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

今回の記事では、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へ

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

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へ

DAXを使い一発で新規顧客・既存顧客別に会員数と金額を集計する

【売上金額が単に増えた減ったとは別に新規と既存の切り口があると、金額の増減をストーリーで語れます】

数字の増減の理由を聞かれた時に、返答に困ったことはありませんか?

なかなか数字の増減の理由をつかめないのは、原因がずっと前に起こっていることもあるからです

例えば、ある企業の売上金額の80%が僅か顧客の20%から生まれていたとします

この優良顧客の離脱率は完全に0にはできません

しかも獲得した顧客もすぐに優良になるわけではありません。優良顧客になるまでに時間はかかるものです

優良顧客が離脱するペース以上に新規顧客を獲得して成長させないと売上は維持できません

ですので、新規顧客の獲得の減少が後々売上に響くこともあります

逆に新規顧客と既存顧客に分けた切り口で集計ができると、中長期期間での分析がしやすくなります

ところが、この新規既存の集計はエクセルのワークシートで行おうとすると猛烈に時間がかかります

今回の記事では、DAX一発で新規既存の数字を集計できるようにします

なお、新規と既存の切り口は売上金額の分析でなくても、在庫分析などでも活用できます

ポイント

新規と既存の違いとは何か?この定義がとても重要です

初回購入とはまた違いますので、その点は明確に意識しておきましょう!

次の図を見てみてください

対象期間より前に購入があった会員は新規と既存、どちらに区分されるでしょうか?

これは既存に区分されます

この「対象期間より前」の定義を数式で表すと次のようになります

<MIN(対象期間)

この数式を活用して既存会員をDAXで表現します

逆に新規はどう表現するか?

全会員-新規会員

上記で表現できますので、既存会員を式で表すことができれば難しい式を使う必要はありません

使用データ

使用するデータは次のデータになります

テーブル名:myData

横の軸は会員番号別の軸になります

4月1日から4日まで新規会員を1会員づつ獲得しており、新規会員からの金額は1日100となっています

上記のテーブルはPower BI内に取り込んだ後、ピボット解除で変換した上で使用します

尚、上記は売上データから新規・既存を集計しますが、会員データを別途作成しておくともう少しDAX式が簡単になります

会員データと売上データの間にリレーションを作成したうえで、DAXを使用する方法も少し紹介します

使用DAX(抜粋)

VALUES関数

今回のDAXではVAR/変数を使って複数のDAX関数を組み合わせます

その中の一つがVALUES関数です

VALUES関数はテーブル操作関数なので「新しいテーブル」を作成できます

上の画像では、全会員をテーブル化しています

CALCULATETABLE関数

こちらの関数はテーブル式をフィルター評価する関数です

CALCULATETABLE_NewTable = CALCULATETABLE(VALUES(myData[会員番号]),’myData'[日付]=MIN(‘myData'[日付]))

上のVALUES関数に加えて、フィルターを加えています

今回の記事では、この関数を使用して既存会員をテーブル化します

新規会員数

ポイントで前述したように、全会員から既存会員を引いたのが新規会員となります

NewCustomers =  
VAR CurrentCustomers= Values(myData[会員番号]) 

VAR CurrentDate=Min(myData[日付]) 

VAR OldCustomers=CALCULATETABLE(VALUES(myData[会員番号]),ALL(myData[日付]),myData[日付]<②CurrentDate) 

VAR NewCustomers=EXCEPT(①CurrentCustomers,③OldCustomers)

⑤RETURN COUNTROWS(④NewCustomers)

解説

①新規・既存会員を含めて全会員をリスト化します

②対象期間の最小日付、つまり新規と既存の境目となる日付を算出します

③②で算出した最小日付を基にして既存会員をテーブル化します

④①の全会員から③の既存会員をEXCEPT関数により差し引いて新規会員をテーブル化します

⑤COUNTROWS関数により、NewCustomersテーブルから新規会員数を出力します

既存会員数

上記の新規会員数を算出するDAX関数の一部を省略するだけです

OldCustomers =

① VAR CurrentCustomers= Values(myData[会員番号]) 

②VAR CurrentDate=Min(myData[日付]) 

③VAR OldCustomers=CALCULATETABLE(VALUES(myData[会員番号]),ALL(myData[日付]),myData[日付]<②CurrentDate)

④RETURN COUNTROWS(③OldCustomers)

新規会員金額

新規会員数を算出するDAXの最後のRETURNを変更します

 SUMX(NewCustomers,CALCULATE(SUM(myData[金額])))

SUMX関数を使用するのですが、第二引数でCALCULATE関数を使用することでNewCustomersテーブルの1行1行*を計算させるのがポイントです

行評価

NewCustomersAmount =

①VAR CurrentCustomers= Values(myData[会員番号]) 

②VAR CurrentDate=Min(myData[日付]) 

③VAR OldCustomers=CALCULATETABLE(VALUES(myData[会員番号]),ALL(myData[日付]),myData[日付]<CurrentDate) 

④VAR NewCustomers=EXCEPT(CurrentCustomers,OldCustomers)

⑤RETURN     SUMX(NewCustomers,CALCULATE(SUM(myData[金額])))

既存会員金額

OldCustomersAmount =  

①VAR CurrentCustomers= Values(myData[会員番号]) 

②VAR CurrentDate=Min(myData[日付]) 

③VAR OldCustomers=CALCULATETABLE(VALUES(myData[会員番号]),ALL(myData[日付]),myData[日付]<②CurrentDate)

④RETURN SUMX(③OldCustomers,CALCULATE(SUM(myData[金額])))

会員データを使用するパターン

使用する会員データは次の通りとなります

新規会員数

NewCustomers =  

①VAR CurrentDates=VALUES(myData[日付])               

②RETURN COUNTROWS(FILTER(‘CustomerFirstSale1’,’CustomerFirstSale1′[最小] in ①CurrentDates))

既存会員数

OldCustomers =

①VAR CurrentDates=VALUES(myData[日付])

②VAR CurrentCustomers=COUNTROWS(VALUES(myData[会員番号]))

③VAR NewCustomers=COUNTROWS(FILTER(‘CustomerFirstSale1’, ‘CustomerFirstSale1′[最小] in ①CurrentDates))    

④RETURN ②CurrentCustomers-③NewCustomers

新規会員金額

NewCustomersAmount =

①VAR CurrentDates=VALUES(myData[日付]) 

②RETURN SUMX(FILTER(‘CustomerFirstSale1’,’CustomerFirstSale1′[最小] in CurrentDates),’MeasureTable (2)'[SalesAmount]*)

*事前にSUM関数で合計を計算するメジャーを作成しておきますSalesAmount = SUM(myData[金額])

既存会員金額

OldCustomersAmount =  

①VAR CurrentCustomers= Values(myData[会員番号]) 

②VAR CurrentDate=Min(myData[日付]) 

③VAR OldCustomers=CALCULATETABLE(VALUES(myData[会員番号]),ALL(myData[日付]),myData[日付]<②CurrentDate)

④RETURN     SUMX(③OldCustomers,CALCULATE(SUM(myData[金額])))

<まとめ>

今回の記事ではDAX関数を使用して、新規と既存を区分して集計する方法を解説しました

複数の関数を組み合わせて集計しますが、一番重要なのはMIN(対象期間)により全期間から新規と既存の境目となる「時」を算出することです

DAX関数を使用することで、エクセルで複数シートをまたいで処理するのとは違い、即時に新規と既存を集計できます

今回は新規と既存の切り口での集計を紹介しましたが、後日、新規と既存の切り口以上に重要なリピートについても解説を行います

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

にほんブログ村

にほんブログ村 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へ