总结值

Question

表格模型。表'Historic One Hour Value‘包含多个日期项目工作时间的小时值(如价格)。它只连接到项目表，而不连接到日期表。

我正在尝试计算小时的值。对于我的问题，假设每个项目只有一个小时。我得到了每个项目的正确答案，但总数是一个非常大的数字，我不明白。

我将感谢你的帮助。

One Hour Value On Date :=
VAR OneHourValueOnStartDate =
    FILTER (
        'Historic Project One Hour Value',
        'Historic Project One Hour Value'[HistoricOneHourValueBeginDate]
            = DATE ( 2016, 12, 31 )
    )
VAR OneHourValueOnStartDateForOneProject =
    1
        * /*Actually, I multiply the working hours of the project, but this number is correct */
        CALCULATE (
            SUM ( 'Historic Project One Hour Value'[HistoricOneHourValue] ),
            OneHourValueOnStartDate
        )
RETURN
    IF (
        HASONEVALUE ( Projects[Project ID] ),
        CALCULATE ( OneHourValueOnStartDateForOneProject ),
        CALCULATE (
            SUMX ( VALUES ( Hours[HoursProjectID] ), OneHourValueOnStartDateForOneProject )
        )
    )

Answer 1

变量OneHourValueOnStartDateForOneProject是一个常量(而不是一个度量)，所以在合计中发生的情况是，这个值给出了VAR计算部分中所有Project ID值的总和。

在此基础上，您的SUMX将该常量值相加的次数与不同的HoursProjectID相同。

Answer 2

Alexis Olson回答后的正确代码：

One Hour Value On Date:=
VAR OneHourValueOnStartDate =
FILTER (
'Historic Project One Hour Value',
'Historic Project One Hour Value'[HistoricOneHourValueBeginDate]
            = date(2016,12,31)    
)
VAR OneHourValueOnStartDateForOneProject =
1 *   /*Actually, I multiply the working hours of the project, but this number is correct */
CALCULATE (
SUM( 'Historic Project One Hour Value'[HistoricOneHourValue] ),
        OneHourValueOnStartDate
        )
RETURN
IF (
HASONEVALUE ( Projects[Project ID] ),
 CALCULATE ( OneHourValueOnStartDateForOneProject ),
CALCULATE (
  SUMX (
                Values ( Hours[HoursProjectID] ),
                1
      * CALCULATE (
                        MAX ( 'Historic Project One Hour Value'[HistoricOneHourValue] ),
                        FILTER (
                            OneHourValueOnStartDate,
                            'Historic Project One Hour Value'[HistoricOneHourValueProjectID]
                            = EARLIER ( 'Hours'[HoursProjectID] )
                        )
                    )
            )

        )
    )

谢谢