Forum Discussion

key_master_95's avatar
key_master_95
Frequent Visitor
2 years ago
Solved

Convert currency without exchange rate

Hi, all!

I have an issue that I can't overcome.

I have one table Coffee with countries, sales in EUR and volume of sale

and I have other table with countries, EUR, CHF

Question  -how I can calculate total Sales in Eur and Total Sales in CHF?

Thank you in advance!

Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi key_master_95 ,

    Your data table may be complicated, if possible, please provide a simplified sample data for us to test, thank you.
    This is a test dataset I created myself and I'm not sure if it matches the data model you are using:


    I am using the following two DAXes for this calculation:

    Total Sales in EUR = 
SELECTEDVALUE(Coffee[sales in EUR（unit price）]) * SELECTEDVALUE(Coffee[volume of sale])
    Total Sales in CHF = 
SUMX('Coffee', 'Coffee'[Total Sales in EUR] / RELATED('Table'[EUR]) * RELATED('Table'[CHF]))


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

  • zenisekd's avatar
    zenisekd
    2 years ago

    with country columns related it should work just the same. Only requirement would be that both need to be in the same data type (text) and both contain the same values: BE-BE or Belgium-Belgium. I am suposing here that in the country/currency table you have only one line per country. Please shere the error message.

5 Replies

  • zenisekd's avatar
    zenisekd
    Super User
    2 years ago

    The best solution would be to have a relationship between the coffee sale table and the rate table

     

     

    Next, you can use a measure:

     

     or calculate a column.

     

    • key_master_95's avatar
      key_master_95
      Frequent Visitor
      2 years ago

      Hi!

      Thank you for the answer.

      I can't build relation like Currency to currency 'cos in Coffee table haven't currency - only contries. 

      I tried to implement your formula with connection Country to Contry, but it didn't work - PBI can't find related table (it's text of error)

      • zenisekd's avatar
        zenisekd
        Super User
        2 years ago

        with country columns related it should work just the same. Only requirement would be that both need to be in the same data type (text) and both contain the same values: BE-BE or Belgium-Belgium. I am suposing here that in the country/currency table you have only one line per country. Please shere the error message.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi key_master_95 ,

    Your data table may be complicated, if possible, please provide a simplified sample data for us to test, thank you.
    This is a test dataset I created myself and I'm not sure if it matches the data model you are using:


    I am using the following two DAXes for this calculation:

    Total Sales in EUR = 
SELECTEDVALUE(Coffee[sales in EUR（unit price）]) * SELECTEDVALUE(Coffee[volume of sale])
    Total Sales in CHF = 
SUMX('Coffee', 'Coffee'[Total Sales in EUR] / RELATED('Table'[EUR]) * RELATED('Table'[CHF]))


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

  • Harper_King_1's avatar
    Harper_King_1
    New Member
    1 year ago

    You can calculate total sales in EUR and CHF by applying a conversion rate. If you have a table with sales in EUR and the volume of sales, you can multiply the volume by the sales price in EUR to get the total sales. Then, to convert the total sales into CHF, you need the current exchange rate between EUR and CHF. You can find more about how to adjust your menu and customize orders by checking out 7 Brew Menu Options for creative drinks that match your preferences!