DAX测量以使用其内部的参数计算平均值 [英] DAX Measure to calculate average with Parameters inside it

问题描述

我有这样的数据

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.

1. 总值(最小和最大范围)
2. 天

例如，如果用户选择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] ) )

这篇关于DAX测量以使用其内部的参数计算平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！