Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Shoukry
Frequent Visitor

How To Unify the Report Currency Based On the Slicer

Hi I have a problem to Unify the Report currency based on the selected currency by the user because each line in the transaction table will include different currency

 

I have applied a sample of my case in the below image, I have two tables,

1-The Exchange rate table, that includes cross-mapping between all currencies
2-The Revenue Transactions table, that includes the revenue amount for each company knowing that each company has different currency

 

DB.jpg

1 ACCEPTED SOLUTION

Hi @Shoukry

 

See the attached file.

Hope it helps

 

Assuming the User Slicer is from Foregin Currency in Table1, we can pull the relevant exchange rate in Table 2 for the same month and same year using this

 

Applicable Exchange Rate =
CALCULATE (
    VALUES ( Table1[Ex Rate] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Foreign Currency] ),
        Table1[Base Currency] = SELECTEDVALUE ( Table2[Base Currency] )
            && Table1[Month] = SELECTEDVALUE ( Table2[Month] )
            && Table1[YEAR] = SELECTEDVALUE ( Table2[YEAR] )
    )
)

Then Revenue in the selected Currency is simply

 

Revenue in Selected Currency =
[Applicable Exchange Rate] * SELECTEDVALUE ( Table2[Revenue] )

 

62.png


Regards
Zubair

Please try my custom visuals

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

 

So what is your expected result?  Which currency do you want all revenue figures to get converted to?  Are you expecting another column in Table2?  Please clarify.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi

thanks for your reply,

the end user will need to show the report in {CHF or USD or OMR} through the slicer, in this case, I want to convert all amounts that in the transaction table based on the user selection, 

which mean if the user selects to show the report in CHF, I will need to convert the base currency for each company to CHF,  knowing that each company in the transaction table has deferent currency 

 

 

Hi
thanks for your reply,

the end user will need to show the report in {CHF or USD or OMR} through the slicer, in this case, I want to convert all amounts that in the transaction table based on the user selection,

which mean if the user selects to show the report in CHF, I will need to convert the base currency for each company to CHF, knowing that each company in the transaction table has a specific currency , so the currency rate will be variable on each line based on the company currency

Hi @Shoukry

 

See the attached file.

Hope it helps

 

Assuming the User Slicer is from Foregin Currency in Table1, we can pull the relevant exchange rate in Table 2 for the same month and same year using this

 

Applicable Exchange Rate =
CALCULATE (
    VALUES ( Table1[Ex Rate] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Foreign Currency] ),
        Table1[Base Currency] = SELECTEDVALUE ( Table2[Base Currency] )
            && Table1[Month] = SELECTEDVALUE ( Table2[Month] )
            && Table1[YEAR] = SELECTEDVALUE ( Table2[YEAR] )
    )
)

Then Revenue in the selected Currency is simply

 

Revenue in Selected Currency =
[Applicable Exchange Rate] * SELECTEDVALUE ( Table2[Revenue] )

 

62.png


Regards
Zubair

Please try my custom visuals

Hi Zubair

 

Many thanks for your wonderful solution, the case has been solved

Thanks a lot.

 

 

Anonymous
Not applicable

Hi,

 

I tried this solution and was great, however it doesnt work when i want to aggregate values for a category, for example new customers and old customers, it only shows values when on indvidual customer / transaction level.

Do you have any tips for solving this?

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.