Forum Discussion

ngct1112's avatar
ngct1112
Icon for Post Patron rankPost Patron
6 years ago
Solved

How to SUMX value with Fixed Exchange%

I am looking for help for with the problem I still could not fix.   I am trying to SUMX the total value with exchange rate, in accordance with DATE. Refer to the FX table, In this case, the total ...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi  ngct1112 ,

     

    Sorry for the late reply!

    Create 2 measures as below:

    Measure 2 = 
var _maxdate=TOPN(1,FILTER('FX table','FX table'[Delivery Date]<=MAX('Table'[Delivery Date])),'FX table'[Delivery Date],DESC)
Return
SUMX(_maxdate,[Exchange Rate])*CALCULATE(SUM('Table'[Sales Price]))
    Measure 3 = SUMX('Table',[Measure 2])

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi  ngct1112 ,

 

Based on your raw data,could you pls advise me the expected output?And how to calculate it out?

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
ngct1112's avatar
ngct1112
Icon for Post Patron rankPost Patron
6 years ago

v-kelly-msft 

I would like to add a slicer on the page using "To Currency" From FX Table

 

The total calculation is sum by "Sales Price" from Table base on its Delivery Date's FX rate,

When choose JPY in the slicer:

= (11110*1) + (2.4*117) + (5112*1) + (920.1*115) = 122314.3

When choose HKD in the slicer:

= (11110*0.03) + (2.4*7.76) + (5112*0.035) + (920.1*7.75) = 766.619

 

Do you think it is possible to handle this situation? Great thanks.

 

Table:

Delivery DateSales PriceCurrency
6/5/202011110JPY
8/4/20192.4USD
12/10/20185112JPY
10/8/2020920.1USD

 

FX Table:

To CurrencyExchange RateDelivery DateFrom Currency
JPY1151/8/2019USD
HKD7.751/8/2019USD
JPY11/8/2019JPY
HKD0.031/8/2019JPY
JPY1171/7/2018USD
HKD7.761/7/2018USD
JPY11/7/2018JPY
HKD0.0351/7/2018JPY
  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi  ngct1112 ,

     

    Sorry for the late reply!

    Create 2 measures as below:

    Measure 2 = 
var _maxdate=TOPN(1,FILTER('FX table','FX table'[Delivery Date]<=MAX('Table'[Delivery Date])),'FX table'[Delivery Date],DESC)
Return
SUMX(_maxdate,[Exchange Rate])*CALCULATE(SUM('Table'[Sales Price]))
    Measure 3 = SUMX('Table',[Measure 2])

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
    • ngct1112's avatar
      ngct1112
      Icon for Post Patron rankPost Patron
      6 years ago

      v-kelly-msft , I have tried few testing on my current model and it works smoothly and acuately.

      And the formula you suggested could even convert FX% according to >2 designated Date.

       

      Appreciated with your help!

    • ngct1112's avatar
      ngct1112
      Icon for Post Patron rankPost Patron
      6 years ago

      Hi v-kelly-msft , may I have a little further question on this solution?

      This solution has been working very well. Yet, I am trying to add one more table to cacalute the other converted Price. 

      I found them not possible to have relationship with both 1.)FX table & 2.)Date table at the same time as below

      Do you have any idea how it could be possble linking them to a "Date table" to make a Date slicer in this situation?

      Great thanks!!

       

       

       

      https://drive.google.com/file/d/1zw0ZHpCDoo0ucQzrvcQADN1Nr_9iF96g/view?usp=sharing