Forum Discussion

t-atgeor's avatar
t-atgeor
Microsoft Employee
9 years ago
Solved

Custom Numeric Formats inside tables

Hi,

 

would like to change slightly the formatting of numbers inside tables, similar to the formatting feature in card visuals. For this reason, am using the FORMAT function and converting numbers to text. However, when trying to use K for thousands, M for millions my syntax seems to be incorrect.

 

The correction I am currently using is: "[>=1000]€ #.##0."K";[<=-1000]-€ #.##0." K";0". Could you please advise on any correction I could make inside the tables, to visualize the numbers in a more simple and easy to read way? (e.g $231,121,214.23 as $231.1M)

 

Moreover, is there a way to visualize negative percentage values in brackets and not with the minus sign (e.g (5%) instead of -5%)?

 

Ideally, I would not want to change any numbers into text, probably the FORMAT function is not the only one.

 

Will be very grateful for any advice on it.

 

Thank you and Regards,

 

Atanas

Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    t-atgeor

     

    To get your desired result, we need to change your number into text with following DAX formula. With number type, we can make it show the result which you mentioned above.

     

    Per Col = 
FORMAT ( Table1[Percent], "#,##0.00%;($#,##0.00%)" )
    Num Col = 
IF (
    Table1[Number] >= 1000000,
    FORMAT ( Table1[Number], "#,##0,,m" ),
    IF (
        Table1[Number] >= 1000,
        FORMAT ( Table1[Number], "#,##0,k" ),
        FORMAT ( Table1[Number], "General Number" )
    )
)

     

    Best Regards,

    Herbert

2 Replies

Replies have been turned off for this discussion
  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    t-atgeor

     

    To get your desired result, we need to change your number into text with following DAX formula. With number type, we can make it show the result which you mentioned above.

     

    Per Col = 
FORMAT ( Table1[Percent], "#,##0.00%;($#,##0.00%)" )
    Num Col = 
IF (
    Table1[Number] >= 1000000,
    FORMAT ( Table1[Number], "#,##0,,m" ),
    IF (
        Table1[Number] >= 1000,
        FORMAT ( Table1[Number], "#,##0,k" ),
        FORMAT ( Table1[Number], "General Number" )
    )
)

     

    Best Regards,

    Herbert