Is it possible to retain formatting strings in a "helper table" for use in a matrix?

[djallarii]

Hi Power BI Community,

I am trying to create a kind of "balanced scorecard/stop light report" using a matrix in Power BI and I am curious if the formatting of individual rows or cells can be altered independlty of eachother or by storing formatting strings or conditional formatting in a "helper table".

I will try my best to explain in the example below.

Let's say our data is stored like the following table where Measures will be populated in the matrix rows, Header Group will be populated in the matrix columns, Values will be populated in the matix values, and Header Sort is used to maintain proper sort order.

Data Table Example
Measures	Values	Header Group	Header Sort
Measure 1	0.045	1/1/2023	1
Measure 2	1214	1/1/2023	1
Measure 3	2225000	1/1/2023	1
Measure 1	0.015	2/1/2023	2
Measure 2	1544	2/1/2023	2
Measure 3	1804655	2/1/2023	2
Measure 1	0.05	Target	99
Measure 2	1250	Target	99
Measure 3	2000000	Target	99

Without any formatting the resulting matrix looks like this:

Measures	Jan-23	Feb-23	Target
Measure 1	0.045	0.015	0.05
Measure 2	1214	1544	1250
Measure 3	2225000	1804655	2000000

In our legacy system, we can store formatting in a helper table such as this:

Format Table
Measures	Format	Polarity
Measure 1	Percentage	Value < Target = Green
Measure 2	Whole numbers, commas	Value < Target = Red
Measure 3	Decimal, abbreviated K/M/B	Value < Target = Red

And once we relate our data table to the format table, the resulting report is represented like this:

Resulting Matrix
Measures	Jan-23	Feb-23	Target
Measure 1	4.5%	1.5%	5%
Measure 2	1,214	1,544	1,250
Measure 3	2.23M	1.80M	2.00M

As you can see, the values of Measure 1 need to be represented as a percentage, Measure 2 needs to be represented as a whole number with commas, and Measure 3 needs to be represented as a decimal number with thousands/millions/billions abbreviated. Each measure needs a color coding based on its relationship to the target value.

If anyone can use these examples to create an example file or point me in the direction of online tutorials, I would greatly appreciate it!

[ray_aramburo · Accepted solution]

Sure you can.

First create the logic to format each measure, for example, Measure1Color = IF(SUM(Table[Value]) < Target, "Green", "Black") 

You can also use a Hex Code instead of the standard naming convention. Create the rest of the measures.

Then, go to the formatting pane of the matrix and go to submenu Cell elements.

There select a series (each measure in this case), turn on Font color and click on Fx. There select By Field Value in Format style:

Search for the color measure that corresponds to it and select it. Finally, click OK. 

For decimals and % format you can do that at a measure level in the Measure Tools menu when selecting the measure.