3

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
  • 38,724
  • 7
  • 42
  • 64
Nick
  • 43
  • 1
  • 4

1 Answers1

5

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:

Matrix

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:

Matrix 2

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:

Pivot

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] )
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Thanks, Alexis. I have multiple measures that I've formatted differently for charts/graphs where there are only the same data types on one graph. I'm trying to get a matrix that doesn't have multiple sub column headers for each measure with repeat values just as different formats. I don't want the value 20 to show up as 20, 2000% and $20.00. Is there no way to show just the one column with the custom format a la excel? – Nick Feb 24 '20 at 20:52
  • I'm not quite sure I follow. Why do you want the same values showing up several times in the first place? Can you edit in a screenshot? – Alexis Olson Feb 24 '20 at 21:05
  • 1
    One day (hopefully this year) Microsoft will make Calculated Groups available in Power BI, to solve problems like this. https://learn.microsoft.com/en-us/analysis-services/tabular-models/calculation-groups?view=asallproducts-allversions – RADO Feb 24 '20 at 21:55
  • Thanks, Alexis! You're a life saver, my backup plan was to do it in excel and pin that to a dashboard which wouldn't have looked good. The text option works best for me since I have an indicator already in my data saying what the metrics are measured in (%, $ or #) and can use that instead of the metric name to format it. – Nick Feb 25 '20 at 14:36