Display $ and no currency sign for Local Currency

[SamSPAIN]

Hi there!

I’ve gone thru several threads related to currency but I haven’t seen anything similar to what I’m seeking for - which is I hope way easier than other business issues.

We just have USD currency (different FX rates) and Local Currency. So just 2 options, as depicted below:

The ones highlighted in yellow are USD, for which I want all my measures to display $ notation sign.

Here one calculation:

Is there any formatting I could apply straight in this code without the need to afterwards format it as Currency, which would lead me to use the $ sign?

Big thanks

[Harsh]

Hello @SamSPAIN,

Thank You for posting your query onto the Forum.

You can use the “FORMAT()” function to format your currencies in $. Just wrap the FORMAT() function around the SUM() part. So in general it’ll be like this -

Measure = 
Initial Criteria , 
FORMAT( SUM( 'OPEX Actuals'[Amount Budget Rate CY] ) , "$ #,##0.00" )

Note: When we make use of the “FORMAT()” function the figures get’s converted into the Text. I’m providing few of the links below pertaining to the queries asked for FORMAT() function. To view the entire post please click onto the link and not onto the “expand/collapse” button.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Can't change Format for Measure

Hello @YusufGillani, Thank You for posting your query onto the Forum. Well, the formatting part is greyed out because you’ve used the “FORMAT()” function in your measure. Once you use this function it converts the figures into the text format by default and sets that format. Second thing is, within that as well you’ve incorporated two different formats i.e. one for “Number” in terms of “0.00” and one for the “Percentages” in terms of “0.00%”. Due to use of “Characters” as well you won’t be abl…

Convert number to Billions in DAX

I understand the format function can be used, for thousand seperator etc, but i want it to look like “2.13B” say, I am not able to get this particular formatting.

[SamSPAIN]

Hi @Harsh,

Thank you very much indeed for your reply.

Despite I found your remarks and posts useful, I’m afraid this is not going to help me… what happens if I then want to make some calculations using this formatted DAX that are set as Text? I won’t be able to do anything with them…

Isn’t there a way to pass $ sign for a specific criteria, otherwise pass no currency notation without changing values to text?

Once again, much appreciated for your collaboration here.

Cheers

[Greg]

Hi @SamSPAIN. I don’t think so. How about 2 measures, one formatted as $ (and with blanks for non-US transactions) and one formatted without a dollar sign (and with blanks for US transactions)?
Greg

[Harsh]

Hello @SamSPAIN,

I’m sorry to say but there’s no way around once the measures are converted to Text. As @Greg suggested, I agree with him that you need to create separate measure one with $ sign passed and one without i.e. measure which is not converted into the text. So basically, the conclusion is -

When we use the “FORMAT()” function we cannot use the measure branching technique since base measure result itself is converted into the Text. If we use then most of the time result will be “Error”. Than the best option is to use the Variable technique in each of the measure.

Thanks and Warm Regards,
Harsh

[Harsh]

Hello @SamSPAIN,

What you can actually do here in this case is create base measures for “Budget rate CY”, “Budget rate PY”, “Monthly Avg rate” and “Local Currency” and than reference the measures into your "(Base) Actuals" formula.

So create the measures like this -

1). Budget Rate CY measure -

Budget Rate CY = SUM( 'OPEX Actuals'[Amount Budget Rate CY] )

2). Budget Rate PY measure -

Budget Rate PY = SUM( 'OPEX Actuals'[Amount Budget Rate PY] )

3). Monthly Avg Rate measure -

Monthly Avg Rate = SUM( 'OPEX Actuals'[Amount Monthly Avg rate] )

4). Local Currency Measure -

Local Currency = SUM( 'OPEX Actuals'[Amount Local Currency] )

Now, since your base measures are created refer these measures into the “(Base) Actuals” formula and wrap them with the “FORMAT()” function. So based on the measures created you’ll be able to use them in your subsequent formulas since they’re evaluated as numbers. So now your final formula for this will be like this -

