Change values in chart/tables depending on the currency selected in a slicer

[Anonymous]

Hello all,

I have a table (Consolidated) with following column names:

The values that show up in monthly columns are all in local currencies based on the info in 'Region' column. For example, IN=INR;  AU=AUS; US=USD.

I have created different reports (visualization views) based on above data set by pivoting/unpivoting the above table and saving them as different tables/queries in PowerBI.

Question: Could someone please help me incorporate in each of the reports view a slicer based currency having INR, USD, AUS, GBP so that whichever currency is selected in the slicer, all "values" in report/table/charts is updated to reflect in the selected currency? 

I tried looking up an answer to a similar issue in the community but was unable to do so. 

Thank you for your help in advance.

-Deepak.

[Anonymous]

Here is the table.

PnL Line ItemsComparison BucketYearJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberTOTALRegionCountryCurrencyBiz Units

Revenue	2017 Actuals	2017	19498.05	22400.67	25735.51	26394.15	20269.09	35824.01	19893.85	22559.88	14377.8633	33220.46	22766.6225	30500.0646	293440.2	Asia	IN	INR	ABC
Direct Costs	2017 Actuals	2017	0	0	0	0	0	233.9	294.7	297.1	484.5	678.3	455.2	1000	3443.7	Asia	IN	INR	XYZ
Gross Margin	2017 Actuals	2017	0	0	0	0	0	0	0	0	0	0	0	0	0	Asia	IN	INR	DEF
Staff Costs	2017 Actuals	2017	190.4	217.5	267.1	263.5	0	0	0	0	0	0	0	0	938.5	Asia	AU	AUS	ABC
IT Costs	2018 Actuals	2018	77.2	148.5	93.7	44.4	335.8	389.4	177.3	318.7	639.3	313.6	124.8	560.1	3222.8	Asia	AU	AUS	ABC
Rental Costs	2018 Actuals	2018	1546.8	1508	1489.1	1491.4	1486.1	1590.2	1557	1566.8	1603.9	3118.5	2220.5	2574.8	21753.1	North America	US	USD	DEF
PBT	2018 Actuals	2018	201.25	528.79996	846.8999	329.35	823.1	600.4	190.9	503.4	356.9499511	461.6999	502.199944	533.799911	5878.749	North America	US	USD	XYZ
Revenue	2018 Original Budget	2018	1309.702	1912.988	966.0316	6358.51	-2343.16	1490.562	2515.804	937.8125	1949.513518	1550.378	2651.73707	7717.5511	27017.43	North America	US	USD	ABC
Direct Costs	2018 Original Budget	2018	518.492	518.75197	519.896	781.17	553.046	527.436	530.556	537.42	537.3159688	546.494	541.839968	568.177967	6680.596	Asia	IN	INR	DEF

[Ashish_Mathur]

Hi,

I may be able to help if you can share the link from where i can download your PBI file.

[Anonymous]

Hi Ashish, Thank you for your reply. Below is a link to download the file. https://1drv.ms/u/s!AuBrtdjpYsBqgQYcuO9HPmC-Fx8z This is a sample file I have created. Thank you for your help. Deepak.

[Ashish_Mathur]

Hi,

What is the Consol Table?  Has this Table been created after consolidating data from the other 3 Tables?  Is that the only Table from you will be building all your visuals?  I may be able to help (not sure how much i can though) but i want to try.  I need to carry out some transformations in the Query Editor.  Share the 4 base data sets in an MS Excel file format.

[Anonymous]

Hi Ashish,

The consol tab is the main table which was loaded in Power BI. This table was created by getting data from different excel files/different tabs. The underlying data I received was in this format.

The rest of the tables in Power BI Desktop or the attached excel sheet you see are the transformations I did in Power BI.

For my visuals am using the following three tables only:

By Comparison Buckets

By PnL Line Items

By Months

Below is the link to download the excel file.

https://1drv.ms/x/s!AuBrtdjpYsBqgQcbU4q_p-7H4fXj

I sincerely appreciate all your assistance.

Many thanks,

Deepak.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution file from here.

Hope this helps.

[Anonymous]

Hi Ashish,

Thank you so much for all your help. This answers my query but not completely.

Unfortunately, am unable to share my dataset to explain you better but I sincerely appreciate all your efforts.

