Power BIでCOUNTIFのような集計方法について

Question

表題の通り、Power BIでエクセルのCOUNTIFのような集計ができるようなdax関数を教えていただけないでしょうか。
具体的にはある製品の項目において、平均値±3σの範囲にある行の合計をカードに出力したいです。

Comments

[mhoby_vt]

平均値はスライサーで選択した期間によって変動しますでしょうか？
それとも全期間の平均値で固定となるでしょうか？

[adyui]

早速の回答ありがとうございます。
平均値はスライサーで選択した際に変動させるようにしたいです。

[adyui]

ご記載いただいた式を入力した結果、希望通りの結果が返ってきました。
しばらく悩んでたので、本当にありがとうございます。

厚かましいお願いになりますが、平均値±3σの値を以下のように設定したく、そちらについても教えていただくことは可能でしょうか。
・現時点の1年前～3年間の平均値±3σの値を設定したい。(今日だとすると、1年前の2021～2019年のデータ)
・設定した値は年度のスライサーのみ影響を受けない。(他のスライサーの影響は受ける)

式は以下のようになっています。
管理値個数 = 
VAR x1 = AVERAGE('L'[IV])
VAR x2 = STDEV.P(L[IV])
VAR x3 = x1 + x2*3
VAR x4 = x1 - x2*3
VAR x5 = CALCULATE(
          　　COUNT('L'[IV])
             　　,FILTER('L','L'[IV] <= x3 && 'L'[IV]>= x4))
VAR x6 = COUNT(L[IV])
VAR x7 = DIVIDE(x5,x6,0)
RETURN x7

[mhoby_vt]

力になれたようで良かったです。
下記ではいかがでしょうか？
VAR x1 =
CALCULATE(
AVERAGE('L'[IV])
,ALL('テーブル'[年度])
)

VAR x2 =
CALCULATE(
STDEV.P(L[IV])
,ALL('テーブル'[年度])
)

