タグ別アーカイブ: 80対20のルール

超高速!パレード図を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へ