Why I say not completely is because, if I have in my dataset INR, AU, US, data values I also want to be able to see data values in GBP or some other currency for that matter. 

And the next thing was that my visualizations are already created using not the "main table" I sent you, but the other tables in excel file I sent you. So how to create measures in those respective table to get the desired result in visualizations is a challenge for me. 

Many thanks once again for all your assistance.

-Deepak.

[Ashish_Mathur]

You are welcone.  The first problem of viewing amounts in othe currencies can be solved.  Your method of creating visualisations from the 3 break-out tables would be an inefficient one because those tables have infact been created from the main table (on which my solution is based).

[Anonymous]

Hi Ashish,

Re: the 3 break-out tables - I have created them because I wanted to use specific visual charts.

As per my understanding on BI, certain visual charts can be used when your data is in a particular format. Am not sure if this is true but if  you are aware of how the data can be in one format only yto support multiple visualizations, I would love to learn more about that. 

The visualization I had sent you in the sample file was just one visual. 

Thanks once again for all your help.

-Deepak.

[Ashish_Mathur]

Build your desired visuals from my single Table.  Let me know which visual you are unable to build because of the data structure of the single Table.

[Anonymous]

Hello Greg/Anish,

So sorry, this is my first time on the forum posting the query. It did not strike me to actually attach the file. My bad.

I have the following tables in my Power BI Desktop:

1. Table "Consolidated" - This is the only dataset in my excel file, (this excel file is loaded into BI Desktop) and the rest of the below tables are in Power BI Desktop.
2. Table By "Comparison Buckets" (pivoted view,  using query editor)
3. Table "By PnL Line items" (pivoted view, using query editor)
4. Table "By Months" (Un-pivoted view, using query editor)

Based on the above tables in Power BI Desktop I have created visualizations. Basically different report tabs are referring to one table at a time.

Could you please guide me on how to incorporate in each of the reports view, a slicer having currency such as INR, USD, AUS, so that whichever currency is selected in the slicer, "all values" in the report/table/charts is updated to reflect the selected currency? 

See the attached image for result. Right now in the picture I can see all local currencies listed in the slicer and based on the selection it will filter the chart to show only figures respective to the selected currency. I want to know if there is a way to get "all values" converted in one standard currency depending on the currency selected in the slicer. It should show me all values and not just thosoe values which are related to INR, AUS or USD only.

Am unable topost the table in this post will post the table in another post.

Exchange Rate:

1 USD=1 USD

1 USD=67 INR

1 USD=30 AUS

1 USD=0.76 Sterling Pound

Thank you so much for your help.

-Deepak.

[Ashish_Mathur]

Hi,

What exachange rate should be used for each currency - Exchange rates will fluctuate everyday.  Share data in a format that can be copied in an Excel workbook.  Also, show your expected result.

[Greg_Deckler]

So, sample data that can be copied and pasted would be very helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Also, if you could include how your data actually ends up after unpivoting, etc. that would save a lot of time.

[Anonymous]

Hello Greg,

So sorry, this is my first time on the forum posting the query. It did not strike me to actually attach the file. My bad. Also I was not aware how to send sample data. 

I have the following tables in my Power BI Desktop:

1. Table "Consolidated" - This is the only dataset in the excel file, (this excel file is loaded into BI Desktop) and the rest of the below tables are in Power BI Desktop.
2. Table By "Comparison Buckets" (pivoted view on 'Value', using query editor)
3. Table "By PnL Line items" (pivoted view on 'Value', using query editor)
4. Table "By Months" (Un-pivoted view on 'Months', using query editor)

Based on the above tables in Power BI Desktop I have created visualizations. Basically, different report tabs are referring to one table at a time.

Could you please guide me on how to incorporate in each of the reports view, a slicer having currency such as INR, USD, AUS, so that whichever currency is selected in the slicer by the user, "all values" in the report/table/charts is updated to reflect the selected currency? 

Thank you so much for your help.

-Deepak.

 PnL Line ItemsComparison BucketYearJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberTOTALRegionCountryCurrencyBiz Units

