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

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

Reply
juangomez
Helper I
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:

DateMonthVersionEUR to US
01/01/2020JanuaryLC$1
01/01/2020JanuaryUSD 20201.02
01/01/2019JanuaryUSD 20191.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
juangomez
Helper I
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:

 

GS differences Power BI.JPG

 

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

 

lbendlin
Super User
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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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