0 votes
1 view
in BI by (17.6k points)

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

enter image description here

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. 

1 Answer

0 votes
by (47.2k points)
  • If you have a table containing all formats available to apply this can be achieved.

  • I've created these tables as example:

MyTable

image

CurrencyFormat

image

  • 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.

image

  • 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] )

    )

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...