Revenue	2017 Actuals	2017	19498.05	22400.67	25735.51	26394.15	20269.09	35824.01	19893.85	22559.88	14377.8633	33220.46	22766.6225	30500.0646	293440.2	Asia	IN	INR	ABC
Direct Costs	2017 Actuals	2017	0	0	0	0	0	233.9	294.7	297.1	484.5	678.3	455.2	1000	3443.7	Asia	IN	INR	XYZ
Gross Margin	2017 Actuals	2017	0	0	0	0	0	0	0	0	0	0	0	0	0	Asia	IN	INR	DEF
Staff Costs	2017 Actuals	2017	190.4	217.5	267.1	263.5	0	0	0	0	0	0	0	0	938.5	Asia	AU	AUS	ABC
IT Costs	2018 Actuals	2018	77.2	148.5	93.7	44.4	335.8	389.4	177.3	318.7	639.3	313.6	124.8	560.1	3222.8	Asia	AU	AUS	ABC
Rental Costs	2018 Actuals	2018	1546.8	1508	1489.1	1491.4	1486.1	1590.2	1557	1566.8	1603.9	3118.5	2220.5	2574.8	21753.1	North America	US	USD	DEF
PBT	2018 Actuals	2018	201.25	528.79996	846.8999	329.35	823.1	600.4	190.9	503.4	356.9499511	461.6999	502.199944	533.799911	5878.749	North America	US	USD	XYZ
Revenue	2018 Original Budget	2018	1309.702	1912.988	966.0316	6358.51	-2343.16	1490.562	2515.804	937.8125	1949.513518	1550.378	2651.73707	7717.5511	27017.43	North America	US	USD	ABC
Direct Costs	2018 Original Budget	2018	518.492	518.75197	519.896	781.17	553.046	527.436	530.556	537.42	537.3159688	546.494	541.839968	568.177967	6680.596	Asia	IN	INR	DEF
Gross Margin	2018 Original Budget	2018	3378.424	5404.271	3899.522	4126.169	6777.072	-617.764	1023.938	1864.39	1608.464262	7704.767	3561.08075	5419.00903	44149.34	Asia	AU	AUS	XYZ
Revenue	Q0 2018  Revised Budget	2018	765.3027	532.19575	798.892	1014.392	778.3481	574.231	3321.344	1115.398	1030.589589	1074.827	552.206584	484.922817	12042.65	North America	US	USD	ABC
Direct Costs	Q0 2018  Revised Budget	2018	44.10471	60.818251	1135.028	241.1017	599.6313	544.5245	-80.4139	205.1926	-20.0492705	418.6723	722.443399	290.435244	4161.489	Asia	IN	INR	DEF
Gross Margin	Q0 2018  Revised Budget	2018	1309.702	1912.988	966.0316	6358.51	-2343.16	1490.562	2515.804	937.8125	1949.513518	1550.378	2651.73707	7717.5511	27017.43	Asia	AU	AUS	XYZ
Revenue	Q1 2018 Revised Budget	2018	518.492	518.75197	519.896	781.17	553.046	527.436	530.556	537.42	537.3159688	546.494	541.839968	568.177967	6680.596	North America	US	USD	ABC
Direct Costs	Q1 2018 Revised Budget	2018	3378.424	5404.271	3899.522	4126.169	6777.072	-617.764	1023.938	1864.39	1608.464262	7704.767	3561.08075	5419.00903	44149.34	Asia	IN	INR	DEF
Gross Margin	Q1 2018 Revised Budget	2018	765.3027	532.19575	798.892	1014.392	778.3481	574.231	3321.344	1115.398	1030.589589	1074.827	552.206584	484.922817	12042.65	Asia	AU	AUS	XYZ
Revenue	Q2 2018 Revised Budget	2018	44.10471	60.818251	1135.028	241.1017	599.6313	544.5245	-80.4139	205.1926	-20.0492705	418.6723	722.443399	290.435244	4161.489	Asia	AU	AUS	ABC
Direct Costs	Q2 2018 Revised Budget	2018	1309.702	1912.988	966.0316	6358.51	-2343.16	1490.562	2515.804	937.8125	1949.513518	1550.378	2651.73707	7717.5511	27017.43	Asia	IN	INR	DEF
Gross Margin	Q2 2018 Revised Budget	2018	518.492	518.75197	519.896	781.17	553.046	527.436	530.556	537.42	537.3159688	546.494	541.839968	568.177967	6680.596	North America	US	USD	XYZ