Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have to calculate sales amount for multiple countries. The exchange rate table looks like this:
In the report I have this filter:
Where the end period is coming from the 'Endperiod' table, as highlighted in yellow.
The request is to calculate local sales amounts to EUR (that column is in the sales_purchase table). The exchange rate should always be the exchange rate of the selected "End Period". So if it is February, all sales, even 2021 Jan should be calculated on FEB 2021 ex rate.
These are my tables:
I cannot have now a 1 to many relationship between exchange rate and sales_purchase table, so cannot use a simple SUMX and RELATED to calculate everything to EUR.
Can you please help me how I could do this?
Solved! Go to Solution.
Try
Sales Amount EUR =
SUMX (
sales_purchase,
VAR exchRate =
LOOKUPVALUE (
exchange_rate[avg_rate],
exchange_rate[country_code], sales_purchase[country_code],
exchange_rate[Year], SELECTEDVALUE ( EndPeriod[Year] ),
exchange_rate[Month], SELECTEDVALUE ( sales_purchase[Month] )
)
RETURN
sales_purchase[sales_amount] * exchRate
)
@johnt75 , I have another page in this report where time selection follows a different logic
here the year slider is coming from the endperiod table year column. When it is filtered like this, the expected output should show Feb-Apr 2022 data. But now as end period table is filtered on 2 years, selectedvalue does not work....
is it possible to create a version of the meausre above that gives this expected output?
thanks so much, I really appreciate your help
Sales Amount EUR =
SUMX (
sales_purchase,
VAR exchRate =
LOOKUPVALUE (
exchange_rate[avg_rate],
exchange_rate[country_code], sales_purchase[country_code],
exchange_rate[Year], MAX( EndPeriod[Year] ),
exchange_rate[Month], SELECTEDVALUE ( EndPeriod[Month] )
)
RETURN
sales_purchase[sales_amount] * exchRate
if I just replace selectedvalue to MAX, it will do the job, what do you think?
Yes, that should work
Try
Sales Amount EUR =
SUMX (
sales_purchase,
VAR exchRate =
LOOKUPVALUE (
exchange_rate[avg_rate],
exchange_rate[country_code], sales_purchase[country_code],
exchange_rate[Year], SELECTEDVALUE ( EndPeriod[Year] ),
exchange_rate[Month], SELECTEDVALUE ( sales_purchase[Month] )
)
RETURN
sales_purchase[sales_amount] * exchRate
)
OMG it is working, just one tiny correction:
Sales Amount EUR =
SUMX (
sales_purchase,
VAR exchRate =
LOOKUPVALUE (
exchange_rate[avg_rate],
exchange_rate[country_code], sales_purchase[country_code],
exchange_rate[Year], SELECTEDVALUE ( EndPeriod[Year] ),
exchange_rate[Month], SELECTEDVALUE ( EndPeriod[Month] )
)
RETURN
sales_purchase[sales_amount] * exchRate
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |