Forum Discussion

dokat's avatar
dokat
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Change number format based on its value

Hi,

 

I have formula that returns value in thousands or millions based on filter selection. Is it possible to format it based on the value with dax formula. For Ex: If the selection returns 500000, it formats $500K, if its $5,000,000, returns $5.0M. Below is the formula use.

 

Net Sales = CALCULATE(('P&L'[2021]), 'P&L'[P&L] in { "Customer NS"})
 
Appreciate anyone who has a solve for it.
Need Help
  • dokat's avatar
    dokat
    4 years ago

    I modified the formula and tried below and its working

     

    Testmeasure = VAR NetSales = SUM([Values])
    RETURN
    SWITCH(TRUE(),
    [Net Sales] <1000000, FORMAT('P&L'[Net Sales],"$#0,K"),
    [Net Sales] >=1000000, FORMAT('P&L'[Net Sales],"$#,##,,.0M"),
    FORMAT('P&L'[Net Sales],"##,##0")
    )

2 Replies

  • ALLUREAN's avatar
    ALLUREAN
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi, dokat 

    Not sure if I understand it correctly, but you can set to display Auto in visual options for this measure?

  • dokat's avatar
    dokat
    Icon for Post Prodigy rankPost Prodigy
    4 years ago

    I modified the formula and tried below and its working

     

    Testmeasure = VAR NetSales = SUM([Values])
    RETURN
    SWITCH(TRUE(),
    [Net Sales] <1000000, FORMAT('P&L'[Net Sales],"$#0,K"),
    [Net Sales] >=1000000, FORMAT('P&L'[Net Sales],"$#,##,,.0M"),
    FORMAT('P&L'[Net Sales],"##,##0")
    )