Power BI: How to dynamically change currency

Question

We have a requirement to allow the user to choose which currency he wants to see in the dashboard, like below example:

By default, it's GBP, if user changes to USD, we need to show the spend by USD. Under the hood we already have table InvoiceDetail which contains columns doing currency conversion beforehand:

SpendInGBP

SpendInUSD

SpendInEUR

I am not sure how I can map when the user chooses different currency using ChicletSlicer to different Columns. 

Answer 1

• If you have a table containing all formats available to apply this can be achieved.

• I've created these tables as example:

MyTable

CurrencyFormat

• In the MyTable table I've created two measures called Format and Total Sales.

Format = LASTNONBLANK ( CurrencyFormat[Format], 1 )

Total Sales = Total Sales = FORMAT(SUM(MyTable[Sales]),[Format])

• Note Total Sales measures uses the [Format] measure to get the selected format from the slicer.

• Adding the ChicletSlicer and setting FormatName column from CurrencyFormat table in Category pane, you should get the expected result.

• Also note the formats I've used can vary from what you need, so you have to add some changes to the format strings, do a quick read of the documentation about it.

    Format            Region

$#,##0;($#,##0)     SpendInUSD

£#,##0;(£#,##0)     SpendInGBP

€#,##0;(€#,##0)     SpendInEUR

• OP wants to get the sum of the right column based on the slicer.

• Fortunately, your table has a column for every currency, as you found with my proposal to map the slicer value to your measure, this is the final expression:

Spend =

IF (

    LASTNONBLANK ( 'Currency'[Code], 1 ) = "GBP",

    SUM ( Invoice[SpendGBP] ),

    IF (

        LASTNONBLANK ( 'Currency'[Code], 1 ) = "USD",

        SUM ( Invoice[SpendUSD] ),

        SUM ( Invoice[SpendEUR] )

    )

) 

If u want to learn the tool in depth, then come to us and sign up for this Power BI online training at Intellipaat.