Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
GraceTCL
Helper II
Helper II

Currency conversion and division error

Hi,

 

I am trying to show a measure in local currency rate (after catering for null values) and to convert it to value in USD.

For this I am trying to follow steps at https://www.daxpatterns.com/currency-conversion/.

 

Unfortunately I am getting this error:

sample.PNG

My sample file with above error is at link:

https://www.dropbox.com/s/hkhx0yziyakgtbn/Sample.pbix?dl=0

 

Can someone help? Thanks!

1 ACCEPTED SOLUTION

Hi @GraceTCL ,

 

You don't have the same granularity, on the exchange rate you have a value once a month and your data you have weekly values, altough they are all in the same month when you do the filtering of the table you can notice that the values for the weeks were the date does not match the end of the month you don't have results.

 

The image below comes from DAX studio and has you can see the result of the table is a empty value for the exchange rate:

MFelix_0-1620215773435.png

If by other and you change the table to the month and year you get results however this needs to be picked up from the one side of the relationship in this case the exchange rate however the total is still zero:

MFelix_1-1620215940172.png

An important part of the calculations is also the direction of the relationships in your case you have one to many from the exchange rate to the date table, if you use the date table you are using the many side of the relationship and you calculation will break.

 

If you use the formula I have sent (see example below adjusted to your file) the calculation is performed correctly:

 

Promo Cost USD V2 = 
VAR DatesExchange =
    SUMMARIZE (
        'Exchange Rate',
        'Exchange Rate'[end_date_id],
        'Exchange Rate'[exchange_one_usd]
    )
VAR Result =
    SUMX (
        DatesExchange,
        DIVIDE (
            [Promo Cost LC],
            'Exchange Rate'[exchange_one_usd]
        )
    )
RETURN
    Result

 

MFelix_2-1620216176694.png

Context is everything in measures and it comes from the tables themselves, passing trough the relationships and going into visualizations and filters. In this case your issue is granularity and relationships.

 

 

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
MFelix
Super User
Super User

Hi @GraceTCL .

 

The issue is with your granularity since you have information on a weekly basis but you exchange is on a monthly format the calculations aren't matching so you have blank values you need to have the following measure:

GMV_LC Exchange = 

    VAR DatesExchange =
        SUMMARIZE (
            'Exchange Rate',
            'Exchange Rate'[end_date_id],
            'Exchange Rate'[exchange_one_usd]
        )
    VAR Result =
                SUMX (
                    DatesExchange,
                    DIVIDE(CALCULATE(SUM(FoodMetrics[GMV_LC])) , 'Exchange Rate'[exchange_one_usd])
                )
    RETURN
        Result

 

also check the relationships:

MFelix_0-1620138193754.png

 

Result below and in attach PBIX file:

 

MFelix_2-1620138311443.png

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix Thanks for your reply. Your solution works. 

May I clarify - base on your proposed calculation, are we instead summarizing the exchange rate and not the numbers in the FoodMetrics tables to a month wise view? Am I interepting wrongly as I thought it should be the other way round where we summarize the FoodMetrics table?

 

If there are null values in GMV_LC problem, will this dax formula still work?

Hi @GraceTCL ,

 

That is correct because you have different granularities between both tables so you need to pick up the values of the exchange rate first and then make the division.

 

Has you may notice on the calculation provided by DAXPATTERNS you add a column to the summarization of the Main table with the exchange rate, in this case I'm not doing that I'm picking up the value for that month of the exchange rate and use it on the division.

 

To avoid errors you can add an adittional parameter on the divide with a 0 or some other value similar to this:

 

GMV_LC Exchange =
VAR DatesExchange =
    SUMMARIZE (
        'Exchange Rate',
        'Exchange Rate'[end_date_id],
        'Exchange Rate'[exchange_one_usd]
    )
VAR Result =
    SUMX (
        DatesExchange,
        DIVIDE (
            CALCULATE ( SUM ( FoodMetrics[GMV_LC] ) ),
            'Exchange Rate'[exchange_one_usd],
            0 -- alternative result if division is error or infinite
        )
    )
RETURN
    Result

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix 

Thanks for your reply. 

For this: "the calculation provided by DAXPATTERNS you add a column to the summarization of the Main table with the exchange rate" I notice that too.

However I still dont understand why it doesnt work as I am summarizing the data in FoodMetrics table to the same month level granularity as the exchange rate table.

 

Are you able to advise if there is something that I need to change in the formula that that link? Thank you!

Hi @GraceTCL ,

 

You don't have the same granularity, on the exchange rate you have a value once a month and your data you have weekly values, altough they are all in the same month when you do the filtering of the table you can notice that the values for the weeks were the date does not match the end of the month you don't have results.

 

The image below comes from DAX studio and has you can see the result of the table is a empty value for the exchange rate:

MFelix_0-1620215773435.png

If by other and you change the table to the month and year you get results however this needs to be picked up from the one side of the relationship in this case the exchange rate however the total is still zero:

MFelix_1-1620215940172.png

An important part of the calculations is also the direction of the relationships in your case you have one to many from the exchange rate to the date table, if you use the date table you are using the many side of the relationship and you calculation will break.

 

If you use the formula I have sent (see example below adjusted to your file) the calculation is performed correctly:

 

Promo Cost USD V2 = 
VAR DatesExchange =
    SUMMARIZE (
        'Exchange Rate',
        'Exchange Rate'[end_date_id],
        'Exchange Rate'[exchange_one_usd]
    )
VAR Result =
    SUMX (
        DatesExchange,
        DIVIDE (
            [Promo Cost LC],
            'Exchange Rate'[exchange_one_usd]
        )
    )
RETURN
    Result

 

MFelix_2-1620216176694.png

Context is everything in measures and it comes from the tables themselves, passing trough the relationships and going into visualizations and filters. In this case your issue is granularity and relationships.

 

 

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Jihwan_Kim
Super User
Super User

Hi, @GraceTCL 

I am not sure how your actual data model looks like, but I suggest changing the model a little bit like below.

All measures are in the sample pbix file.

Picture10.png

 

 

https://www.dropbox.com/s/yk60lce8i1i6qxw/Sample%20%281%29.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.