Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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-msft's avatar
    v-xiaosun-msft
    Community 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 _ xiaosun

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.