Forum Discussion
Complex measures while comparing sales figures with switch functions
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:)
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 ResultResult below and in attach PBIX file. Be aware that you may need to change the grouping on the summarize to fit your needs.
4 Replies
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 ResultResult below and in attach PBIX file. Be aware that you may need to change the grouping on the summarize to fit your needs.
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.
