SUMX、SUMMY和MISSING值 [英] SUMX, SUMMARIZE, and missing values

问题描述

我有一个有点复杂的场景，我正尝试使用PowerPivot在Excel中构建该场景。我们有销售渠道项目，每个项目代表一个销售机会，并与这些渠道中的每一个相关联，有许多事件。我有管道和事件的历史记录表，我想用它们来构建一个模型，说明给定销售主管的整个管道集是如何随时间变化的。

我的SQL查询的结果集可能如下所示：

Pipeline_id	date_last_mod	管道事件id	金额
P-01	2020年1月1日	PE-01	100
P-02	2020年1月1日	PE-02	500
P-02	2020年1月2日
P-02	2020年1月2日	PE-02	1000
P-03	2020年1月1日	PE-03	200
P-03	2020年3月1日	PE-03	200
P-03	2020年4月1日	PE-04	300
P-03	2020年5月1日	PE-03	400
P-03	2020年5月15日	PE-03	300

我还有一个包含常用字段的日期表。我试图生成的是如下所示的数据透视表。您可以看到，它采用每个管道事件的最新"金额"值，并将这些值合计为每个月底的值。

年份	月	金额
2020	1	800
2020	2	800
2020	3	800
2020	4	1100
2020	5	1200

看起来解决方案应该是SUMX和LASTNONBLANK。我有一个类似以下内容的度量值：

TestRev:=VAR MaxDate =
    MAX ( qryPipelineUS[date_last_mod] )
        RETURN
SUMX( ADDCOLUMNS( SUMMARIZE( qryPipelineUS, qryPipelineUS[pipeline_id], qryPipelineUS[pipeline_event_id] ), "temp",
CALCULATE (
                SUM ( qryPipelineUS[amount] ),
    LASTNONBLANK( qryPipelineUS[date_last_mod], CALCULATE( SUM( qryPipelineUS[amount] ) ) ),
                ALL( qryDateTable ),
                ALLEXCEPT( qryPipelineUS, qryPipelineUS[pipeline_event_id] ),
                qryPipelineUS[date_last_mod] <= MaxDate ) ), [temp] )

它的问题似乎是汇总函数在没有条目的月份中不会为Pipeline_event_id生成行。说得通，真的。即使我尝试使用CROSSJOIN执行某些操作，我也可以使用所需的ID生成表，但无法使其生成数据。

如果有任何关于我应该如何做的建议，我将不胜感激。这个问题我已经绞尽脑汁好几个星期了，但一无所获。提前谢谢。

推荐答案

此处的关键是对日期表而不是事实表进行日期计算，并删除汇总表上的日期筛选器。

下面是我如何调整您的度量值的方法：

TestRev =
VAR MaxDate =
    MAX ( qryDateTable[Date] )
RETURN
    SUMX (
        ADDCOLUMNS (
            CALCULATETABLE (
                SUMMARIZE (
                    qryPipelineUS,
                    qryPipelineUS[pipeline_id],
                    qryPipelineUS[pipeline_event_id]
                ),
                ALL ( qryDateTable )
            ),
            "temp",
                CALCULATE (
                    LASTNONBLANKVALUE (
                        qryPipelineUS[date_last_mod],
                        CALCULATE ( SUM ( qryPipelineUS[amount] ) )
                    ),
                    qryDateTable[Date] <= MaxDate
                )
        ),
        [temp]
    )

编辑：在Excel中，无法识别LASTNONBLANKVALUE，因此您可以使用此扩展版本：

Test =
VAR MaxDate = MAX ( qryDateTable[Date] )
RETURN
    SUMX (
        ADDCOLUMNS (
            CALCULATETABLE (
                SUMMARIZE (
                    qryPipelineUS,
                    qryPipelineUS[pipeline_id],
                    qryPipelineUS[pipeline_event_id]
                ),
                ALL ( qryDateTable )
            ),
            "temp",
                CALCULATE (
                    CALCULATE (
                        SUM ( qryPipelineUS[amount] ),
                        LASTNONBLANK (
                            qryPipelineUS[date_last_mod],
                            CALCULATE ( SUM ( qryPipelineUS[amount] ) )
                        )
                    ),
                    qryDateTable[Date] <= MaxDate
                )
        ),
        [temp]
    )

请注意，日期过滤NOT与LASTNONBLANK包含在相同的内部计算表达式中。如果这样做，度量值将不会以相同方式工作。

这篇关于SUMX、SUMMY和MISSING值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！