Currency Conversion: Formatting Symbols in Tabular

This article on currency conversion was written on my old blog in August 2006.  It still applies today for Multidimensional in 2012.  Update August 17 2013: see revised version for SSAS 2012.  One of the items covered is how to format the currencies so they look like the following rather than all use one currency’s symbol.

I created a couple of extra columns in my currency dimension table.  My examples use the LCID column for multidimensional and FormatString for tabular.

You can get the LCIDs from here.  An easy way to get the format strings is to select Format=Currency and set the currency symbol in the properties window of SSDT.  This internally sets the FormatString property to the custom string.

Multidimensional

Set the currency attribute’s ValueColumn property to the LCID column.

And then the MemberValue function in MDX script to set the Language property.

Language(([Currency].[Currency].Members, [Measures].[Internet Sales Amount])) = [Currency].[Currency].CurrentMember.MemberValue;

Tabular

I couldn’t find a way to use LCID in tabular, but we can use the FORMAT function and pass in the FormatString value from the currency table.

DEFINE
MEASURE 'Internet Sales'[Internet Sales] =
    SUMX
    (
        'Currency Rate'
        , CALCULATE( Sum('Internet Sales'[Sales Amount]) / Sum('Currency Rate'[End of Day Rate]) )
    )
MEASURE 'Internet Sales'[Internet Sales FORMATTED] =
    FORMAT
    (
        [Internet Sales]
        , VALUES('Currency'[Format String])
    )
MEASURE 'Internet Sales'[Internet Sales ALL MEMBER DOLLARS] =
    IF
    (
        HASONEVALUE('Currency'[Currency Name])
        , [Internet Sales FORMATTED]
        , [Internet Sales FORMATTED]('Currency'[Currency Name] = "US Dollar")
    )
EVALUATE
ADDCOLUMNS
(
    FILTER
    (
        VALUES('Date'[Calendar Year])
        , [Internet Sales] > 0
    )
    ,"US Dollar", [Internet Sales ALL MEMBER DOLLARS]( 'Currency'[Currency Name] = "US Dollar" )
    ,"United Kingdom Pound", [Internet Sales ALL MEMBER DOLLARS]( 'Currency'[Currency Name] = "United Kingdom Pound" )
    ,"Brazilian Real", [Internet Sales ALL MEMBER DOLLARS]( 'Currency'[Currency Name] = "Brazilian Real" )
    ,"Saudi Riyal", [Internet Sales ALL MEMBER DOLLARS]( 'Currency'[Currency Name] = "Saudi Riyal" )
    ,"ALL MEMBER", [Internet Sales ALL MEMBER DOLLARS]
)
ORDER BY 'Date'[Calendar Year]

In multidimensional, we can use the following properties to hide the all member and choose the default currency – summing multiple currencies obviously doesn’t make sense.

  • IsAggregatable=False removes the all member
  • DefaultMember set to the desired default currency

Given that tabular doesn’t have anything equivalent to these features at this time, we need to check if we are at the all level of the currency dimension using HASONEVALUE(‘Currency'[Currency Name]).  The above example sets the all member to US Dollars.  This also covers some clever clogs selecting a multi-value filter on currency.

Advertisements

3 Responses to Currency Conversion: Formatting Symbols in Tabular

  1. Mark Davis says:

    I love this efficacious example! I was able to implement this as columns in the model itself, and I was surprised, since the formatted sales measure displays the error due to table input, yet it still provides otuput to the HASONEVALUE function. Have you tried doing the same and encountered any problems?

    • Christian says:

      Mark – thanks for the feedback. I have tried the same and I get the behavior you describe. If I understand correctly, the reason it works (using HASONEVALUE to force a single currency in filter context) is if the return value from the VALUES function is a single column/row rather than a multi-row table, DAX will implicitly convert it to a scalar value when passing as a parameter to the FORMAT function.

  2. Pingback: Currency Conversion in SSAS 2012 (Multidimensional & Tabular) | Christian Wade's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: