Power BI如何将多个度量值以行而非列的形式展示在表格中？

问题描述 #

已在Power BI中创建5个带不同筛选条件的DAX度量值，计算结果正确，但添加到表格视觉对象时以列形式展示，不符合需求。期望得到如下行式表格结构：

现有DAX度量值代码如下：

Realised Savings Azure App Service v2 = 
CALCULATE(SUM('Usage details'[costInBillingCurrency]), FILTER ( 'Usage details', 'Usage details'[meterCategory] = "Azure App Service" && CONTAINSSTRING('Usage details'[meterName.1], "v2") ))

Realised Savings Azure App Service v3 = 
CALCULATE(SUM('Usage details'[costInBillingCurrency]), FILTER ( 'Usage details', 'Usage details'[meterCategory] = "Azure App Service" && CONTAINSSTRING('Usage details'[meterName.1], "v3") ))

Realised Savings DTU = 
CALCULATE(SUM('Usage details'[costInBillingCurrency]), FILTER ( 'Usage details', 'Usage details'[meterCategory] = "SQL Database" && CONTAINSSTRING('Usage details'[meterName.1], "DTU") ))

Realised Savings vCore = 
CALCULATE(SUM('Usage details'[costInBillingCurrency]), FILTER ( 'Usage details', 'Usage details'[meterCategory] = "SQL Database" && 'Usage details'[meterName.1] = "vCore" ) )

Realised Savings VMs = 
CALCULATE ( SUM ( 'Usage details'[costInBillingCurrency] ), FILTER ( 'Usage details', 'Usage details'[meterCategory] = "Virtual Machines"  ) )

解决方案 #

方法1：创建DAX计算表（推荐） #

直接通过DAX生成包含分类名称和对应值的行式计算表，步骤如下：

1. 在「建模」选项卡点击「新建表」
2. 输入以下DAX代码：

Realised Savings Summary = 
UNION(
    ROW("MeterCategory", "Realised Savings Azure App Service v2", "Total", [Realised Savings Azure App Service v2]),
    ROW("MeterCategory", "Realised Savings Azure App Service v3", "Total", [Realised Savings Azure App Service v3]),
    ROW("MeterCategory", "Realised Savings DTU", "Total", [Realised Savings DTU]),
    ROW("MeterCategory", "Realised Savings vCore", "Total", [Realised Savings vCore]),
    ROW("MeterCategory", "Realised Savings VMs", "Total", [Realised Savings VMs])
)

3. 点击「确定」后，将该计算表的MeterCategory和Total列拖入表格视觉对象，即可得到期望的行式结构。

方法2：维度表+通用度量值配合矩阵视觉对象 #

如果希望保留度量值的灵活性，可通过维度表+通用度量值实现：

1. 创建手动维度表：在「建模」选项卡点击「新建表」，输入以下代码生成分类列表：

Savings Categories = 
DATATABLE(
    "MeterCategory", STRING,
    {
        {"Realised Savings Azure App Service v2"},
        {"Realised Savings Azure App Service v3"},
        {"Realised Savings DTU"},
        {"Realised Savings vCore"},
        {"Realised Savings VMs"}
    }
)

2. 创建通用度量值：

Realised Savings Total = 
SWITCH(
    SELECTEDVALUE('Savings Categories'[MeterCategory]),
    "Realised Savings Azure App Service v2", CALCULATE(SUM('Usage details'[costInBillingCurrency]), 'Usage details'[meterCategory] = "Azure App Service" && CONTAINSSTRING('Usage details'[meterName.1], "v2")),
    "Realised Savings Azure App Service v3", CALCULATE(SUM('Usage details'[costInBillingCurrency]), 'Usage details'[meterCategory] = "Azure App Service" && CONTAINSSTRING('Usage details'[meterName.1], "v3")),
    "Realised Savings DTU", CALCULATE(SUM('Usage details'[costInBillingCurrency]), 'Usage details'[meterCategory] = "SQL Database" && CONTAINSSTRING('Usage details'[meterName.1], "DTU")),
    "Realised Savings vCore", CALCULATE(SUM('Usage details'[costInBillingCurrency]), 'Usage details'[meterCategory] = "SQL Database" && 'Usage details'[meterName.1] = "vCore"),
    "Realised Savings VMs", CALCULATE(SUM('Usage details'[costInBillingCurrency]), 'Usage details'[meterCategory] = "Virtual Machines"),
    BLANK()
)

3. 添加矩阵视觉对象：将Savings Categories表的MeterCategory拖到行区域，将Realised Savings Total度量值拖到值区域，即可显示行式表格。

方法3：Power Query逆透视列（静态数据场景） #

如果数据为静态导入，可先生成列式表格再转置：

1. 新建表格视觉对象，添加所有5个度量值，得到列式数据
2. 选中该表格，点击「转换数据」进入Power Query编辑器
3. 选中所有度量值列，点击「转换」选项卡的「逆透视列」
4. 将自动生成的Attribute列重命名为MeterCategory，Value列重命名为Total
5. 关闭并应用更改，即可得到行式表格。

内容的提问来源于stack exchange，提问作者Francesco Mantovani