[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『パワーピボットのメジャー計算』（kon）
パワーピボットのメジャー計算について教えて下さい。
年度、月、担当者ごとの目標収入を登録した以下のデータモデルを用いて、
年度ごとの月別累計目標をメジャーで計算したいと考えています。
年月 年度 担当 目標収入
2021/4 2021 いとう 500000
2021/4 2021 かとう 800000
2021/5 2021 いとう 500000
2021/5 2021 かとう 500000
2021/6 2021 いとう 100000
2021/6 2021 かとう 400000
・
・
・
2022/4 2022 いとう 200000
2022/4 2022 かとう 500000
2022/5 2022 いとう 300000
2022/5 2022 かとう 400000
以下の表をピボットデーブルで表示させるには、
メジャーでどのような式を作ればよいでしょうか？
年度 年月 累計目標収入
2021 2021/4 130000
2021 2021/5 230000
・
・
・
2022 2022/4 70000
2022 2022/5 140000
以上、よろしくお願いいたします
＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
・年度内の累計をとろうとすると、 EARLIER関数を使った集計をする必要があると思います。 ・しかし、EARLIER関数のヘルプをみると、 ＞EARLIER は主に計算列のコンテキストで使用されます。 などとあり、メジャー内で使おうとするとエラーが発生してうまくいきませんでした。 （"存在しない以前の行コンテキストを使用しています"などとおっしゃる。）
・そこで、「計算列」として
年度累計:=SUMX(
FILTER('テーブル1',
'テーブル1'[年度] = EARLIER('テーブル1'[年度])
&& 'テーブル1'[売上日] <= EARLIER('テーブル1'[売上日])
),
[目標収入]
)
などと定義し、
ピボットテーブルで、「年度累計」の"最大値"をとることで一応の結果は得られました。
・しかし、ワークシート上でごく単純にできることを、 わざわざ道具を使って複雑にやっている印象で、なにかがおかしいのではという印象を拭えません。 私も、どなたかに教えて頂きたいですね。 （DAXのメジャーは2日目なもので） （γ） 2022/04/10(日) 15:19
パワーピボットで売り上げ計算を行う過程で累計目標を出せればと思っていましたが、
γ様が仰るようにワークシートで計算して取りこめば呆気なくできましたので、
これで解決とさせていただきます。
ありがとうございました。
（kon） 2022/04/12(火) 11:51
中途半端なコメントをしたために、迷惑をかけたかもしれません。 きっとそれ相応の対応策があるのでしょう。
やろうとしていることは、 売上日 商品 売上金額 2022/04/13 A 1 2022/04/13 B 2 2022/04/14 A 3 2022/04/14 B 4 といった売上データがあったときに、年度始からの累計を表すメジャーを作り、 年月単位のピボットテーブルを作成することです。 （特にPower Pivotでないとできないことではないですが、メジャーの学習ということのようです。）
■ ネットに上がっている、 https://modernexcel7.hatenablog.com/entry/2019/08/03/003951 の記事が参考になるかと思います。 （ただ、記事には修正が必要な気がします。）
この記事では以下の式を推奨していますが、 手元で実行したところ思うような結果が得られませんでした。（私のミスかも）
累計2:=
VAR MaxDate = MAX('dateTable'[日付])
VAR MaxFinYear = MAX('dateTable'[会計年度])
RETURN
CALCULATE (
SUM('テーブル1'[売上金額]),
FILTER (
ALL ( 'dateTable' ),
'dateTable'[日付] <= MaxDate
&& 'dateTable'[会計年度] = MaxFinYear
)
)
・'dateTable'テーブルと'テーブル1'は、日付と売上日をキーにrelationを結んでいる。
・なぜうまくいかないのかは不明ですが、
MaxDate: =MAX ( 'dateTable'[日付] )というメジャーを作って、
表示しても、ピボットのメッシュに対応した最大値を返さず、全体の最大値を返してしまう。
・つまり、フィルタコンテキストでは実行されていないように思われる。
■私がやったのは、こんな方法です。（中身はほぼ同じですが、微妙に異なります）
累計:
=
VAR MaxDate = MAX ('テーブル1'[日付] )
VAR MaxFinYear = MAX('テーブル1'[会計年度] )
RETURN
CALCULATE (
SUM('テーブル1'[売上金額]),
FILTER (
ALL('テーブル1'),
[売上日] <= MaxDate && [年度] = MaxFinYear
)
)
・ちなみに、
テーブル1の[会計年度] という計算列には、
=related(dateTable[年度])
が設定してあります。
・ピボットの行の「年月」に対応するMaxDateとMaxFinYearを計算したうえで、
改めて、ALL('テーブル2')でフィルタを外します。
そのコンテキストで、同じ会計年度中の、当該年月までの累計を再計算させる、ということです。
■なお、メジャーを使わずに、
=TOTALYTD(SUM('テーブル1'[売上金額]),'テーブル1'[売上日],ALL('テーブル1'[売上日]),"3/31")
という計算列を作り、
その「計算の種類」を、「基準フィールド」を「年月」とした「累計」にしても、同じ結果は得られます。
■【メモ書き】
(1)まず、こうした日にちを使う計算では、別途、カレンダーテーブルを使うのが定石のようです。
dateTableという名称で、カレンダテーブルを作成しました。
(2)DAXの計算では、"コンテキスト"というのが重要な考え方のようです。
私もまだ日も浅く慣れていませんが、
https://www.scribd.com/document/371176540/Contoso-Sample-DAX-Formulas
にあったサンプルのExcelファイルと、Wordのドキュメントが参考になりました。
# 私は、最近 PowerPivotというのを知ったのですが、これを支えているDAXというのは # 10年以上前からあったのですね。余り流行らなかった？のは何故なんでしょうか。
==================================
【参考までに使用したデータをメモしておきます】
<<テーブル1>> 売上関係データ
売上日 商品 売上金額 年度 年月
2022/04/13 A 1 2022 2022/04/01
2022/04/13 B 2 2022 2022/04/01
2022/04/14 A 3 2022 2022/04/01
2022/04/14 B 4 2022 2022/04/01
2022/05/13 A 5 2022 2022/05/01
2022/05/13 B 6 2022 2022/05/01
2022/05/14 A 7 2022 2022/05/01
2022/05/14 B 8 2022 2022/05/01
2022/06/13 A 9 2022 2022/06/01
2022/06/13 B 10 2022 2022/06/01
2022/06/14 A 11 2022 2022/06/01
2022/06/14 B 12 2022 2022/06/01
2023/04/13 A 10 2023 2023/04/01
2023/04/13 B 20 2023 2023/04/01
2023/04/14 A 30 2023 2023/04/01
2023/04/14 B 40 2023 2023/04/01
2023/05/13 A 50 2023 2023/05/01
2023/05/13 B 60 2023 2023/05/01
2023/05/14 A 70 2023 2023/05/01
2023/05/14 B 80 2023 2023/05/01
2023/06/13 A 90 2023 2023/06/01
2023/06/13 B 100 2023 2023/06/01
2023/06/14 A 110 2023 2023/06/01
2023/06/14 B 120 2023 2023/06/01
↑
「年度」と「年月」は
dateTableを参照した計算列です。
<<ピボットの結果>>
行ラベル 累計 MAX日付
2022
2022/4/1 10 2025/3/31 0:00
2022/5/1 36 2025/3/31 0:00
2022/6/1 78 2025/3/31 0:00
2023
2023/4/1 100 2025/3/31 0:00
2023/5/1 360 2025/3/31 0:00
2023/6/1 780 2025/3/31 0:00
↑年度始からの累計が計算されている。
↑（参考）
MAX日付: =MAX('dateTable'[日付] )というメジャーは、
正しい結果を返していない。
（γ） 2022/04/13(水) 11:57
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.