P&L Finance Report - Format problems, how to format data in Percentage and Thousand

[Wangfl]

Hello,

I am struggling with the Format problems. Is there any way to display the Ratio (got 9 Ratios) in P&L as a percentage, and other line items as thousands? 

I have tried to add DAX formula "Format()" to each item in measure [Actual], but it will poop out an error saying "cannot convert the value of type text to number"

Appreciate it if anyone could help me out! 🙂 Thank you guys~

Actual =

VAR PosNeg = MAX(PLStructure[Expense])

VAR Result =

CALCULATE(SWITCH(

    SELECTEDVALUE(PLStructure[Line Item ID]),

    "Net Sales",[Net Sales]*PosNeg,

    "Standard Cost",[Standard Cost]*PosNeg,

    "Total Supply Chain Variances",[Total Supply Chain Variances]*PosNeg,

    "LIFO / FIFO / Inventory Adjustments",[LIFO / FIFO / Inventory Adjustments]*PosNeg,

    "Direct Charges",[Direct Charges]*PosNeg,

    "Total Freight Expense",[Total Freight Expense]*PosNeg,

    "COGS Admin",[COGS Admin]*PosNeg,

    "Other COGS", [Other COGS]*PosNeg,

    "Gross Profit",[Gross Profit]*PosNeg,

    "Bad Debt",[Bad Debt]*PosNeg,

    "Local Selling",[Local Selling]*PosNeg,

    "Local Selling(Excl. Bad Debt)",[Local Selling(Excl. Bad Debt)]*PosNeg,

    "Region Selling",[Region Selling]*PosNeg,

    "Division Selling",[Division Selling]*PosNeg,

    "Local Marketing",[Local Marketing]*PosNeg,

    "Local R&D",[Local RD]*PosNeg,

    "Local Others",[Local Others]*PosNeg,

    "Local GA",[Local GA]*PosNeg,

    "Region GA",[Region GA]*PosNeg,

    "Corporate GA",[Corporate GA]*PosNeg,

    "Division GA",[Division GA]*PosNeg,

    "Local Shared",[Local Shared]*PosNeg,

    "Region Allocated",[Region Allocated]*PosNeg,

    "Global Allocated",[Global Allocated]*PosNeg,

    "Total SGA",[Total SGA]*PosNeg,

    "Operating Income",[Operating Income]*PosNeg,

    "LOI",[LOI]*PosNeg,

        "Ratio1",[Ratio 1],

        "Ratio2",[Ratio 2],

        "Ratio3",[Ratio 3],

        "Ratio4",[Ratio 4],

        "Ratio5",[Ratio 5],

        "Ratio6",[Ratio 6],

        "Ratio7",[Ratio 7],

        "Ratio8",[Ratio 8],

        "Ratio9",[Ratio 9]

),EPM[Scenario]="Actual")

Return

Result

[Anonymous · Accepted solution]

Hi @Wangfl ,

You can change data format directly here:

Best regards,

Community Support Team Selina zhu

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

[daXtreme]

This is how you should properly create a P&L statement in Power BI.