DAX测量以使用其内部的参数计算平均值

本文介绍了DAX测量以使用其内部的参数计算平均值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这样的数据

App_Num Days Price A1 10 100 A1 11 150 A2 11 200 A3 12 250 A3 12 300 A4 20 350 A4 21 400

平均天数显示在卡片上，为13.857.

The average of the days is displayed on a card visual as 13.857.

现在，设置了两个参数供用户调整值并查看.

Now, there are two parameters that are set for user to adjust the values and see.

总值(最小和最大范围)

天

例如，如果用户选择0-280，则期望列出A1(100 + 150 = 250小于280)和A2(200小于280).

For example, if the user selects 0-280- it is expected to list A1 (100 + 150 = 250 less than 280) and A2 (200 being less than 280).

我使用了这样的DAX并建立了这样的表格，

I used a DAX like this and built a table like this,

Apps_in_scope = Var min_amount = Min('Total Value'[Total Value]) Var max_amount = Max('Total Value'[Total Value]) var required_app_num = SELECTEDVALUE(Table1[App_Num]) Var required_amount = CALCULATE(sum(Table1[Price]),FILTER(Table1,Table1[App_Num] = required_app_num)) var in_scope = if(And(required_amount <= max_amount, required_amount >= min_amount),1,0) return in_scope

我能够产生这样的视觉效果

And I was able to produce a Visual like this,

App_Num Apps_in_scope A1 1 A2 1 A3 0 A4 0

现在，在选择了总价格范围后，如果用户手动将days参数选择为15，那么我的平均值将按照此逻辑进行移动.

Now after selecting the total price range, if the user selects the days parameter manually to be 15 then my average will shift as per this logic.

• A1有2笔交易，并且在选定的价格范围内280将变为(15 * 2)
• A2有1笔交易，并且在选定的280个价格范围内变为(15 * 1)
• A3有2笔交易，将保持不变(12 + 12)
• A4有2笔交易，将保持不变(20 + 21)

因此，我希望在卡片上放置的新尺寸现在可以显示(15 + 15 + 15 + 12 + 12 + 20 + 21)/7 = 15.714

So my new measure which I want to place on the card is expected to show now (15+15+15+12+12+20+21)/7 = 15.714

我该如何写这个量度.请帮助我

How can I write this measure. Kindly help me with this

推荐答案

我会略微调整您的度量，以便更好地求平均值:

I'd tweak your measure slightly so that it works better for taking the average:

Apps_in_scope_2 = VAR min_amount = MIN ( 'Total Value'[Total Value] ) VAR max_amount = MAX ( 'Total Value'[Total Value] ) VAR required_amount = CALCULATE ( SUM ( Table1[Price] ), ALLEXCEPT ( Table1, Table1[App_Num] ) ) VAR in_scope = IF ( AND ( required_amount <= max_amount, required_amount >= min_amount ), 1, 0 ) RETURN in_scope

通过这种调整，平均值非常简单:

With this tweak the average is fairly simple:

AvgMeasure = VAR DaysParam = SELECTEDVALUE ( DaysSlicer[Days] ) RETURN AVERAGEX( Table1, IF( [Apps_in_scope_2] = 1, DaysParam, Table1[Days] ) )

这是一个替代版本，它不使用第一种方法，但是应该可以更好地扩展到大型数据表.

Here's an alternative version that doesn't use the first measure but should scale better to large data tables.

AvgAlternate = VAR min_amount = MIN ( 'Total Value'[Total Value] ) VAR max_amount = MAX ( 'Total Value'[Total Value] ) VAR DaysParam = SELECTEDVALUE ( DaysSlicer[Days] ) VAR apps = ADDCOLUMNS ( SUMMARIZE ( Table1, Table1[App_Num], "@Price", SUM ( Table1[Price] ), "@Rows", COUNT ( Table1[Price] ) ), "@Days", IF ( AND ( [@Price] <= max_amount, [@Price] >= min_amount ), DaysParam * [@Rows], CALCULATE ( SUM ( Table1[Days] ) ) ) ) RETURN DIVIDE ( SUMX ( apps, [@Days] ), SUMX ( apps, [@Rows] ) )