Multiple data types in a Power BI matrix

Question

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.

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

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

Test Data Screenshot:

Test Data Screenshot

What I want, but I want it in Power BI, not 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:

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

Alexis Olson Alexis Olson · Accepted Answer · 2020-02-24T20:44:21

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.

Edit: I see a few options here.

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.

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.

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] )

Multiple data types in a Power BI matrix

1 Answers