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

【フィルターがどうもおかしい？と思ったらまずは今回の内容を疑ってみましょう！】

今回の記事では２つに分かれているテーブルを、リレーションにより一緒のマトリックスで使用します

上の画像の「発注数の合計」「納品数の合計」はそれぞれ別のテーブルのものです

そして、別のテーブルの内容がそれぞれ同じく「商品ID」にてフィルターされています

ちなみにここで言うフィルターとはエクセルのフィルター機能とは似て若干異なります

エクセルのフィルター機能は主に絞り込むために使用します

今回の記事のフィルターは「絞り込む」だけでなく「区分」や「選択」の意味がありますので、その点はご留意ください

実はこの「別テーブル共存」「同フィルター」の状態を作るのがとても重要なことなのです

どう作るかについては王道があります

１．参照マスタを作成する

２．それぞれのテーブルとの間で参照マスタとのリレーションを「１対多」「単一方向」で作成する

３．参照マスタの内容でフィルターする

この３つが王道です

何故ここで王道という表現を使うのか？

これは他にもリレーションやフィルタをする方法があるからです

但し、他の方法だと不正確だったり、曖昧さが残ります

この不正確になったり、あいまいさが何故発生するのかを理解し、Power BIのフィルターがどういうものなのかを考察するのが今回の記事の目的です

では、ここから王道も含めて他の方法を取った場合を実際に見ていきたいと思います

今回使用するデータ

発注テーブル

商品ID、発注数、発注日の3列から構成されます

商品IDは１～１０まであります

発注日は１つの商品IDに対して複数あります

ちなみに発注数の合計は１９０になります

納品テーブル

商品ID、納品数、納品日の3列から構成されます

商品IDは発注されてまだ全く納品されてないものもあるので、１～７までになります

納品日は１つの商品IDに対して複数あります

発注された10種類のIDの商品の中にはまだ全く納品されていない商品がある、というのが後々大きなポイントになります

ちなみに納品数の合計は８０になります

商品IDマスタ

全部で10行あります

発注テーブルと納品テーブルの商品ID数は以下のようになります

発注テーブル：10 > 納品テーブル：7

ですので商品IDマスタの行数は、両テーブルを漏れなくダブりなく網羅した１０となります

王道の場合

王道の場合のリレーションは次のようになります

「発注テーブルと商品IDマスタ」そして「納品テーブルと商品IDマスタ」それぞれの間に「１対多」のリレーションがあります

何故、１対多の関係になるかというと、前述のように１商品IDに対して発注日や納品日が複数に跨るからです

（注）ここでの*は多の意味になります

そしてそれぞれのリレーションは「単一方向」で作成します

以上が王道のリレーションのパターンです

マスタでフィルター

ではビジュアルはどうなるかを次に説明します

最大のポイントは商品IDマスタでフィルタすることです

マスタ以外でフィルター

では、次のように発注テーブルの商品IDでフィルターするとどうなるかを見てみましょう？

発注テーブルから取得する「発注数」は問題ないですが、納品テーブルから取得する「納品数」の方は全ての行が合計数になってしまい、商品ID別のフィルターが効いていません

理由はフィルタの方向が「商品IDマスタ」⇒「発注、納品テーブル」の方向の「単一」であるためです

その為、発注テーブルのフィルターが商品IDマスタを通じて納品テーブルまで波及していないからです

更に、次の画像のように納品テーブルの商品IDでフィルターしてみましょう！

こちらも発注数が合計になってしまいます

加えて、商品IDの８以降が欠けてしまいます

ですので、あくまでフィルターは参照マスタの内容で行うのが確実なのです

単一ではなく双方向でフィルターした場合

前述の王道ではクロスフィルターを単一で行っていました

では、クロスフィルターを双方向に変えてみたいと思います

この場合は実は、前述はうまく行かなかった発注テーブルからのフィルターが正しく行えます

何故正しくフィルターが行えるか？他の言葉で言い換えると何故、発注テーブルの内容で正しく納品テーブルの内容をフィルターが行えるかというと、フィルターが波及するからです

