Currency Toggle for Tables and Charts?

[Anonymous]

Hello,

How can I creat a toggle for a table (or matrix/chart/etc..) in order to switch between 2 currencies (i.e. USD/EUR)? The underlying dataset aleady includes both values, so there is no conversion necessary. I want to avoid either having to build 2 tables, which would double the entire already massive report or having to include both values in a single table, which would make every table way too busy.

Thanks!

[Anonymous]

Thanks @v-janeyg-msft @VijayP @Sumanth_23 for all your timely responses. There was much overlap between all your suggested solutions. Not entering 1 and 2 as values for EUR and USD respectively, so that the switch-function was able to pick up the chosen currency, proved to be the final missing piece. It now is working. 

Thanks all!

[Sumanth_23]

hi @Anonymous - Just to confirm - you have the required USD and EUR in different column in your data model. 

You can use DAX to toggle between the different different columns based on user selection from the slicer. 

You can refer to the below video for reference. 

https://www.youtube.com/watch?v=88QSf2fB1Rg

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue.

Regards,

Sumanth 

[VijayP]

@Anonymous 

Create a dummy Table with  like 

Currency

USD

EUR

Then create a mesure 

Currency Selection = SelectedValue (DummyTable[Currency])

then create your goal measure =

Switch(True(), [Currecny selection ]="USD", USD Values , EUR Values)

This gives a dynamic view with single visual

LEt me know if any further assistance required

[Anonymous]

Thank you @VijayP & @Sumanth_23 ,

I think this would work if I only had 2 columns, one for EUR and one for USD. It looks like, however, that I can't do this when there are multiple columns for each currency, with different characteristics. See example in below:

- The diff revenue characteristics are Prior Year Rev and CY Booked/Commit/Prob/Upside

- Each of those characteristics has their own row and column

- Each company has a single ID (KEY)

- if its a CY deal, it has an Oppty ID, otherwise not

- I can't just use the Totals for the toggle, b/c for forecasting purposes for example, I need the revenue broken down. 

Thanks for your help!

[v-janeyg-msft]

Hi, @Anonymous 

According to your description, I think it's not difficult, but I don’t know if there is a currency column in your fact table.

Can you share some sample fake data? If it doesn't exist, then you can create a table containing information about these two currencies as they said, use it as a slicer, then modify your measures. 

LIke this:

measure=if(table[currency]="USD",yes logic,no logic)

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-janeyg-msft @Sumanth_23 @VijayP ,

Let me clarify. I am looking to swith between 2 columns (i.e. EUR - Total Pipe 2021, USD - Total Pipe Pipe 2021) for a given table or chart. See example below:

Company ID	Company Name	Current Year Opportunity ID	EUR - Booked	EUR  - Prob	EUR - Total Pipe 2021	USD - Booked	USD - Prob	USD - Total Pipe 2021
6481613	XYZ 187		1,700,000		1,700,000	2,040,000	-	2,040,000
6481613	XYZ 187	6848		500,000	500,000	-	600,000	600,000
6481613	XYZ 187	6848		157,289	157,289	-	188,747	188,747
161168	ABC 215	798463125	156,874		156,874	188,249	-	188,249
161168	ABC 215	798463125		5,874,658	5,874,658	-	7,049,590	7,049,590
15978	KRG 778	46549		20,000	20,000	-	24,000	24,000
5464654	ZCZ 101				-	-	-	-
5464654	ZCZ 101	7779874	123,456		123,456	148,147	-	148,147
Total			1,980,330	6,551,947	8,532,277	2,376,396	7,862,336	10,238,732

Thanks!

[Anonymous]

Hi @v-janeyg-msft - thanks for this! I do not have a separate column that says if a measure is USD or EUR. I think I understand where you are going with this but let me break the steps down again to make sure I got it right:

1) Create a column that defines if a measure is USD/EUR:

measure = if(table[currency]="USD",yes logic, no logic)

2) Create a dummy table like this:

Currency

USD

EUR

3) Create a measure: 

Currency Selection = SelectedValue (DummyTable[Currency])

4) Create goal:

measure = Switch(True(), [Currency Selection ]="USD", USD Values , EUR Values)

Here is the link to the sample data:

https://1drv.ms/f/s!ANDqgxFHbN3ueg 

Thanks!

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

I think you probably understood what I meant, so let me correct it more specifically.

1) Create a table like this:

Currency

USD

EUR

You can easily use 'enter data' in desktop.

2)Create a measure to calculate the results that displayed in your matrix 'values'.

measure=if(table[currency]="USD",yes logic:USD value,no logic: EUR value)

Since the data link you shared is invalid, I can only say that the results of yes and no in the measure are the values you want to display separately, and you need to complete it yourself, like sum(table[column]). If you can modify it, you can switch the display data by selecting the slicer.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VijayP]

@Anonymous 

Watch this Video to Get USD and EUR Column within data and then you can evaluate the measure accordingly! LEt me know if any further assistance requried

https://drive.google.com/file/d/11tu1Dm6oVZHTMhUCRxvXNnqL2q6XkLIs/view?usp=sharing