Forum Discussion

JP_G's avatar
JP_G
Frequent Visitor
1 year ago
Solved

Multiple Data Formats in Matrix

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

PlantMonth NumberMonth NameMetricActual Value
Michigan1JanuaryProduction Sales$100.00
Michigan1JanuarySales Goal100%

 

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 NameJanuary
MetricActualGoal
Production Sales100100
Sales Goal1.001.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!

 

Need Help
  • FarhanJeelani's avatar
    FarhanJeelani
    1 year ago

    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.

2 Replies

  • collinq's avatar
    collinq
    Super User
    1 year ago

    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"

  • FarhanJeelani's avatar
    FarhanJeelani
    Super User
    1 year ago

    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.