Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Unpivoted data, wrote a switch function to format percentages and currency, not pulling through?

I am putting together a P&L report where I want to display the line items in a matrix. It has a mix of percentage and currency values things like standard margin. I created a measure and wrote the following switch statement (snippet below) and I'm not getting any errors, but the values are not coming through

Formatted Value =
SWITCH(
    SELECTEDVALUE('Merchandising P&L'[Header]),
    "DC cost %", FORMAT(SELECTEDVALUE('Merchandising P&L'[Value]),"Percent"),
    "Freight cost %", FORMAT(SELECTEDVALUE('Merchandising P&L'[Value]),"Percent"),
    "G2N% (Gross Sales)", FORMAT(SELECTEDVALUE('Merchandising P&L'[Value]),"Percent"),
    "Operational Gross Margin% (COPA)", FORMAT(SELECTEDVALUE('Merchandising P&L'[Value]),"Percent"),
    "SM%", FORMAT(SELECTEDVALUE('Merchandising P&L'[Value]),"Percent"),
    "Total SM%", FORMAT(SELECTEDVALUE('Merchandising P&L'[Value]),"Percent"),
    "COS", FORMAT(SELECTEDVALUE('Merchandising P&L'[Value]),"Currency"),
    "Distribution Center Cost", FORMAT(SELECTEDVALUE('Merchandising P&L'[Value]),"Currency"),
    "FIFO", FORMAT(SELECTEDVALUE('Merchandising P&L'[Value]),"Currency"))
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    First of all, thanks HotChilli  for the quick reply. 

    You can try it.

    Formatted Value2 = SUM('Merchandising P&L'[Value])
    SWITCH(
    SELECTEDVALUE('Merchandising P&L'[Header]),
    "DC cost %", "00.00%",
    "Freight cost %", "00.00%",
    "G2N% (Gross Sales)", "00.00%",
    "Operational Gross Margin% (COPA)", "00.00%",
    "SM%", "00.00%",
    "Total SM%", "00.00%",
    "COS", "$0.00",
    "Distribution Center Cost", "$0.00",
    "FIFO", "$0.00")

    '

     

     

    Best Regards,

    Neeko Tang

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

4 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Normally it's very hard to look at a piece of DAX and diagnose problems without a data sample or the context of the visual.

    However, it looks like the generous use of FORMAT has scuppered your values, turning them into strings.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I've tried about a half dozen different things between conditional columns and I'm just not getting anywhere 😛 I saw this work on a less complex example posted elsewhere, but I'm not having any luck

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    First of all, thanks HotChilli  for the quick reply. 

    You can try it.

    Formatted Value2 = SUM('Merchandising P&L'[Value])
    SWITCH(
    SELECTEDVALUE('Merchandising P&L'[Header]),
    "DC cost %", "00.00%",
    "Freight cost %", "00.00%",
    "G2N% (Gross Sales)", "00.00%",
    "Operational Gross Margin% (COPA)", "00.00%",
    "SM%", "00.00%",
    "Total SM%", "00.00%",
    "COS", "$0.00",
    "Distribution Center Cost", "$0.00",
    "FIFO", "$0.00")

    '

     

     

    Best Regards,

    Neeko Tang

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please feel free to let me know.

     

    Best Regards,

    Neeko Tang

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