SVGグラフの4回目で、棒グラフについては最終回となります。
目標
前回は実績に応じて長さが変わる棒グラフを完成しました。
今回は、それに前年実績の棒グラフと、今年の目標値のラインを追加して対前年対比、対目標対比を１つのメジャーで表現します。
シリーズ一覧
[Power BI] SVGで独自グラフを作ろう① - 鷹尾祥（アキラ）のExcel BI ブログ
[Power BI] SVGで独自グラフを作ろう② - 鷹尾祥（アキラ）のExcel BI ブログ
[Power BI] SVGで独自グラフを作ろう③ - 鷹尾祥（アキラ）のExcel BI ブログ
[Power BI] SVGで独自グラフを作ろう④ - 鷹尾祥（アキラ）のExcel BI ブログ
前年度グラフを追加する
前回までで階層に応じて最大値を調整する棒グラフが完成しました。ただし、このままでは条件付き書式の棒グラフと特に変わりません。そこでここからはＳＶＧならではの自由さを使って機能を追加していきます。
作成した今年の実績の下に前年度の実績の棒グラフを追加してみましょう。
前年と今年を併記するにあたり、以下の点にご注意ください。
- 共有されるものについてVAR変数の名前を変更しています。
- 紛らわしくなるので前回最後に紹介した最大値の色指定の処理は廃止しています。
- 前年の棒グラフは今年の棒グラフの真下にy=25の開始座標で追加します。
- 長さの最大値については、前年と今年の最大値をMAX関数で比較して大きい方を採用します。2つのスカラー値を比較するときはMAX関数を使用します。（MAXX関数ではありません。）
対前年実績比図 4-1 =
// 今年の実績
VAR s_CY_Act_Year_Max = MAXX(ALL('G_カレンダー'[会計年度]) , [売上])
VAR s_CY_Act_Qtr_Max = MAXX(ALL('G_カレンダー'[会計四半期]), [売上])
VAR s_CY_Act_All = [売上]
// 前年の実績
VAR s_PY_Act_Year_Max = MAXX(ALL('G_カレンダー'[会計年度]) , [売上（前年）])
VAR s_PY_Act_Qtr_Max = MAXX(ALL('G_カレンダー'[会計四半期]), [売上（前年）])
VAR s_PY_Act_All = [売上（前年）]
// 最大値の取得
VAR s_Act_Max
= SWITCH(
TRUE(),
ISINSCOPE('G_カレンダー'[会計四半期]), MAX(s_CY_Act_Qtr_Max , s_PY_Act_Qtr_Max) ,
ISINSCOPE('G_カレンダー'[会計年度]) , MAX(s_CY_Act_Year_Max, s_PY_Act_Year_Max),
MAX(s_CY_Act_All, s_PY_Act_All)
)
// バーの長さの定義
VAR s_CY_Act = DIVIDE(s_CY_Act_All, s_Act_Max) * 100
VAR s_PY_Act = DIVIDE(s_PY_Act_All, s_Act_Max) * 100
// カラーの設定
VAR s_CY_Bar_Color
= SWITCH(
TRUE(),
ISINSCOPE('G_カレンダー'[会計四半期]), "#B7D0C8",
ISINSCOPE('G_カレンダー'[会計年度]) , "#6FA191",
"#447B69"
)
VAR s_PY_Bar_Color
= SWITCH(
TRUE(),
ISINSCOPE('G_カレンダー'[会計四半期]), "#E3A193",
ISINSCOPE('G_カレンダー'[会計年度]) , "#C96A55",
"#AD4226"
)
// SVGの定義
VAR s_SVG =
"data:image/svg+xml;utf8,
<svg xmlns='http://www.w3.org/2000/svg'>
<rect x='5' y='5' width='" & s_CY_Act & "' height='15' style='fill:" & s_CY_Bar_Color & ";'/>
<rect x='5' y='25' width='" & s_PY_Act & "' height='15' style='fill:" & s_PY_Bar_Color & ";'/>
</svg>"
RETURN s_SVG
画像エリアの下部が隠れないようにマトリクス・ビジュアルをクリックして「画像のサイズ」の高さを「105」まで広げておきます。
こんな感じに仕上がりました。ここまでくると標準機能にはないレイアウトなので、達成感が出てきます。
マトリクス・ビジュアルの列に「商品カテゴリー」を置き、値を「対前年実績比図
」メジャーだけにすると、以下のような感じです。最大値の算出はそれぞれの商品カテゴリー内での最大値に調整されています。
目標値をラインで表す
最後に目標値も縦線で追加してみましょう。
目標値のメジャーは以下のようになります。
目標 = SUMX('F_目標値', 'F_目標値'[目標値])
データモデルやテーブルの説明は省略しますが、詳細を知りたい方は私の本の『Excel パワーピボット』か『Excel パワーピボット DAX編』をご覧ください。
今回はSVGのrectではなく、lineを使ってみます。
s_Act_Maxの条件については今度は売上、売上（前年）、目標の三つのうちの最大を求めるので、MAX関数の入れ子になっています。
s_Planでlineの位置を指定しますが、rectの方はx座標は5から始まるので、ここでも5を足して調整しています。
対前年実績比図 4-2 =
// 今年の実績
VAR s_CY_Act_Year_Max = MAXX(ALL('G_カレンダー'[会計年度]) , [売上])
VAR s_CY_Act_Qtr_Max = MAXX(ALL('G_カレンダー'[会計四半期]), [売上])
VAR s_CY_Act_All = [売上]
// 前年の実績
VAR s_PY_Act_Year_Max = MAXX(ALL('G_カレンダー'[会計年度]) , [売上（前年）])
VAR s_PY_Act_Qtr_Max = MAXX(ALL('G_カレンダー'[会計四半期]), [売上（前年）])
VAR s_PY_Act_All = [売上（前年）]
// 目標値
VAR s_Plan_Act_Year_Max = MAXX(ALL('G_カレンダー'[会計年度]) , [目標])
VAR s_Plan_Act_Qtr_Max = MAXX(ALL('G_カレンダー'[会計四半期]), [目標])
VAR s_Plan_Act_All = [目標]
// 最大値の取得
VAR s_Act_Max
= SWITCH(
TRUE(),
ISINSCOPE('G_カレンダー'[会計四半期]),
MAX(MAX(s_CY_Act_Qtr_Max, s_PY_Act_Qtr_Max), s_Plan_Act_Qtr_Max),
ISINSCOPE('G_カレンダー'[会計年度]),
MAX(MAX(s_CY_Act_Year_Max, s_PY_Act_Year_Max), s_Plan_Act_Year_Max),
MAX( MAX(s_CY_Act_All, s_PY_Act_All), s_Plan_Act_All)
)
// バーの長さの定義
VAR s_CY_Act = DIVIDE(s_CY_Act_All , s_Act_Max) * 100
VAR s_PY_Act = DIVIDE(s_PY_Act_All , s_Act_Max) * 100
VAR s_Plan = DIVIDE(s_Plan_Act_All, s_Act_Max) * 100 + 5
// カラーの設定
VAR s_CY_Bar_Color
= SWITCH(
TRUE(),
ISINSCOPE('G_カレンダー'[会計四半期]), "#B7D0C8",
ISINSCOPE('G_カレンダー'[会計年度]) , "#6FA191",
"#447B69"
)
VAR s_PY_Bar_Color
= SWITCH(
TRUE(),
ISINSCOPE('G_カレンダー'[会計四半期]), "#E3A193",
ISINSCOPE('G_カレンダー'[会計年度]) , "#C96A55",
"#AD4226"
)
// SVGの定義
VAR s_SVG =
"data:image/svg+xml;utf8,
<svg xmlns='http://www.w3.org/2000/svg'>
<rect x='5' y='5' width='" & s_CY_Act & "' height='15' style='fill:" & s_CY_Bar_Color & ";'/>
<rect x='5' y='25' width='" & s_PY_Act & "' height='15' style='fill:" & s_PY_Bar_Color & ";'/>
<line x1='" & s_Plan &"' y1='5' x2='"& s_Plan &"' y2='40' stroke='#DFBC2D' stroke-width='2' />
</svg>"
RETURN s_SVG
以下のように目標値が黄色の縦線で表現されました。
ほぼ完成に近いですが、よく見ると目標のない2023年にも開始位置に縦線が入っています。また、列の合計でも2023年と2024年の合計に対して、2024年の目標値を置いた縦線が入っています。
これらはミスリーディングなので非表示にします。
SVGのコンポーネントの表示、非表示はテキストで扱えばいいので、Planのlineの部分を独立させてVAR変数のテキストとして定義します。こちらで条件を満たさないときに
“”にしてしまえばよく、その条件は①その年に目標値が入っており、かつ②会計年度のフィルターが存在しているということですので、以下のようになります。
対前年実績比図 =
// 今年の実績
VAR s_CY_Act_Year_Max = MAXX(ALL('G_カレンダー'[会計年度]) , [売上])
VAR s_CY_Act_Qtr_Max = MAXX(ALL('G_カレンダー'[会計四半期]), [売上])
VAR s_CY_Act_All = [売上]
// 前年の実績
VAR s_PY_Act_Year_Max = MAXX(ALL('G_カレンダー'[会計年度]) , [売上（前年）])
VAR s_PY_Act_Qtr_Max = MAXX(ALL('G_カレンダー'[会計四半期]), [売上（前年）])
VAR s_PY_Act_All = [売上（前年）]
// 目標値
VAR s_Plan_Act_Year_Max = MAXX(ALL('G_カレンダー'[会計年度]) , [目標])
VAR s_Plan_Act_Qtr_Max = MAXX(ALL('G_カレンダー'[会計四半期]), [目標])
VAR s_Plan_Act_All = [目標]
// 最大値の取得
VAR s_Act_Max
=
SWITCH(
TRUE(),
ISINSCOPE('G_カレンダー'[会計四半期]),
MAX(MAX(s_CY_Act_Qtr_Max, s_PY_Act_Qtr_Max), s_Plan_Act_Qtr_Max),
ISINSCOPE('G_カレンダー'[会計年度]),
MAX(MAX(s_CY_Act_Year_Max, s_PY_Act_Year_Max), s_Plan_Act_Year_Max),
MAX( MAX(s_CY_Act_All, s_PY_Act_All), s_Plan_Act_All)
)
// バーの長さの定義
VAR s_CY_Act = DIVIDE(s_CY_Act_All , s_Act_Max) * 100
VAR s_PY_Act = DIVIDE(s_PY_Act_All , s_Act_Max) * 100
VAR s_Plan = DIVIDE(s_Plan_Act_All, s_Act_Max) * 100 + 5
VAR s_SVG_Plan =
IF(
s_Plan_Act_All > 0 && ISINSCOPE('G_カレンダー'[会計年度]),
"<line x1='" & s_Plan &"' y1='5' x2='"& s_Plan &"' y2='40' stroke='#DFBC2D' stroke-width='2' />",
""
)
// カラーの設定
VAR s_CY_Bar_Color
=
SWITCH(
TRUE(),
ISINSCOPE('G_カレンダー'[会計四半期]), "#B7D0C8",
ISINSCOPE('G_カレンダー'[会計年度]) , "#6FA191",
"#447B69"
)
VAR s_PY_Bar_Color
=
SWITCH(
TRUE(),
ISINSCOPE('G_カレンダー'[会計四半期]), "#E3A193",
ISINSCOPE('G_カレンダー'[会計年度]) , "#C96A55",
"#AD4226"
)
// SVGの定義
VAR s_SVG =
"data:image/svg+xml;utf8,
<svg xmlns='http://www.w3.org/2000/svg'>
<rect x='5' y='5' width='" & s_CY_Act & "' height='15' style='fill:" & s_CY_Bar_Color & ";'/>
<rect x='5' y='25' width='" & s_PY_Act & "' height='15' style='fill:" & s_PY_Bar_Color & ";'/>
" & s_SVG_Plan & "
</svg>"
RETURN s_SVG
これで完成です。
