Latest Enterprise DNA Initiatives

Display $ and no currency sign for Local Currency

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:
image

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

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. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

2 Likes

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… :frowning: 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

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

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

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. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

1 Like

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

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()
)

image
image
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

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. :slightly_smiling_face:

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

Thanks and Warm Regards,
Harsh

1 Like

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()
)image image

Your support has been very much appreciated.

Wishing you a great day ahead.

1 Like

Hello @SamSPAIN,

You’re Welcome. :slightly_smiling_face:

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

Have a great weekend ahead. :+1:

Thanks and Warm Regards,
Harsh