(Base) Actuals = 
SWITCH( TRUE() , 
    SELECTEDVALUE( 'Rate table ( For selection)' [Rate] ) = "Budget rate CY" , 
    FORMAT( [Budget Rate CY] , "$ #,##0.00" ) , 
    SELECTEDVALUE( 'Rate table ( For selection)' [Rate] ) = "Budget rate PY" , 
    FORMAT( [Budget Rate PY] , "$ #,##0.00" ) , 
    SELECTEDVALUE( 'Rate table ( For selection)' [Rate] ) = "Monthly Avg rate" , 
    FORMAT( [Monthly Avg Rate] , "$ #,##0.00" ) , 
    SELECTEDVALUE( 'Rate table ( For selection)' [Rate] ) = "Local Currency" , 
    FORMAT( [Local Currency] , "$ #,##0.00" ) , 
    BLANK()
)

So now, your primarary or base measure are intact and you can use them for creating subsequent measures i.e. "Measure Branching’ technique.

Hoping this helps you to solve your query.

Thanks and Warm Regards,
Harsh

[SamSPAIN]

@Harsh

FORMAT( [Budget Rate CY] , "$ #,##0.00" )

Thanks so much @Harsh! that seems to be workings pretty well

See below the code I wrote changing it slightly to show NO decimals:
OpEx Act (Act FX) = SUM( ‘OPEX Actuals’[Amount Monthly Avg rate] )
OpEx Act (Bgt CY) = SUM(‘OPEX Actuals’[Amount Budget Rate CY])
Opex Act (Bgt PY) = SUM( ‘OPEX Actuals’[Amount Budget Rate PY] )
OpEx Act (LC) = SUM( ‘OPEX Actuals’[Amount Local Currency] )

Actuals II =
SWITCH(
TRUE(),
SELECTEDVALUE(‘Rate table ( For selection)’[Rate])=“Budget rate CY”,FORMAT( [OpEx Act (Bgt CY)] , “$ 0,##0” ),
SELECTEDVALUE(‘Rate table ( For selection)’[Rate])=“Budget rate PY”,FORMAT( [Opex Act (Bgt PY)],“$ 0,##0” ),
SELECTEDVALUE(‘Rate table ( For selection)’[Rate])=“Monthly Avg rate”,FORMAT( [OpEx Act (Act FX)],“$ 0,##0” ),
SELECTEDVALUE(‘Rate table ( For selection)’[Rate])=“Local Currency”,FORMAT( [OpEx Act (LC)], " 0,##0" ),
BLANK()
)

I just got one more thing: I wish to display my figures in K USD and/or K LC - which would be the FORMAT to accomplish this?

Really appreciate your support here.

Thanks a lot

[Harsh]

Hello @SamSPAIN,

To convert the numbers to the thousands I’ve provided the link in the second post - “Convert number to Billions in DAX”. It shows how to convert the numbers into the “Thousands”, “Millions” and even “Billions”. Again, the link is provided below for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Note: To view the entire post, please click onto the link and not onto the “expand/collapse” button.

Thanks and Warm Regards,
Harsh

Convert number to Billions in DAX

Hello @Anu, Well actually to convert the numbers into the billions I had put 3 commas and a full stop as a decimal separator. For converting them into millions use only 2 commas and for thousands use only a single comma. I’m providing a screenshot below of the results that I’ve mentioned. [Billions Format - 2] I’m also providing a link that I used as an reference that provides an explanation about formatting the numbers (although they’re used in the Excel) but same concept was applied here …

[SamSPAIN]

Big thanks for sharing this @Harsh!

It greatly helped me to debug until I reached the exact FORMAT I was seeking for as I wanted to display figures in '000s but without the K notation at the end.

This is the final code I came up with:
Act =
SWITCH(
TRUE(),
SELECTEDVALUE(‘Rate table ( For selection)’[Rate])=“Budget rate CY”,FORMAT( [OpEx Act (Bgt CY)] , " 0,#,"** ), SELECTEDVALUE('Rate table ( For selection)'[Rate])="Budget rate PY",FORMAT( [Opex Act (Bgt PY)],**" 0,#," ),
SELECTEDVALUE(‘Rate table ( For selection)’[Rate])=“Monthly Avg rate”,FORMAT( [OpEx Act (Act FX)],"$ 0,#," ),
SELECTEDVALUE(‘Rate table ( For selection)’[Rate])=“Local Currency”,FORMAT( [OpEx Act (LC)], " 0,#," ),
BLANK()
)

Your support has been very much appreciated.

Wishing you a great day ahead.

[Harsh]

Hello @SamSPAIN,

You’re Welcome.

I’m glad that I was able to assist you and you found it helpful.

Have a great weekend ahead.

Thanks and Warm Regards,
Harsh