Sum of DAX

[emiliePowerBI]

Good Morning,

I have a DAX to convert my costs from local currency to EUR. This conversion should depend on the period, year and scenaripo selected using filters.

This DAX (test) is the results of 2 DAX: 

test = [ACTYTD]/[FX Rate] 

where: 

ACTYTD = TOTALYTD(SUM('ABC Master'[LC]),Dates[Date],'ABC Master'[Scenario]="Actual")

and 

FX Rate =

CALCULATE (

    SUMX (

        FILTER (

            'FX Table',

            'FX Table'[Date] <= MAX ( Dates[Date] ) && 'FX Table'[Date] >= STARTOFMONTH ( Dates[Date] )

        ),

        'FX Table'[FX Rate]

    ),

    'FX Table'[Scenario] = SELECTEDVALUE ( 'FX Table'[Scenario] )

)

The DAX "test " by row works really well.

Here are my tables connection:

I would like the conversion to move according to the year, the scenario and the Month. We should be able to filter on Any brands in any country

My problem is that the total of my "Test" is not correct. It seems tricky to calculate a sum of measure being the results of 2 DAX already.

I tried to create a bridge table but this is not working

ACTYTDEUR =

CALCULATE(

    SUMX('ABC Master',

        [ACTYTD] / [FX Rate])

    ,

    'Bridge'[Scenario] = SELECTEDVALUE('FX Table'[Scenario]),

    'Bridge'[Year] = SELECTEDVALUE('FX Table'[Year]),

    'Bridge'[Country code] = SELECTEDVALUE('FX Table'[Country code]),

    'Bridge'[Brand] = SELECTEDVALUE('ABC Master'[Brands])

)

I don't know if it is because of the links between my tables but they are all "Many to One" and not ambiguous.

Would you please help me identify what I am doing wrong?

Thanks a lot in advance,

Best,

Emilie

[emiliePowerBI]

Thanks a lot for your quick answer!

[shafiz_p]

Hi @emiliePowerBI  

Your DAX measure test works well at the row level but not at the total level. This is a common issue when dealing with measures that involve division or other operations that don’t aggregate linearly.

One approach to solve this issue is to use SUMX to iterate over each row and then sum the results. This ensures that the calculation is done at the row level before summing up.

Try revising your test measure(For bridge table meaning evaluating against country code from bridge):
Test = SUMX('Bridge', [ACTYTD] / [FX Rate])

Ensure that the relationships between your tables are correctly set up.

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[rajendraongole1 · Accepted solution]

Hi @emiliePowerBI -Here SUMX calculates row-by-row results in ACTYTDEUR, but the total doesn't correctly aggregate these individual conversions.

FX Rate calculation with necessary filters into account

FX Rate =
CALCULATE (
AVERAGE('FX Table'[FX Rate]),
FILTER(
'FX Table',
'FX Table'[Date] <= MAX(Dates[Date]) &&
'FX Table'[Date] >= STARTOFMONTH(Dates[Date]) &&
'FX Table'[Scenario] = SELECTEDVALUE('ABC Master'[Scenario]) &&
'FX Table'[Country code] = SELECTEDVALUE('ABC Master'[Country code]) &&
'FX Table'[Brand] = SELECTEDVALUE('ABC Master'[Brands])
)
)

Use a measure for the row-level conversion, making sure it respects the filter context

Converted Cost Row-Level =
DIVIDE([ACTYTD], [FX Rate])

we want to sum up each row’s conversion without directly summing the results of [Converted Cost Row-Level] to avoid incorrect totals

ACTYTDEUR =
IF(
HASONEVALUE('ABC Master'[Country code]) &&
HASONEVALUE('ABC Master'[Brands]) &&
HASONEVALUE('Dates'[Month]) &&
HASONEVALUE('Dates'[Year]),
-- Row-level calculation
[Converted Cost Row-Level],
-- Total calculation
CALCULATE(
SUMX(
'ABC Master',
[ACTYTD] / [FX Rate]
)
)
)

check and apply the above logic and let me know if any