Custom FORMAT() thousand separator in a DAX measure

[phuonganhtdinh]

My region is set to Vietnam so that the thousand separator becomes "." and the decimal separator would be "," according to local conventions. For example, one thousand two hundred point zero two should show "1.200,02". However with the measure below, the Sales number would use normal separators "1,200.02" again.

Sales measure for card = IF(ISFILTERED(Dim_Date),FORMAT(([Sales]/1000000000), "#,##0 \billion\V\N\D;") & "         ", "Year not selected")

This is a total sales measure written specifically for card visuals (to add some space and for it to say "Year not selected" when no year is chosen). How do I format this for it to follow Vietnamese conventions again? Thanks.

[TimoRiikonen]

Try "Standard" format. Although this format is not visible in the list by default, you can still overwrite it as the display format.

For integers use custom format #,0.

[v-lid-msft · Accepted solution]

Hi @phuonganhtdinh ,

We can also change the measure as following to meet your requirement:

Update formula: consider it may have negative value, we use "@" as a temp char instead of previous "-"

Sales measure for card =
IF (
    ISFILTERED ( Dim_Date ),
    VAR sales =
        FORMAT ( ( [Sales] / 1000000000 ), "#,#.## \billion\V\N\D;" )
    RETURN
        SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( sales, ".", "@" ), ",", "." ), "@", "," ) & "         ",
    "Year not selected"
)

By the way, PBIX file as attached.

Best regards,

[Syndicate_Admin]

Great answer @v-lid-msft

I've been searching for this solution for a while. It is a witty one. Thanks for sharing 😉

[nandukrishnavs]

@phuonganhtdinh 

Check out this video 

Documentation: https://docs.microsoft.com/en-us/power-bi/desktop-custom-format-strings

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