none
Currency Symbol in Calculated Measure

    Question

  • Hi there,

    In PowerPivot, I have a fact table with REVENUE in different currencies. The Dim Currency table contains the currency symbol. Per default, the data type "currency" is detected and the EURO currency symbol is displayed in each row.

    As a work around, I tried to create a new calculated measure with DAX:

    =FORMAT([Revenue];RELATED(Dim_Currency[Format]))

    The FORMAT column in Dim_Currency contains the format string, such as "$0.0" for USD or "0.0€" for EUR. It works, but the result is a text string and not a number.

    Any idea how to handle multiple currency symbols in PowerPivot?

    thanx for you help

    Peter

    Thursday, July 18, 2013 5:08 AM

Answers

  • Hi Gerhard,

    This is what I was afraid of.

    Working with different currencies works nevertheless fine with SSAS.

    http://www.ssas-info.com/analysis-services-faq/27-mdx/244-how-change-currency-symbol-based-on-selected-currency-dimension-member 

    Great article!

    Thanx for posting.

    Viele Grüsse nach Germany

    Peter

    www.bicn.de
    • Edited by PeterDE61 Friday, July 19, 2013 1:17 PM First feedback
    • Marked as answer by PeterDE61 Thursday, August 22, 2013 7:50 AM
    Friday, July 19, 2013 1:15 PM

All replies

  • Im afraid thats not possible

    you cannot set the FormatString in PowerPivot dynamically
    the only "Workaround" is using FORMAT which you already mentioned with the given drawback that ist not a number anymore but a text

    an Option may be to add a seperate measure that Displays only the currency Symbol and you pull it besides your number-measure?

    hth,
    gerhard


    - www.pmOne.com -

    Friday, July 19, 2013 6:55 AM
    Answerer
  • Hi Gerhard,

    This is what I was afraid of.

    Working with different currencies works nevertheless fine with SSAS.

    http://www.ssas-info.com/analysis-services-faq/27-mdx/244-how-change-currency-symbol-based-on-selected-currency-dimension-member 

    Great article!

    Thanx for posting.

    Viele Grüsse nach Germany

    Peter

    www.bicn.de
    • Edited by PeterDE61 Friday, July 19, 2013 1:17 PM First feedback
    • Marked as answer by PeterDE61 Thursday, August 22, 2013 7:50 AM
    Friday, July 19, 2013 1:15 PM
  • I hate to add on to this old trail, but see little else on the subject. A text formatted measure is left justified and not right justified.  I'm using Tabular Analytics for presenting in SP 2013.

    Is there a way to change the currency symbol property associated with the Measure format?  I've defaulted to no $ symbol at all, but need JPY and EUR currency symbols.

    Maybe we'll see a real attention to global issues in SQL 2015/16?

    Thanks,

    Todd


    • Edited by Todd Fields Monday, September 15, 2014 10:03 PM
    Monday, September 15, 2014 10:02 PM
  • once you set your measure to format as Currency, you can also set the property "Currency Symbol" for that specific measure. However, this cannot be set dynamically as it was possible in multidimensional 

    this should solve your issue with SP 2013

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Thursday, September 18, 2014 8:09 AM
    Answerer
  • Hi Gerhard,

    Thanks for the reply.  My needs are truly to be dynamic.  I have a global user base and each user will have a preference to see their local currency.  What I've opted to do is create the user preference as a dimension that can be displayed as a filter or slicer to see what the currency is and then show the dollars without any symbol.

    If my end cube does not have many measures, then I'll show my USD, JPY, EUR as separate measures and they can drag in the value that they want.  Each currency measure would be formatted with the proper symbol as you've pointed out above.

    Thanks,

    Todd

    Thursday, September 18, 2014 3:44 PM