Format value to currency and percent in the same row and get a numeric output.

[SebastianOliver]

Format value to currency and percent in the same row, and get a numeric output to sort them as numeric, not by alphabet.

My output is a table that I want to sort by the value. The problem is that the value right now is a text.

I leave the formula that I'm using:

Formatted Value = 
VAR Result = 
IF(
    SUM('Main Table'[VALUE]) <> 0,
    SWITCH(
        MAX('Main Table'[PL_ACCOUNT_TYPE_NAME]),
        "Revenue", CONVERT(FORMAT(SUM('Main Table'[VALUE]), "Currency"), CURRENCY),
        "Contribution Margin", FORMAT(SUM('Main Table'[VALUE]), "$#0,0"),
        "Gross Margin", FORMAT(SUM('Main Table'[VALUE]), "$#0,0"),
        "Overhead", FORMAT(SUM('Main Table'[VALUE]), "$#0,0"),
        "Non-Labor Operating Costs", FORMAT(SUM('Main Table'[VALUE]), "$#0,0"),
        "Total Labor Costs", FORMAT(SUM('Main Table'[VALUE]), "$#0,0"),
        "Gross Margin %", FORMAT(FORMAT(SUM('Main Table'[VALUE]), "Percent"), "General Number"),
        (AVERAGE('Main Table'[VALUE])* 100) & "%"
    )
)
Return
Result

[amitchandak]

@SebastianOliver , I think you should is Dynamic format string for this

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-the-new-dynamic-format-strings-for-measures/

Power BI Dynamic Format Strings for Measures| Format without converting to text: https://youtu.be/qHALucET7N8