Creating a currency filter that is attached to a fixed measure

[squarecat]

Hello, I am attaching a simplified version of the file I've been working on so you can have a better understanding of what I'm talking about.

PBI file.pbix 

This file contains two main tables, a budget table which contains information about the amount budgeted for each cost center throughout the year and an executed table which contains a registry of the amount spent on the cost centers. All of the information is handled in local currency but it's necessary to be able to see it in USD, preferably with a slicer that can switch between each and affects all the pages in the report.
The exchange rate is provided by the finance department, it doesn't change very often and in an ideal world should be able to be easily changed by the people who will use this report. Considering all of this I think the best course of action was to create a fixed measure like this

FX = 0.9

Which they can edit easily when necessary and doesn't require a profound understanding of Power BI. With this measure I created two new calculated columns on their respective tables: Total BUD amount in USD and Total EX amount in USD. The problem is that I don't know how I can link this to a slicer without using a new table or column, but rather the measure FX i created previously.

Something like if USD is selected then multiply Total BUD amount by FX and Total EX amount by FX, else show Total BUD amount and Total EX amount. It should work something like this:

And that the slicer not only affects this visualization but hopefully all the pages in the report.

Is there any way to do this? Maybe I have to ditch the slicer idea and use buttons? Any help or guidance is appreciated.

[squarecat]

Any help is appreciated 🙏

[v-linyulu-msft · Accepted solution]

Hi, @squarecat 
Thanks for reaching out to the Microsoft fabric community forum.

I might need to inform you that currently, I am unable to open the data you shared. If you need to share data with us, I recommend using GitHub and ensuring that sensitive information is removed.

Currently, there isn't a good solution to directly convert global currencies based on slicer options. However, we can switch from a parameter field solution to a measure-based solution, which will be more convenient for you to use later:

1.First, here is my test data:

2.Next, I created a calculated table to use as a slicer:

CurrencyTable = DATATABLE(
    "Currency", STRING,
    {
        {"Local"},
        {"USD"}
    }
)

3.Then, I created the following group of measures as the basis for the logic:

SelectedCurrency = 
IF(
    SELECTEDVALUE(CurrencyTable[Currency]) = "USD",
    [FX],
    1
)

4.Create two new measures to replace the original TotalBUDAmount and TotalEX:

TotalBUDUSD = SUM(BudgetTable[TotalBUDAmount]) * [SelectedCurrency]

TotalEXUSD = SUM(ExecutedTable[TotalEXAmount]) * [SelectedCurrency]

5.Here is the final result:

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[squarecat]

Hello, please check out if this link works now PBI file.pbix, if it doesn't I tried to upload it to Github, let me know if it works squarecat26/Squarecat

I will check your solution shortly thanks for your help!!

[v-linyulu-msft]

Hi, @squarecat 
Thank you for your prompt response. However, regarding the PBIX file you shared, the first link cannot be opened:

 The second link, even after downloading successfully, cannot be opened:

Please check if sensitivity labels or restricted permissions are set. You can also try describing in text which part of my previous response did not resolve your issue

Best Regards,

Leroy Lu

[squarecat]

I just tried your solution and it worked, it's a pity you couldn't open the archive but your advice really helped.

Thanks!