Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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!
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
Proud to be a Super User!
@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
Proud to be a Super User!
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!
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.
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!
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!
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.
@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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |