power bi 矩阵中的自定义聚合列 [英] Custom aggregate column in power bi matrix

问题描述

我正在尝试在 Power BI 报告中创建一个矩阵，以总结以多种不同方式划分的销售人员绩效.

I'm trying to create a matrix in a Power BI report summarizing Salesperson performance sliced in a number of different ways.

我知道如何创建一个矩阵，其中包含行 - 销售人员、列 - 产品类型和值 - 销售数量，这将显示每种产品类型的每个销售人员的销售数量，但我也希望能够做到以下:

I know how to create a matrix with Rows - Salesperson, Columns - Product Type, and Values - count of Sales which will show the number of Sales per Salesperson per Product Type, but I'd like also be able to do the following:

1. 添加一个额外的列设置为透视(例如销售年份)，这样我就可以在同一个表中并排看到按产品类型和年份进行透视的销售计数(即未嵌套).
2. 在我的矩阵中添加额外的汇总列，显示销售人员的平均销售金额、销售人员销售总数的百分比等值.

为了清楚起见，我想这会产生一个矩阵，其中列标题为:销售人员、产品 1、产品 2、...、第 1 年、第 2 年、...、总销售额、平均销售额，占总销售额的百分比.请参阅下面的图片链接(我还没有声誉点来包含实际图片)

For clarity, I'd imagine that this would result in a matrix where the column headers read: Salesperson, Product 1, Product 2, ..., Year 1, Year 2, ..., Total Sales Count, Average Sales Amount, % of Total Sales Count. See image link below (I don't have the reputation points to include the actual image yet)

我认识到我可以通过创建有效复制矩阵如何拆分值并将每个度量添加为值(无列)的度量来做到这一点，但我不想创建新的度量并更新每年或每次我们添加新产品类型时的矩阵.

I recognize that I can do this by creating measures which effectively replicate how the matrix is splitting out the values and adding each measure as a value (no Columns), but I don't want to have to create new measures and update the matrix every year or every time we add a new Product Type.

我还查看了 Power BI 市场上的自定义视觉效果，但没有看到任何可以实现这一目标的方法.

I've also looked at custom visuals on the Power BI marketplace, but didn't see any that would achieve this.

推荐答案

可以做到这一点，但并不容易.您需要一个带有 SWITCH 的度量值以及一个用于标题的表格.

It's possible to do this, but not super easy. You'll need a measure with a SWITCH as well as a table for your headers.

您可以按照以下方式创建标题表:

You can create a header table along these lines:

Header =
UNION (
    SUMMARIZE ( Sales, Sales[Product], "Group", "By Product", "Index", 1 ),
    SUMMARIZE ( Sales, Sales[Year], "Group", "By Year", "Index", 2 ),
    DATATABLE (
        "Header", STRING,
        "Group", STRING,
        "Index", INTEGER,
        {
            { " Total", "Summarizations", 3 },
            { "% of Total Sales", "Summarizations", 3 },
            { "Avg Sale Size", "Summarizations", 3 }
        }
    )
)

看起来像这样:

Header,            Group,          Index,
Product 1,         By Product,      1,
Product 2,         By Product,      1,
2016,              By Year,         2,
2017,              By Year,         2,
2018,              By Year,         2,
 Total,            Summarizations,  3,
% of Total Sales,  Summarizations,  3,
Avg Sale Size,     Summarizations,  3

当添加更多产品或年份时，此表将自动扩展.
(注意:Index 列是为了让我可以使用按列排序正确地对它们进行排序.)

This table will automatically expand when more products or years are added.
(Note: The Index column is so I can order them properly using Sort by Column.)

一旦你有了它，你只需要将 Group 和 Header 放在矩阵视觉的列上，并将 Salesperson 放在行上，在值中有一个切换度量.

Once you have that, you just need to put Group and Header on the columns of a matrix visual and Salesperson on the rows, with a switching measure in the values.

Measure =
VAR Val =
    SWITCH (
        SELECTEDVALUE ( Header[Group] ),
        "By Product", CALCULATE (
            SUM ( Sales[Amount] ),
            FILTER ( Sales, Sales[Product] = MAX ( Header[Header] ) )
        ),
        "By Year", CALCULATE (
            SUM ( Sales[Amount] ),
            FILTER ( Sales, Sales[Year] = VALUE ( MAX ( Header[Header] ) ) )
        ),
        SWITCH (
            SELECTEDVALUE ( Header[Header] ),
            "% of Total Sales", DIVIDE (
                SUM ( Sales[Amount] ),
                CALCULATE ( SUM ( Sales[Amount] ), ALL ( Sales ) )
            ),
            "Avg Sale Size", AVERAGE ( Sales[Amount] ),
            SUM ( Sales[Amount] )
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( Header[Header] ) = "% of Total Sales",
        FORMAT ( Val, "0.0%" ),
        FORMAT ( Val, "0.0" )
    )

每个不同的组都有自己的计算，我们必须使用 FORMAT 函数来强制表格正确格式化百分比函数.

Each different group gets its own calculation and we have to use the FORMAT function to force the table to format the percentage function properly.

(注意:如果您有切片器或过滤器，您可能想要使用 ALLSELECTED，而我在上面使用了 ALL.)

(Note: If you have slicers or filtering, you probably want to use ALLSELECTED where I used ALL above.)

这是我的表格的样子(不是完全相同的数据，而是相似的结构)

Here's what my table looks like (not the exact same data but similar structure)

这是我为此创建的 PBIX 文件:

and here's the PBIX file I created for this:

https://drive.google.com/file/d/1qxc5p53MgmOm-NH3EcivkZLhLeEHpr4R/

这篇关于power bi 矩阵中的自定义聚合列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！