Power BI。矩阵两列在一列下 [英] Power BI. Matrix two columns under one column

问题描述

这是我需要的矩阵的所需布局。我需要在一列下有两列：

但是，我最终得到了这个（我使用了不同的值，所以值可能会略有不同。）：

在矩阵字段中包含以下列：

我进行的更改：

• A，B列的原始数据，并将结果列重命名为 Unit Type


• 删除 Total Units列，没有必要


• 将表命名为数据（您可以将其更改为所需的任何名称）。

然后，编写2个DAX度量。

度量1：

 单位= SUM（数据[值] ）
  

措施2：

 ％=除（[单位]，计算（[单位]，ALL（数据[状态]））））
  

然后，创建如下矩阵：

结果：

说明：

• 您需要取消透视数据才能将A和B类型放入同一列。这将允许您使用它们将数据切成矩阵行；


• 总单位是无用的列，因为我们可以轻松地使用DAX度量来重现它，然后它将是动态的（它将


• ％公式需要计算给定单位类型但在所有Status中的单位百分比。我们首先通过计算ALL（Data [STATUS]）的小计单位，然后将其除以小数来实现。

This is the desired layout of the matrix that I need. I need to have two columns under one column:

However, I ended up with this(I used different values, so the values might be slightly different.):

With the following columns in the matrix fields:

This is a sample of the dataset. Total Units sum up columns A and B. The Order column sorts the Status column:

STATUS  A        B     Total Units  Order 
ABC     3        0     3            1
DEF     0        6     6            2
ABC     3        2     5            1
ABC     5        6     11           1
GHI     0        4     4            3
ABC     5        3     8            1
DEF     0        9     9            2

How do I get my desired layout? Do I need to pivot the table? Or do I need to group certain values together?

解决方案

First, transform your data to this:

Changes I've made:

• Unpivoted source data for columns A, B, and renamed resulting column as "Unit Type"
• Deleted column "Total Units", it's unnecessary
• Named your table "Data" (you can change it to whatever name you prefer).

Then, write 2 DAX measures.

Measure 1:

Units = SUM(Data[Value])

Measure 2:

% = DIVIDE ( [Units], CALCULATE ( [Units], ALL ( Data[STATUS] ) ) )

Then, create a matrix as follows:

Result:

Explanation:

• You need to unpivot data to put A and B types into the same column. This will allow you to use them to slice data as matrix rows;
• Total Units is a useless column because we can easily reproduce it with DAX measure, and then it will be dynamic (it will respond to matrix rows, columns, slicers and other interactive controls);
• % formula needs to calculate % of units for a given unit type, but across all Status. We accomplish that by first calculating subtotal units for ALL(Data[STATUS]), and then dividing units by them.

这篇关于Power BI。矩阵两列在一列下的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！