Complex measures while comparing sales figures with switch functions

[mai_2020]

Hi everyone,

I have a situation as follows:

I want to create a possibility for my users to choose what numbers they can compare with what. We are talking company performance, so i have sales, sales last year, sales from 2 years ago, budgetted sales, and quarterly budget sales. I did that with a switch function and i don't really have problems with the choosing part.

And the calculations are fairly standard, i calculate the total difference, price effect, volume effect, mix effect AND exchange effect effect. And this last guy i can't figure out.

So i have 1. sales figures per month (for all of the options here), different currencies and their exchange rates per month (generally 3 biggest ones are EUR, USD, CNY, but we have about 10 different ones in general).

Normaly if i want fx effect of period 1 compared to period 2. I would take salesperiod1-salesperiod1@fxrateperiod2= nominal fx rate.

And what i thought of doing is =switchfunction period 1 net sales / fx rate period 1 (should be iterating the net sales to see which month and currency to divide with) * fx rate period 2(should be iterating the net sales to see which month and currency to multiply with).

But i don't know how to make them match each other, and for columns you have a lookupvalue but it doesn't work for measures (or i don't know how to).

So say i have sales table:

Customer A	USD	2020/01	$4000
Customer B	CNY	2020/01	$600
Customer C	EUR	2020/09	$7
Customer D	BRL	2021/03	$678

And i have exchange rates table (with all the fx rates, not just what i show here):

CNY	2020/01	|  4
CNY	2020/02	|  5
CNY	2020/03	|  6

So i want my calculation to know that to get back to CNY from the 1st table, i would need to multiply by 4, and not by 5 or 6 or an avergae thereof. And if i want to compare month 1 to month 2, i want the fx effect to be able to first multiply by 4 and then divide by 5. 

I hope this is written in a way it's understandable:)

[MFelix · Accepted solution]

Hi @mai_2020 ,

You need to use the setup following the description of SQLBI but in this case you need to make some changes to the model.

My setup is has follow:

Now I have added the following measure:

Sales Currency =
VAR DatesExchange =
    ADDCOLUMNS (
        SUMMARIZE (
            Sales,
            'Date'[Year-Month],
            'Currency'[Currency],
            "@TotalSales", SUM ( Sales[Value] )
        ),
        "@ExchangeAverageRate", CALCULATE ( SELECTEDVALUE ( Exchange[Exchange] ) )
    )
VAR Result =
    SUMX ( DatesExchange, [@TotalSales] * [@ExchangeAverageRate] )
RETURN
    Result

Result below and in attach PBIX file. Be aware that you may need to change the grouping on the summarize to fit your needs.

[mai_2020]

Unfortunately this won't do it as in the sales table i have different currencies. So for that i would normally use the lookupvalue to combine on both date and currency.

[MFelix]

Hi @mai_2020,

Don't understand your point. In the example I have there are several currencies and several dates has you can see it's returning correct amounts.

If not can you share a more detailed sample? 

[mai_2020]

Ah, i am sorry, i followed the link to sqlbi and saw that there is no currency on customer level and left the comment.

What is your calculation doing exactly, any chance you could explain in a bit more detail?