Change the calculation to all page based on User Input

[deepakb18]

Hi Experts

I have one requirement. I have one table that contains Accont Number, Amount, Amount Currency as below

Now I need to take user input of exchange rate for USD, EUR from user in One Page. Now what we require is on Page 2 we want to show chart Account wise total Amount based on exchange rate selected by user. Page 3 also have some calculation/char based on Page 1 exchange rate, that also need to change.

Please help how to achive as I tried several optios. 

[Ashish_Mathur]

Hi,

Why can't you create a slicer form the Amount Currency column and a visual with Account Number and Amount.  When you select any item in the sliccer, your visual will change.

[deepakb18]

Ashish

User needs to provide exchange rate for all or some currency. Example for US he can give 0.90 and for Euro 0.80 then based on amount currency type you should calculate the final amount against each account.

[MFelix]

Hi @deepakb18,

To achieve this you need to use the what if analsys ti make the exchange rate slicer.

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

Combine this with the slicer syncronization

https://docs.microsoft.com/en-us/power-bi/desktop-slicers

Not in the computer right now but follow the links and tell me if you have any isues.

Regards,
MFelix

[deepakb18]

Hi MFelix

Thanks for your reply. Now with the help of your links, I created 4 Parameter for exchange Rate like "Rate_US", "Rate_Ind", "Rate_EUR". Also I have sync slicer to both page and hide slicer from Page 2. Though calculation is not coming accurately but its changing. Now will you help me to write case statement for new measure to calcuate amount * ExchangeRate based on their Account_currency Coulmn .

Measure Amount After ExchangeRate = Sum('Total Population'[Account Balance Value Reported])*Rate_USD[Rate_USD Value].

I am new in DAX, Appreciate your help. I Need Measure to calculate like this

If(Account_Currency =USD, Sum('Total Population'[Account Balance Value Reported])*Rate_USD[Rate_USD Value])

else if (Account_Currency =EUR, Sum('Total Population'[Account Balance Value Reported])*Rate_USD[Rate_EUR Value])

etc etc.

[MFelix · Accepted solution]

Hi @deepakb18,

On Dax you can use a SWITCH function that is a supersize IF that allow to make several IF, check the link with the explanation.

Regarding your formula using the above syntax you should use the following:

TOTAL Value =
SWITCH (
    TRUE ();
    MAX ( Table[Account_Currency] ) = "USD"; SUM ( 'Total Population'[Account Balance Value Reported] )
        * MAX ( Rate_USD[Rate_USD Value] );
    MAX ( Table[Account_Currency] ) = "EUR"; SUM ( 'Total Population'[Account Balance Value Reported] )
        * MAX ( Rate_USD[Rate_EUR Value] );
    SUM ( 'Total Population'[Account Balance Value Reported] )
)

You should add the as many parameter as table you need the last one is the If nothing of the above happens returns in this case I return the actuals value.

This is the values you should increment in each of your exchange rate (change AAA by your exchange rate):

MAX ( Table[Account_Currency] ) = "AAA"; SUM ( 'Total Population'[Account Balance Value Reported] )
        * MAX ( Rate_AAA[Rate_AAA Value] )

Regards,

MFelix

[deepakb18]

Hi MFelix

One observation in your formaula

TOTAL Value =
SWITCH (
    TRUE ();
    MAX ( Table[Account_Currency] ) = "USD"; SUM ( 'Total Population'[Account Balance Value Reported] )
        * MAX ( Rate_USD[Rate_USD Value] );
    MAX ( Table[Account_Currency] ) = "EUR"; SUM ( 'Total Population'[Account Balance Value Reported] )
        * MAX ( Rate_USD[Rate_EUR Value] );
    SUM ( 'Total Population'[Account Balance Value Reported] )
)

 MAX ( Table[Account_Currency]  will always come as USD so all the currency is getting calculated based on USD Rate. how to resolved this.

Also based on below input file, can we create & calculate the amount based on exchange rate ?

Currency Rate	Calculated Amount
Based on USD Rate Parameter	="Account Balance Value Reported" * USD Rate Exchange

Input file

Division	Account Number	Account Balance Value Reported	Account Currency
Business Unit 3	101210480	301602	GBP
Business Unit 3	101210485	347462	GBP
Business Unit 3	101210694	2800000	GBP
Business Unit 1	101210527	382666	GBP
Business Unit 1	101210534	231914	GBP
Business Unit 1	101210445	263349	GBP
Business Unit 1	101210528	303572	GBP
Business Unit 3	101210668	805000	GBP
Business Unit 4	101456780	132915	USD
Business Unit 2	101210472	1300000	GBP
Business Unit 4	101210546	52072	GBP
Business Unit 4	101210545	216685	GBP
Business Unit 1	101210511	96923	GBP
Business Unit 1	101210512	24154	GBP
Business Unit 4	101210816	355447	USD
Business Unit 3	101210701	266388	USD
Business Unit 3	101210711	1200000	GBP

 Looking forward of your reply. Please !!!!

[MFelix]

Hi @deepakb18,

The SWITCH fornula was based on your explanation that you create a 4 parameter table changig the value of the exchange rate and that you wanted several nested IF's so you should add a statment for each of the exchange rate you have.

How are you presenting the values? In a card? in a chart?

You need to had context on the visual you need for example place a slicer or a visual filter where you select the exchange rate to USD, EUR, whatever based on the column Account currency that will give you the expected values.

Regarding the second part of your question do you want to calculate to all rows the value multiplied by the exchange rate? So when you have USD gives amount * 1 when it's GBP gives the amount * exchange rate?

Regards,

MFelix

[deepakb18]

Let me give you full details

A. 

Currency	Rate
USD	0.7
EUR	0.8
INR	0.6

 All are created as Parameter by using What If Paramaeter

B. Input Data

BU	Account No	Amount	Currency
BU 1	101	100	USD
BU 1	102	200	EUR
BU 1	103	300	USD
BU 1	104	400	USD
BU 2	104	500	EUR
BU 2	106	600	USD
BU 2	102	300	USD
BU1	106	300	EUR

Step 1 : To calculate  Amout After Exchange Rate Conversion

BU	Account No	Amount	Currency	Final Amount
BU 1	101	100	USD	70
BU 1	102	200	EUR	160
BU 1	103	300	USD	210
BU 1	104	400	USD	280
BU 2	104	500	EUR	400
BU 2	106	600	USD	420
BU 2	102	300	USD	210
BU1	106	300	EUR	240

Step 2 : Find the Maximum Final Amount from Each Account No

Account No	MAX Final Amount
101	70
102	210
103	210
104	400
106	420

Step 3 : Take Distinct BU & Account No from Step 1

BU	Account No
BU 1	101
BU 1	102
BU 1	103
BU 1	104
BU 2	104
BU 2	106
BU 2	102
BU1	106

Step 4: Lookup the Amount from each account no from step 3

BU	Account No	Final MAX Amount
BU 1	101	70
BU 1	102	210
BU 1	103	210
BU 1	104	400
BU 2	104	400
BU 2	106	420
BU 2	102	210
BU1	106	420

USE Treemap Chart to plot between BU & Final Max Amount

[deepakb18]

Hi Felix

Thanks for you quick reply and solution. However I changed calculation to fit the exact requirement.