Multiple Data Formats in Matrix

[JP_G]

Hello,

I was looking for some continued help on my Matrix I'm building:

My original thread I was able to get the data to display in the Matrix very well however, now that the values are all in one column after unpivoting. In the matrix, they are showing as general decimal numbers, while I need them to show currency, percentages, etc...

So here's my current data structure

Plant	Month Number	Month Name	Metric	Actual Value
Michigan	1	January	Production Sales	$100.00
Michigan	1	January	Sales Goal	100%

And I have two of these tables, one that's Goals and one that's Actuals.

Then in my matrix they present as:

Rows: Metric

Column: Month Name

Values: Actual Value, Goal Value

Month Name	January
Metric	Actual	Goal
Production Sales	100	100
Sales Goal	1.00	1.00

Just looking for some help, ideally don't want to use it as text but not sure if I can pivot some other way. 

Thank you!

[FarhanJeelani · Accepted solution]

Hi @JP_G ,

Power BI does not support applying multiple formats to a single column directly. However, you can use a DAX measure to dynamically format your values.

1. Create a new measure for Formatted Actual Value:

   Formatted Actual Value = 
   SWITCH(
       TRUE(),
       SELECTEDVALUE('Table'[Metric]) = "Production Sales", FORMAT(SUM('Table'[Actual Value]), "$#,##0.00"),
       SELECTEDVALUE('Table'[Metric]) = "Sales Goal", FORMAT(SUM('Table'[Actual Value]), "0%"),
       FORMAT(SUM('Table'[Actual Value]), "#,##0.00")
   )

2. Create a similar measure for Formatted Goal Value:

   Formatted Goal Value = 
   SWITCH(
       TRUE(),
       SELECTEDVALUE('Table'[Metric]) = "Production Sales", FORMAT(SUM('Table'[Goal Value]), "$#,##0.00"),
       SELECTEDVALUE('Table'[Metric]) = "Sales Goal", FORMAT(SUM('Table'[Goal Value]), "0%"),
       FORMAT(SUM('Table'[Goal Value]), "#,##0.00")
   )

3. Replace the Actual Value and Goal Value fields in the matrix with the respective formatted measures.

   Outcome: This will display the values with the correct formats in your matrix.

Please mark this as solution if it helps you. Appreciate kudos.

[collinq]

Hi @JP_G ,

On the fields themselves you can change the formatting on how they display.  One way is to just select the field and then customize how it appears.

As for the order in the matrix itself, just change which field is "Rows" vs "Columns" vs "Values"