Forum Discussion

SanjeevMurthy's avatar
SanjeevMurthy
Frequent Visitor
6 years ago
Solved

Different format in Target column

Hi,

 

I have a field [Target] which has target value for each of the Metric line item. The targets are not in the same data type format.

Example

Metric 1 - 20%

Metric 2 - 2

Metric 3 - 2.5

 

Request to please help with a way to store and show value in the original format.

 

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi SanjeevMurthy ,

    As epappu said, it can be implemented with TEXT type. For now, it is not supported to change different format and stain the original data type except text. 

    I created a measure use the function of IF and FORMAT. You can use it to show the values. When you want to calculate them, you could use the original column. 

    Measure = IF(SELECTEDVALUE('Table'[Metric]) = "Metric1",FORMAT(SUM('Table'[Values]),"00%"),SUM('Table'[Values]))

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.

5 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    Hi SanjeevMurthy ,

    As epappu said, it can be implemented with TEXT type. For now, it is not supported to change different format and stain the original data type except text. 

    I created a measure use the function of IF and FORMAT. You can use it to show the values. When you want to calculate them, you could use the original column. 

    Measure = IF(SELECTEDVALUE('Table'[Metric]) = "Metric1",FORMAT(SUM('Table'[Values]),"00%"),SUM('Table'[Values]))

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.

    • SanjeevMurthy's avatar
      SanjeevMurthy
      Frequent Visitor
      6 years ago

      Thank you all for the suggestions and help. This really helped. Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    If the values are all different, try setting the data type as Text and see if that works (unless you want to do calculations on that field)

    • SanjeevMurthy's avatar
      SanjeevMurthy
      Frequent Visitor
      6 years ago

      I need to do the calculation based on actual and target values.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        So, since I am unable to see the dataset/data model at my end, I am assuming the following and will try give you a response accordingly. Hope this helps you.

         

        Assuming your table is having two columns as below, you can try creating a measure with a formula to convert the value in the column Target by using VALUE function something similar to:

        =DIVIDE((Actual - VALUE(Target), Actual)

         

        Please note the above is not tested, but VALUE is supposed to convert Text to a number, so that should help your calculation, if you are looking to see the difference between Actual and Variance. You can also covert the above measure to a % in the modelling table to get the % difference.


        Table:

        Target (Text)Actual (Decimal)
        20%15
        21
        2.53

         

        Hope the above helps you.