cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ANM_97
Helper III
Helper III

Multiplying the value of the exchange rate

Hello!

 

I have this situation:

 

VALUERATERESULT
10004.5222.2222
25004.5555.5556
100 Infinity
5004.5222.2222
2000 Infinity

 

How could I divide all the VALUES to the value 4.5? I should build a measure that will take that rate of exchange rate and use it in dividing.

 

I need help!

Thank you~

 

1 ACCEPTED SOLUTION

Hi @ANM_97 ,

Sorry for late back. Please try like this:

Measure = 
VAR vvalue =
    SUM ( 'Table'[VALUE IN CURRENCY= RON] )
VAR vmax =
    CALCULATE (
        MAX ( 'Table'[RATE] ),
        FILTER ( ALL ( 'Table' ), 'Table'[CURRENCY] = "EUR" )
    )
RETURN
    DIVIDE ( vvalue, vmax )

1.PNG

If you will use a slicer to change currency, you could try like this:

  • Create a new irrelevant table.
Filter Table = VALUES('Table'[CURRENCY])
  • Create a measure
Measure 2 = 
var vSelectedCurrency = SELECTEDVALUE('Filter Table'[CURRENCY])
VAR vvalue =
    SUM ( 'Table'[VALUE IN CURRENCY= RON] )
VAR vmax =
    CALCULATE (
        MAX ( 'Table'[RATE] ),
        FILTER ( ALL ( 'Table' ), 'Table'[CURRENCY] = vSelectedCurrency )
    )
RETURN
  IF(ISFILTERED('Filter Table'[CURRENCY]),  DIVIDE ( vvalue, vmax ),SUM('Table'[VALUE IN CURRENCY= RON])
)

2.PNG

For more details, please see the attachment.

 

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

View solution in original post

8 REPLIES 8
amitchandak
Super User IV
Super User IV

@ANM_97 ,Try Divide by 4.5 when blank

divide([value],if(isblank([RATE]),4.5,[RATE]))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

az38
Super User II
Super User II

Hi @ANM_97 

just create a new column and use it in your measures

Column = DIVIDE([Value], 4.5)

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

The value 4.5 is not static, it changes daily.

I need to take this value daily in a masure and realize the division. 

It depend on context, you can something like

divide(sum([value]),if(isblank(max([RATE])),4.5,max([RATE])))


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

divide(sum([value]),if(isblank(max([RATE])),max([RATE]),max([RATE])))

I need to always divide by the first value that is not NULL.

Hi @ANM_97 ,

So for the sample, the value of 100 should divide 4.5 as well. And the result should be 22.22. Right?  If so, please try like this:

Measure = 
var vvalue = SUM('Table'[VALUE])
var vmax = CALCULATE(MAX('Table'[RATE]),ALLSELECTED('Table'))
return
DIVIDE(vvalue,vmax)

2.PNG 

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

Hello,

Thank you for help!~


I probably didn't explain the situation correctly.

I have this table:

Date ex: 2020/02/28

CLIENTVALUE IN CURRENCY= RONRATECURRENCYVALUE_EUR
A5002.6AUD                   104.17 =500/4.8
B2003.1CAD                     41.67
C1504.8EUR                     31.25
D1005.3GBP                     20.83
E6000.4NOK                   125.00

 

How can I divide all the values in the VALUE column at the EURO exchange rate (it is influenced by the date, it is not static), without being influenced by the rest of the currencies?

 

VALUE_EUR is the expected result.

 

If I use this masure:

Measure = 
var vvalue = SUM('Table'[VALUE])
var vmax = CALCULATE(MAX('Table'[RATE]),ALLSELECTED('Table'))
return
DIVIDE(vvalue,vmax)

 

Obtain the following results:

 

CLIENTVALUE IN CURRENCY= RONRATECURRENCYVALUE_EUR
A5002.6AUD

                   192.31=500/2.6

 

B2003.1CAD                 64.52
C1504.8EUR                     31.25
D1005.3GBP                     18.87
E6000.4NOK               1,500.00



Hi @ANM_97 ,

Sorry for late back. Please try like this:

Measure = 
VAR vvalue =
    SUM ( 'Table'[VALUE IN CURRENCY= RON] )
VAR vmax =
    CALCULATE (
        MAX ( 'Table'[RATE] ),
        FILTER ( ALL ( 'Table' ), 'Table'[CURRENCY] = "EUR" )
    )
RETURN
    DIVIDE ( vvalue, vmax )

1.PNG

If you will use a slicer to change currency, you could try like this:

  • Create a new irrelevant table.
Filter Table = VALUES('Table'[CURRENCY])
  • Create a measure
Measure 2 = 
var vSelectedCurrency = SELECTEDVALUE('Filter Table'[CURRENCY])
VAR vvalue =
    SUM ( 'Table'[VALUE IN CURRENCY= RON] )
VAR vmax =
    CALCULATE (
        MAX ( 'Table'[RATE] ),
        FILTER ( ALL ( 'Table' ), 'Table'[CURRENCY] = vSelectedCurrency )
    )
RETURN
  IF(ISFILTERED('Filter Table'[CURRENCY]),  DIVIDE ( vvalue, vmax ),SUM('Table'[VALUE IN CURRENCY= RON])
)

2.PNG

For more details, please see the attachment.

 

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

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors