How to convert Text with Percentage symbol to number (DAX)

[Wangfl]

Hi,

Thank you in advance! Appreciated it if someone could help me out!

 I need to format these [Ratio] with %, however, How can I convert it back to numbers? Otherwise, I cannot use this measure[Actual 2] to do my next calculation... it will show error

Actual 2 =

VAR FiscalYear = MAX(DimTimeMonthly[Fiscal Yr])

VAR FiscalPeriod = MAX(DimTimeMonthly[Fiscal Period Yr])

VAR SerofPeriod =

FILTER(

    ALL(DimTimeMonthly), DimTimeMonthly[Fiscal Yr]= FiscalYear &&DimTimeMonthly[Fiscal Period Yr]=FiscalPeriod)

RETURN

CALCULATE(SWITCH(

    SELECTEDVALUE(PLStructure[Line Item ID]),

    "Net Sales",FORMAT([Net Sales],"#,##0,.0"),

    "Standard Cost",FORMAT([Standard Cost],"#,##0,.0"),

    "Total Supply Chain Variances",FORMAT([Total Supply Chain Variances],"#,##0,.0"),

    "LIFO / FIFO / Inventory Adjustments",FORMAT([LIFO / FIFO / Inventory Adjustments],"#,##0,.0"),

    "Direct Charges",FORMAT([Direct Charges],"#,##0,.0"),

    "Total Freight Expense",FORMAT([Total Freight Expense],"#,##0,.0"),

    "COGS Admin",FORMAT([COGS Admin],"#,##0,.0"),

    "Other COGS", FORMAT([Other COGS],"#,##0,.0"),

    "Gross Profit",FORMAT([Gross Profit],"#,##0,.0"),

    "Bad Debt",FORMAT([Bad Debt],"#,##0,.0"),

    "Local Selling",FORMAT([Local Selling],"#,##0,.0"),

    "Local Selling(Excl. Bad Debt)",FORMAT([Local Selling(Excl. Bad Debt)],"#,##0,.0"),

    "Region Selling",FORMAT([Region Selling],"#,##0,.0"),

    "Division Selling",FORMAT([Division Selling],"#,##0,.0"),

    "Local Marketing",FORMAT([Local Marketing],"#,##0,.0"),

    "Local R&D",FORMAT([Local RD],"#,##0,.0"),

    "Local Others",FORMAT([Local Others],"#,##0,.0"),

    "Local GA",FORMAT([Local GA],"#,##0,.0"),

    "Region GA",FORMAT([Region GA],"#,##0,.0"),

    "Corporate GA",FORMAT([Corporate GA],"#,##0,.0"),

    "Division GA",FORMAT([Division GA],"#,##0,.0"),

    "Local Shared",FORMAT([Local Shared],"#,##0,.0"),

    "Region Allocated",FORMAT([Region Allocated],"#,##0,.0"),

    "Global Allocated",FORMAT([Global Allocated],"#,##0,.0"),

    "Total SGA",FORMAT([Total SGA],"#,##0,.0"),

    "Operating Income",FORMAT([Operating Income],"#,##0,.0"),

    "LOI",FORMAT([LOI],"#,##0,.0"),

        "Ratio1",FORMAT([Ratio 1],"0.0%"),

        "Ratio2",FORMAT([Ratio 2],"0.0%"),

        "Ratio3",FORMAT([Ratio 3],"0.0%"),

        "Ratio4",FORMAT([Ratio 4],"0.0%"),

        "Ratio5",FORMAT([Ratio 5],"0.0%"),

        "Ratio6",FORMAT([Ratio 6],"0.0%"),

        "Ratio7",FORMAT([Ratio 7],"0.0%"),

        "Ratio8",FORMAT([Ratio 8],"0.0%"),

        "Ratio9",FORMAT([Ratio 9],"0.0%")

),EPM[Scenario]="Actual",SerofPeriod)

[Anonymous]

Hi @Wangfl ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Best Regards

Community Support Team _ Polly

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

[Anonymous]

Hi  @Wangfl ,
The problem is with this formula.   

FORMAT

 

Converts a value to text according to the specified format.

FORMAT function (DAX) - DAX | Microsoft Learn

Best Regards

Community Support Team _ Polly

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

[amitchandak]

@Wangfl , it is better to use a calculation group with such measures, there you can have a conditional format

https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/

Or  you can have two measures, one without format and one with format, to check back value