Power BI 矩阵中的多种数据类型 [英] Multiple data types in a Power BI matrix

问题描述

我在 10 个位置有大约 20 个不同的指标，我想制作一个矩阵，其中指标为行，位置为不同的列.我遇到的问题是指标是不同的数据类型，有些是整数，有些是 %s，有些是 $s.

I've got about 20 different metrics across 10 locations and want to make a matrix with metrics as rows and the locations as the different columns. The issue I'm running into is that the metrics are different data types, some are whole numbers, some are %s and some are $s.

有什么方法可以像在 excel 中那样将每一行自定义格式为不同的数据类型?

Is there any way to custom format each row as a different data type like there is in excel?

抱歉我不清楚.我不希望多次出现相同的值.请参阅下面的屏幕截图.

Sorry I wasn't clear. I don't want the same value showing up multiple times. See below screenshots.

测试数据截图:

我想要的，但我想要它在 Power BI 中，而不是 Excel:

What I want, but I want it in Power BI, not Excel:

当我使用格式化为不同数据类型的度量时我不想要的:

What I don't want when I use measures that are formatted as different data types:

推荐答案

格式不受行或列控制，而是可以使用建模"选项卡为每个度量分配自己的数据类型.

The formatting is not controlled by the rows or columns but rather each measure can be assigned its own data type using the Modeling tab.

我在这里看到了几个选项.

I see a few options here.

选项 1:编写一个文本度量来切换格式，如下所示:

Option 1: Write a text measure that switches formats like this:

FormatMetric =
VAR Val = SUM ( TestData[Value] )
RETURN
    SWITCH (
        SELECTEDVALUE ( TestData[Metric] ),
        "# quantity", FORMAT ( Val, "0" ),
        "$ Sales",    FORMAT ( Val, "$0.00" ),
        "% to plan",  FORMAT ( Val, "0%" )
    )

你会得到一个看起来像这样的表格:

You'll get a table that looks like this:

请注意，此度量会返回文本值，并且无法在图表中使用.

Be aware that this measure returns text values and won't work in a chart.

选项 2:创建三个单独的度量并分别设置格式:

Option 2: Create three separate measures and format each separately:

# quantity = CALCULATE ( SUM ( TestData[Value] ), TestData[Metric] = "# quantity" )
$ Sales    = CALCULATE ( SUM ( TestData[Value] ), TestData[Metric] = "$ Sales" )
% to plan  = CALCULATE ( SUM ( TestData[Value] ), TestData[Metric] = "% to plan" )

如果您确保已打开格式">值">在行上显示"并将这三个度量值放入值"框中:

If you make sure you have Format > Values > Show on rows turned on and put these three measures in the Values box:

这些度量可以用在图表中.

These measures can be used in charts.

选项 3:在查询编辑器的 Metric 列上旋转数据表，这样就不会在单个列中混合数据类型.您的数据表现在应如下所示:

Option 3: Pivot your data table on the Metric column in the query editor so you don't have mixed data types in a single column. Your data table should look like this now:

从这里，您可以像上一个选项一样编写三种简单的度量格式:

From here, you can write three simple measures format as in the previous option:

# quantity = SUM ( Pivot[# quantity] )
$ Sales    = SUM ( Pivot[$ Sales] )
% to plan  = SUM ( Pivot[% to plan] )

这篇关于Power BI 矩阵中的多种数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！