cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## Divide by Constant Currency

Hello

I am trying to optimice the following Dax measure:

``````SUMX (
FILTER(Complete_DATA,Complete_Data[Version] in VALUES('Scenario 1'[Scenario 1])),
DIVIDE (
Totales[Gross Sales],
SUMX(
FILTER(
FILTER(FX,FX[Version]=Forex[Selected Currency]),
FX[Month]=Complete_DATA[Month]),
FX[EUR to US]
)
)
)``````

I have 2 slicers, one to select an scenario (From the Complete_DATA table), and another to select the currency.

The idea is that the [Gross Sales] be divided by the corresponding currency (according to the scenario selected) and month independent of the "Date" as I could need 2020 sales in 2019 Exchange Rates.

FX table has the following structure:

 Date Month Version EUR to US 01/01/2020 January LC\$ 1 01/01/2020 January USD 2020 1.02 01/01/2019 January USD 2019 1.05

The previous DAX formula works, but is incredibly inefficient as it first goes to each of the Complete_DATA registry, divides the corresponding currency and then summarizes everything. With small data it doesn't matter but right now I have query updates of +3 minutes.

I have tried the following with no success as Exchange is not calculating correctly:

``````Gross Sales 1 =
var Sales =
CALCULATE(
Totales[Gross Sales],
Complete_Data[Version] in VALUES('Scenario 1'[Scenario 1])
)

var Exchange =
CALCULATE(
[TRM],
FX[Version] in VALUES(Forex[Currency]),
FX[Month] = SELECTEDVALUE(Calendario[Month])
)

return DIVIDE(Sales,Exchange)``````

Any suggestions?

2 REPLIES 2
Helper I

I am doing the following:

``````Gross Sales 1 =
var Sales =
CALCULATE(
Totales[Gross Sales],
Complete_Data[Version] in VALUES('Scenario 1'[Scenario 1])
)

var Exchange =
CALCULATE(
[TRM],
FX[Version] in VALUES(Forex[Currency]),
USERELATIONSHIP(Complete_Data[Month],FX[Month])
)

return DIVIDE(Sales,Exchange)``````

But apparently it is not calculating correclty:

The right one is the correct calculation with the old & inneficient code. Any suggestions?

Super User

Take your DAX query and analyze its query plan in DAX Studio.  Check the number of produced queries, the FE/SE ratio, and the overall number of records touched.  Then decide on how to change it.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

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

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

Top Solution Authors
Top Kudoed Authors