Change value of measure based on slicer

[Powerbia_acc]

Hello Community,

I have created a model with multiple measures . Let’s say the base measure is [Amount], which is sum of multiple GL codes from Trial balance. I have multiple measures connected to this [Amount] measure example [Actual revenue], [Actual cost], [cos%], [EBITDA] and many more. I have the report good to go and now the requirement is that I would like to slice by different currencies, example By USD, by Eur, Swiss frank etc. I do not want to write more measures for each of the currency . What is the fastest way to solve it?
Anyway I can multiply [Amount] measure by currency based on slicer selection. Also the [Amount] measure does not have one currency. This master trial balance is for multiple countries and has values for different countries . Hence, values are in local currency. Example Revenue for Canada is in CAD, revenue for US is in USD. Requirement is that I would like to convert all the values to single currency, which is passed in the slicer.

Hopefully someone will be able to help .

Thanks

[vs_7 · Accepted solution]

Hi, @Powerbia_acc 

try below calculated measure

• Converted Amount = VAR SelectedCurrency = SELECTEDVALUE('Currency'[Currency]) VAR ConversionRate = LOOKUPVALUE('Currency'[ConversionRate], 'Currency'[Currency], SelectedCurrency) RETURN [Amount] * ConversionRate

  This formula uses the SELECTEDVALUE function to get the selected currency from the slicer, and then looks up the corresponding conversion rate from the Currency table. It multiplies the [Amount] measure by the conversion rate to calculate the converted amount.

• Use the new measure in visuals: Replace your existing measures, such as [Actual Revenue], [Actual Cost], etc., with the new calculated measure, [Converted Amount], in the visuals where you want to display the converted values.

[Powerbia_acc]

Thank you, it works!

[vs_7]

Hi @Powerbia_acc ,

try below steps

• Create a table in your data model that includes currency conversion rates. This table should have columns for currency codes (e.g., USD, EUR, CAD) and corresponding conversion rates to a base currency (e.g., USD).

• Create a relationship between this currency conversion table and your main data table that contains the [Amount] measure.

• Create a new calculated column in the main data table that retrieves the conversion rate based on the currency code. Use a formula similar to the following:

  java
• Conversion Rate = RELATED('Currency Conversion'[Conversion Rate])

  This formula uses the RELATED function to retrieve the conversion rate based on the currency code in the main data table.

• Modify your [Amount] measure to include currency conversion by multiplying it with the conversion rate. For example:

  css
• Amount Converted = [Amount] * [Conversion Rate]

  This new measure will multiply the [Amount] measure by the conversion rate for the respective currency.

• Add a slicer visual to your report that allows users to select the desired currency.

• Use the new [Amount Converted] measure in your report visuals, such as [Actual Revenue], [Actual Cost], [cos%], and [EBITDA]. These measures will dynamically adjust based on the currency selected in the slicer.

[Powerbia_acc]

Hi @vs_7 , thank you for your repsonse. I am adding additonal information. The above solution does not work. Please let me know if you need any other detail.