Converting value to percentage using FORMAT but it converts value to text instead of decimal.

[johnmenx]

Analysis Metric =

Hi, so due to some technical implementations on the page instead of using field parameters in a table I created a table using Enter Data with 3 valies: Sales, Profit, Achievement. And I am passing the following measure into a table and a line chart, based on slicer selection, it will display either Sales. Profit or Achievement percemt:

IF(

    SELECTEDVALUE('Sales Analysis Metric'[Metric]) = "Sales",

    [Sales Measure],

    IF(

        SELECTEDVALUE('Sales Analysis Metric'[Metric]) = "Profit",

        [Profit Measure],

        FORMAT([Achievement], "Percent")

    )

)

I have used the FORMAT function to format the achievement % to show in percentage format but this actually converts it into a text value and so it displays fine in table but doesn't display in the line chart. How do I get around this? Setting the whole measure or the specific column to percentage values wont work as it only needs to show in percent format when Achievement is selected in the slicer and rest of the two are normal decimal numbers.

[Anonymous · Accepted solution]

Hi @johnmenx 

You can try the Dynamic format strings for measures:

Create dynamic format strings for measures in Power BI Desktop - Power BI | Microsoft Learn

Introducing dynamic format strings for DAX measures - SQLBI

Example:

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Anonymous]

Unfortunately thats just how the FORMAT function works. It will always convert the value to text and display it in the specified format. You should be ok if you put the number value on the axis, the line will still show the same trend, and if you still wanted to visibly see a percentage somewhere you can add in the formatted measure anywhere that accepts text values i.e. data labels on the line graph.