Forum Discussion
Complex measures while comparing sales figures with switch functions
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.
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.
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.
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?
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?