フィルターが波及する点は、納品テーブルの内容でフィルターした時も同様です

但し、この場合は結果が曖昧になります

商品IDが納品テーブルの内容の7迄になるからです

ですので、リレーションを双方向にて２つのテーブルをつなぐ場合は「曖昧」な結果になる可能性があるということになります

多対多

最後に、商品IDマスタを介さずに多対多でリレーションする場合を見てみましょう

ここで、本題に入る前に多対多の関係について詳細に見ておきましょう

上の画像で１が上下のテーブルで複数出現します

前回の商品IDマスタと発注、納品テーブルの関係では１⇒「1、1、・・・」となっていました

ところが、今回の多対多の関係では「1、1」⇔「1、1」ということになります

以下の画像のように、参照マスタに参照する値が複数あるのとは違い、元々複数の値があるテーブルどうしなのでそんなに問題が無いかのように感じます

実はこの場合も曖昧な結果になります

下の画像は「発注テーブル」の商品IDにてフィルタしたケースです

この場合は確かに問題がありません

今度は納品テーブルにてフィルタしてみます

この場合、納品テーブルの商品ID数までしか表示されないので曖昧な結果になります

ですので、やはり王道の方法が一番確実なのです！

＜まとめ＞

今回は参照マスタを使用して、２つのテーブルを連携する「王道」について解説しました

記事の途中で「２つのテーブルの内、どちらかの方がID数が多いのが分かっていれば問題ないのでは？」と思った方もいらっしゃると思います

確かにその通りなのですが、王道の方法であれば「ミスに気づきやすい」なども含めて総合的に確実性が担保されるので、王道の方法を実践するのがベストです！

最後に、

今回の記事は前回の内容：クロスフィルターの方向を単一もしくは双方で調整とも踏まえてみて頂くと理解が深まると思います

ぜひ前回の記事も目を通してみてください

にほんブログ村

初級

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

2024年6月14日 analytic-vba コメントをどうぞ

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

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

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

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

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

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

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

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

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

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

ポイント

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

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

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

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

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

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

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エディターで「例からの列」で「年」の表示を入れます

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

＜まとめ＞

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

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

ちなみに、

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

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

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

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

今回は以上です

にほんブログ村

コンテキスト遷移

コンテキスト遷移～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の活用の幅が広がります

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

にほんブログ村

分析

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

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＝グラフ表示なしになります

＜まとめ＞

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

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

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

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

にほんブログ村

裏技

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

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])

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

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

ツリーマップの作成

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

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

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

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

＜まとめ＞

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

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

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

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

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

にほんブログ村

ビジュアル素材

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

2023年4月18日 analytic-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を乗じることが必要ですので、その点はご注意ください

にほんブログ村

今回使用するデータ

発注テーブル

納品テーブル

商品IDマスタ

王道の場合

マスタでフィルター

マスタ以外でフィルター

単一ではなく双方向でフィルターした場合

＜まとめ＞

ポイント

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

カード

作成するダッシュボード

社員数カード

男性・女性社員数

男性・女性構成比

昇進済み・未昇進

未昇進

昇進済み

その他

＜まとめ＞

DAXで計算したいこと

MAX関数での計算

MAXX関数/行評価

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

＜まとめ＞

使用データ

ポイント

分析テーブル作成

SUMMRIZE関数

IF関数

＜まとめ＞

ポイント

矛盾の排除

単一⇒双方向

＜まとめ＞

使用データ

DATESINPERIOD関数

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

＜まとめ＞

パラメーターとは？

メジャーの設定

＜まとめ＞

合計の位置を変える

テーブル作成

リレーション

列の並び替え

縦に並び変える

＜まとめ＞

ポイント

使用RAWデータ

ランク付け

Recency

Frequency

Monetary

SCORE化

ツリーマップの作成

＜まとめ＞

使用データ

Waffle Chart（その他のビジュアル）

メジャーの準備

グラフの作成

＜まとめ＞

誰もが手軽にデータを有効活用してExcellentなビジネスライフを送れるようにします