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

问题描述

我正在尝试在Power BI报告中创建一个矩阵，该矩阵总结了以多种不同方式切分的Salesperson绩效。

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. 向我的矩阵中添加其他汇总列，以显示值，例如按销售人员划分的平均销售金额，按销售人员划分的销售总数的百分比。

为清楚起见，我想这将导致一个矩阵，其中列标题显示为：Salesperson，Product 1，Product 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

添加更多产品或年份后，该表将自动扩展。


（注意：索引列是，因此我可以使用按列排序对它们进行正确排序。）

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 f我为此创建的文件：

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

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

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