Forum Discussion
Currency Conversion for Averages
Hi,
I have an issue with Currency Conversion, when converting measures which totals should not be summed.
The situation is that if I have a measure Average Price = DIVIDE(SUM(Net Sales), SUM(Item Quantity)), which shows the average price of a product in EUR (base currency). However, when I convert the value into CHF for example, then on the row level (date) the values convert correctly, but on the weekly, monthly or yearly level the Average Price is summed instead of averaged. Has anybody else run into a similar issue before?
I have tried various DAX conversions and using Calculation Groups, but somehow cannot get the Currency Conversion to work with 'divisions' or 'average' values. I think it has to do smth with the Currency Conversion DAX code, which I have included in the bottom, however I am not clever enough to fix it...
Currently I am using the Calculation Group conversion method opposed by SQLBI: https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/
VAR MeasureName =
SELECTEDMEASURENAME ()
VAR SkipConversion =
NOT ISCROSSFILTERED ( 'D_Exchange' )
|| ( SEARCH ( "#", MeasureName, 1, 0 ) > 0 )
|| ( SEARCH ( "%", MeasureName, 1, 0 ) > 0 )
RETURN
IF (
SkipConversion,
SELECTEDMEASURE (),
VAR SelectedCurrency =
SELECTEDVALUE ( 'D_Exchange'[CurrencyFilter] )
VAR DatesExchange =
SUMMARIZE (
F_Currency,
'D_Calendar'[Date].[Date],
'F_Currency'[ConversionRates]
)
VAR Result =
IF (
NOT ISBLANK ( SelectedCurrency ),
IF (
SelectedCurrency = "EUR",
SELECTEDMEASURE (),
SUMX ( DatesExchange, SELECTEDMEASURE () * 'F_Currency'[ConversionRates] )
)
)
RETURN
Result
)
Below is a video example:
1 Reply
- v-xiaosun-msftCommunity Support
Hi Anonymous ,
Here is an article about Currency conversion in Power BI reports. It is about three conditions:
- Data in multiple currencies
- Data in multiple currencies
- Data in a single currency
You can reference the following link.
Currency conversion in Power BI reports - SQLBI
Best Regards,
Community Support Team _ xiaosunIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.