Compute effective approach; sumx, summarize, sum - case of transactional sales data and exch. rates

Hi LasseL ,

I re-write the measure like this:

Sales Revenue = 
VAR tab =
    SUMMARIZE (
        'Orders',
        'Calendar'[Date],
        'Orders'[Currency],
        'Orders'[Price],
        "REp",
            CALCULATE (
                MIN ( Rates[Rate] ),
                FILTER (
                    Rates,
                    Rates[From Currency] IN DISTINCT ( Rates[From Currency] )
                        && 'Rates'[To Currency] IN DISTINCT ( ReportingCur[Currency] )
                )
            )
    )
RETURN
    SUMX ( tab, [REp] * [Price] )

 Now the sum value should be correct:

See the sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

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

Hi LasseL ,

I find that you have disabled the interactions between the slicer and the two tables under the sample file so the value looks like 'strange', just resume the interaction and the value shoule be normal.

About IN statement, it creates a logical OR condition between each row being compared to a table and the Distinct statement removes dupulicate values and only return unique values.

You can refer these articles if you are interested about them:

1. The IN operator in DAX 
2. Using “IN” Operator in DAX 
3. DISTINCT (column) 

Attached the modified sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

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

Thanks a lot for filter-correction, the explanation and definition!

How come the sum of sales value does not reflect the actual sum of each line, in this case DKK sum should be ~14+311 = ~325DKK

Hi again v-yingjl ,

On the positive side, yes, now it gives an (almost) perfect calculation (missing the original correct look up of the exchange rate  closest (before) to the transaction date - but I can fix that.

On the negative side, then I believe we are back to the poor performance of the original solution of a line by line calculation (SUMX) which was the approach that proved ineffective from the beginning when working on the big dataset, i.e. original measure was;

Sales Revenue = 
SUMX(
    'Sales Revenue and Cost',
    'Sales Revenue and Cost'[Line Amount] * 
    Calculate(
        min('Exchange Rates'[Rate]),
        filter('Exchange Rates', 
        'Exchange Rates'[From Currency]='Sales Revenue and Cost'[Company Currency] && 
        'Exchange Rates'[Date]<='Sales Revenue and Cost'[Date] &&
        'Exchange Rates'[To Currency]=SELECTEDVALUE('Reporting Currency'[Currency])
        )   
    )
)​

I just tested your measure against the big dataset, and the performance is dreadfully slow and I hit again memory errors from the MS datacenter.

However, I feel you are on to "something", is there a way we can get around the line by line calculation for each transaction and aggregate to a higher level, e.g. grouped by Calendar[Year-Month] and Orders[Currency]; thinking next evolution of your last measure to something like;

Sales Revenue = 
VAR tab =
    SUMMARIZE (
        'Orders',
        'Calendar'[Year-Month],
        'Orders'[Currency],
        'Orders'[Price],
        "REp",
            CALCULATE (
                MIN ( Rates[Rate] ),
                FILTER (
                    Rates,
                    Rates[From Currency] IN DISTINCT ( Rates[From Currency] )
                        && 'Rates'[To Currency] IN DISTINCT ( ReportingCur[Currency] )
                        && 'Rates'[Date] <= MAX(Orders[Date])
                        && MAX('Rates'[Date])
                )
            )
    )
RETURN
    SUMX ( tab, [REp] * [Price] )

Still, can't hit the correct exchange rate and sum of converted price is still not right - but at least it seems to run faster as the sumx is performed on a higher aggregated level?