すみません、追記します。
上記内容は平均値、標準偏差の計算時に年度のフィルターを無視する内容ですが、「設定した値」というのがCOUNTも含まれるのであれば、x5とx6にもCALCULATE~,ALL(’テーブル'［年度］)が必要になります。

[adyui]

ご対応ありがとうございます。
言葉足らずで申し訳ありません。
「設定した値」というのは「現時点の1年前から3年間の平均値±3σの値」となります。

また、「現時点から3年間の平均値」という条件式は下記の
「CALCULATE(AVERAGE('L'[IV]),
          　　DATESINPERIOD(L[分析日], LASTDATE(L[分析日]), -3, YEAR))」
にて表現できてると思うのですが、「現時点の1年前から3年間の平均値」となった場合、どのようにすればよろしいでしょうか。
この条件式に先ほどの年度フィルターを無視する式を加えたものを最終的には知りたいです。

以下、「現時点の3年間の平均値±3σの範囲にある割合」になります。
管理値個数 = 
VAR x1 = CALCULATE(AVERAGE('L'[IV]),
          　　DATESINPERIOD(L[分析日], LASTDATE(L[分析日]), -3, YEAR))
VAR x2 = CALCULATE(STDEV.P(L[IV]),
        　　  DATESINPERIOD(L[分析日], LASTDATE(L[分析日]), -3, YEAR))   
VAR x3 = x1 + x2*3
VAR x4 = x1 - x2*3
VAR x5 = CALCULATE(
         　　 COUNT('L'[IV])
           　  ,FILTER('L','L'[IV] <= x3 && 'L'[IV]>= x4))
VAR x6 = COUNT(L[IV])
VAR x7 = DIVIDE(x5,x6,0)
RETURN x7

[mhoby_vt]

理解ができておらず申し訳ありません。
「現時点の1年前から3年間の平均値」
→こちらについてですが、他に記述方法はあるかと思いますが、思いつくのは以下です。(少しダサいですが笑)
VAR x0 = TODAY
CALCULATE(AVERAGE('L'[IV]),
DATESBETWEEN(L[分析日], x0-365*3,x0-365))

DATESBETWEEN関数は開始日付と終了日付を指定できるので、使えるかなと思います。

「この条件式に先ほどの年度フィルターを無視する式を加えたもの」
→こちらについてですが、メジャーで指定ではなく、書式>相互作用を編集を押下し、年度のスライサーを選択すると、他ビジュアルに対するスライサーの作用を無効に設定できますので、こちらで対象のビジュアルを無効にすれば良いかと思います。

[adyui]

またまた、言葉足らずで申し訳ないです。
「現時点(日)の1年前から3年間の平均値」ではなく、現時点の年(今なら2022年)の1年前から3年間の平均値」を表示したいです。
下記に、それっぽい式を書いてみたのですが、結果が空白で返されてしまったのですが、なぜでしょうか。
VAR x0 = YEAR(TODAY())
VAR x1 = CALCULATE(AVERAGE('L'[IV]),
          DATESBETWEEN(L[分析日], x0-3,x0-1))

相互作用の編集を押下てみたのですが、アイコン等が出現せず、スライサーが選択できない状態になっています。。
厚かましいお願いになりますが、メジャーの指定が可能であれば、そちらの方法もご教授できないでしょうか。

[mhoby_vt]

>下記に、それっぽい式を書いてみたのですが、結果が空白で返されてしまったのですが、なぜでしょうか。
→DATESBETWEEN関数は、あくまで日付(yyyy/mm/dd)が対象なので、YEAR(yyyy)を入れるとうまく表示されなくなります。

>「現時点(日)の1年前から3年間の平均値」ではなく、現時点の年(今なら2022年)の1年前から3年間の平均値」
→テーブルにYEAR([分析日])等で、年数値(yyyy)の列を追加することは可能でしょうか？
上記が可能であれば、以下の式で実現できると思います。
VAR x0 = MAX('L'[年数値])
CALCULATE(AVERAGE('L'[IV])
,FILTER('L','L'[年数値] >= x0-3 && 'L'[年数値] <= x0-1)
)

>相互作用の編集を押下てみたのですが、アイコン等が出現せず、スライサーが選択できない状態になっています
→手順が間違ってました。
①スライサー選択
②書式クリック
③相互作用の編集
④カードの右上の〇に斜線が入ったマークを押下
上記でできないでしょうか？
もし「なし」のマーク出ない場合、カードの配置を少し動かしてみて下さい。他ビジュアルと重なって見えないときがあるので。
回答にて画面ショットを貼ります。

メジャーだと今のところうまくいかず、、申し訳ありません。

[adyui]

返信遅れて申し訳ございません。
年度の問題に関しましてはご記載いただいた式で解決しました。
ありがとうございます。

相互作用の件につきましては、添付いただいた内容で解決しましたが、
根本的な問題は解決していないようです。
こちらの言葉足らずな部分が多く申し訳ないです。

添付させていただいた画像のグラフに平均値・標準偏差±3σの定数線を載せています。
この線のみを、スライサーの影響受けずに「現時点の1年前から3年間値」で表示することはできますでしょうか。
グラフ自体はスライサーの影響を受けるようにしたいです。

[mhoby_vt]

なるほど。承知しました。
本日は少し忙しく、試せておらず申し訳ないのですが、以前提示頂いた式で、「RETURN x7」があったと思うのですが、そこを以下にするとどうでしょうか。

RETURN
CALCULTE(
SUM(x7)
,ALLSELECTED('テーブル'［年度］)
)

構文内容が誤っていたら申し訳ありませんが、ALLSELECTEDはグラフ上の軸を無視して表示したい時に使った記憶がありますので、こちらで試してみて頂きたいです。

[adyui]

お忙しい中のご回答ありがとうございます。
記載いただいた式を入れたのですが、ＳＵＭ(x7)で列参照をしていないため、エラーが出てしまいます。
 x7 = DIVIDE(x5,x6,0)で記載しています。

[mhoby_vt]

失礼しました。
x7は変数定義自体が不要で、以下にしてみて下さい。

RETURN
CALCULATE(
DIVIDE(x5,x6,0)
,ALLSELECTED('テーブル'［年度］)
)

[adyui]

エラーは起きなかったのですが、日付スライサーの影響を受けてしまっているみたいで、単一の年(2019年のみ等)を選ぶと結果が空白でかえってしまいます。
またまた、伝え方が悪く申し訳ないのですが、「現時点の1年前から3年間の値(平均値等)」を固定値として、スライサーの影響を受けずに表示したいという意味です。

下記の平均値を算出する式の場合はどのようになりますでしょうか。
VAR x0 = MAX('日付'[年])
VAR x1 = CALCULATE(AVERAGE('まとめ'[IV]),
          　　FILTER('日付','日付'[年] >= x0-3 && '日付'[年] <= x0-1)

[mhoby_vt]

返事が遅くなり申し訳ございません。
ここだとインデントが反映されないようなので、最初の回答を編集してコードを追加しました。
ご確認お願い致します。

ALL('L'[年度])はスライサーに指定している列、ALLSELECTED('L'[年度])はグラフのx軸に指定している列が入るイメージです。

[adyui]

返信遅くりました。
ご回答いただいたコードを入れた結果、求めていた状態にすることができました。

最初の質問から飛躍してしまいましたが、長期間のご対応いただきありがとうございます。

Answer 1

Answer 2

Answer 3

平均値は変動する旨、承知しました。
すみませんが、平均値±3σの計算方法は分からないため、単純に平均値+3と平均値-3の範囲のカウント数を表示するDAX例を回答させて頂きます。

メジャー =
VAR x1 = AVERAGE('テーブル'［値列］)

RETURN
CALCULATE(
        COUNT('テーブル'［任意の列］)
             ,FILTER('テーブル','テーブル'［値列］ <= x1+3 && 'テーブル'［値列］>= x1-3)
)

上記メジャーはメジャー内で平均値を変数で定義してRETURNの後で変数を使用しています。

平均値を算出する別メジャーがあれば、変数ではなくそのメジャーをFILTERの条件に入れても問題ないです。

参考になれば幸いです。
不明点等ございましたら、ご連絡をお願い致します。

↓2022/6/2追記

test = 
VAR x0 = CALCULATE(
            MAX('L'[年])
                ,ALL('L'[年度]))
VAR x1 = 
CALCULATE(
            AVERAGE('L'[Ⅳ])
                ,FILTER(ALL('L'),'L'[年] >= x0-3 && 'L'[年]  <= x0-1))
VAR x2 = CALCULATE(
            STDEV.P('L'[Ⅳ])
                ,FILTER(ALL('L'),'L'[年] >= x0-3 && 'L'[年]  <= x0-1))
VAR x3 = x1 + x2*3
VAR x4 = x1 - x2*3
VAR x5 = CALCULATE(
            COUNT('L'[Ⅳ])
                ,FILTER(ALL('L'),'L'[Ⅳ]<= x3 && 'L'[Ⅳ]>= x4))
VAR x6 = CALCULATE(
            COUNT('L'[Ⅳ])
                ,ALL('L'[年度]))

RETURN 

CALCULATE(
    DIVIDE(x5,x6,0)
        ,ALLSELECTED('L'[年度])
)