时间戳

首页 > 解决方案 > PowerBI 中需要的多个平均水平

powerbi - PowerBI 中需要的多个平均水平

问题描述

从这种形式的初始数据开始：

表格1

PARENT         BRAND      SUB BRAND   AVG INCREASE
PARENT ONE     BRAND ONE     1           9.90%
PARENT ONE     BRAND ONE     2           8.70%
PARENT ONE     BRAND ONE     3           5.00%
PARENT TWO     BRAND TWO     4           4.90%
PARENT TWO     BRAND THREE   5           9.50%
PARENT TWO     BRAND FOUR    6           4.90%
PARENT TWO     BRAND FIVE    7           4.90%
PARENT THREE   BRAND SIX     8         -96.90%
PARENT THREE   BRAND SIX     9         -50.00%
PARENT THREE   BRAND SIX     10        -33.00%
PARENT THREE   BRAND SEVEN   11        -70.00%
PARENT THREE   BRAND SEVEN   12        -89.00%

1) 我正在使用 TABLE1 计算父母平均增加的平均值，如下所示：

PARENT AVG = 
AVERAGEX(
    SUMMARIZE (
        TABLE1,
        TABLE1[PARENT],
        TABLE1[BRAND],
        "Brand Average", AVERAGE (TABLE1[AVG INCREASE] )
    ),
    [Brand Average]
)

要获得以下信息：

PARENT         BRAND          AVG INCREASE
PARENT ONE     BRAND ONE       7.87%
PARENT TWO     BRAND TWO       4.90%
PARENT TWO     BRAND THREE     9.50%
PARENT TWO     BRAND FOUR      4.90%
PARENT TWO     BRAND FIVE      4.90%
PARENT THREE   BRAND SIX     -59.97%
PARENT THREE   BRAND SEVEN   -79.50%

我将如何获得 PARENT 的整体 AVG？

它应该是每个父母的平均值：

PARENT ONE    7.87%
PARENT TWO    6.05%
PARENT THREE  -69.74

父母的平均人数 = -18.61%

标签： powerbidax

解决方案

您可以重复相同的过程，但添加一个级别：

AvgIncrease =
AVERAGEX (
    SUMMARIZE (
        Table1,
        Table1[Parent],
        "Parent Avg", AVERAGEX (
            SUMMARIZE (
                TABLE1,
                TABLE1[PARENT],
                TABLE1[BRAND],
                "Brand Average", AVERAGE ( TABLE1[AVG INCREASE] )
            ),
            [Brand Average]
        )
    ),
    [Parent Avg]
)

如果将[PARENT AVG]公式保存为度量，则可以在另一个度量中使用它，如下所示：

AvgIncrease =
AVERAGEX (
    SUMMARIZE (
        Table1,
        Table1[Parent],
        "Parent Average", [PARENT AVG]
    ),
    [Parent Average]
)

推荐阅读