cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## Multi currency reporting question

I have sales fact table that has columns for sales in local currency, Sales in USD, Sales in EUR, Sales in GPB and the local currency code.

I have a exchange rate table that reports each currency exhange rate against the 3 reporting currencies. Instead of reporting by just 3 currencies we want to report by all the different local currencies. I created a measure called Sales currency, so we are going from just the USDs to multi-currencies.

My questions are,

1) Is there a way to leverage just the sales amount in local currency columns and total sales displayed by default currency of usds (when nothing is selected)?

1 ACCEPTED SOLUTION
Community Support

Hi @chutchut09 ,

I suggest you to create an unrelated Currency Code table for slicer.

``````Sales Currency =
IF (
MAX ( 'Dim Currency'[Currency Code] )
IN VALUES ( 'Slicer Currency'[Currency Code] ),
IF (
ISFILTERED ( 'Slicer Currency'[Currency Code] ),
IF (
ISCROSSFILTERED ( 'Dim Currency' ),
VAR SelectedCurrency =
SELECTEDVALUE ( 'Dim Currency'[Currency Code], "USD" )
VAR DatesExchange =
SUMMARIZE (
FILTER (
'Fact Exchange Rate',
'Fact Exchange Rate'[Reporting Currency] = "USD"
),
'Fact Exchange Rate'[MMM-YYYY],
'Fact Exchange Rate'[Exchange Rate]
)
VAR Result =
IF (
NOT ISBLANK ( SelectedCurrency ),
IF (
SelectedCurrency = "USD",
[Total Sales_USD],
SUMX ( DatesExchange, [Total Sales_USD] * 'Fact Exchange Rate'[Exchange Rate] )
)
)
RETURN
Result
),
[Total Sales_USD]
)
)``````

Result is as below.

Best Regards,
Rico Zhou

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

2 REPLIES 2
Regular Visitor

I decided to keep what I have. I know the ask is a bit bizzare. It works! thank you for posting this alternative solution.

Community Support

Hi @chutchut09 ,

I suggest you to create an unrelated Currency Code table for slicer.

``````Sales Currency =
IF (
MAX ( 'Dim Currency'[Currency Code] )
IN VALUES ( 'Slicer Currency'[Currency Code] ),
IF (
ISFILTERED ( 'Slicer Currency'[Currency Code] ),
IF (
ISCROSSFILTERED ( 'Dim Currency' ),
VAR SelectedCurrency =
SELECTEDVALUE ( 'Dim Currency'[Currency Code], "USD" )
VAR DatesExchange =
SUMMARIZE (
FILTER (
'Fact Exchange Rate',
'Fact Exchange Rate'[Reporting Currency] = "USD"
),
'Fact Exchange Rate'[MMM-YYYY],
'Fact Exchange Rate'[Exchange Rate]
)
VAR Result =
IF (
NOT ISBLANK ( SelectedCurrency ),
IF (
SelectedCurrency = "USD",
[Total Sales_USD],
SUMX ( DatesExchange, [Total Sales_USD] * 'Fact Exchange Rate'[Exchange Rate] )
)
)
RETURN
Result
),
[Total Sales_USD]
)
)``````

Result is as below.

Best Regards,
Rico Zhou

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors