cancel
Showing results for
Did you mean:  Helper III

## Multiplying the value of the exchange rate

Hello!

I have this situation:

 VALUE RATE RESULT 1000 4.5 222.2222 2500 4.5 555.5556 100 Infinity 500 4.5 222.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  Community Support

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 )
`````` 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])
)`````` 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.
8 REPLIES 8  Super User IV

@ANM_97 ,Try Divide by 4.5 when blank

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

Proud to be a Super User!  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  Helper III

The value 4.5 is not static, it changes daily.

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

It depend on context, you can something like

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

Proud to be a Super User!  Helper III
``divide(sum([value]),if(isblank(max([RATE])),max([RATE]),max([RATE])))``

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

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)`````` 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.  Helper III

Hello,

Thank you for help!~

I probably didn't explain the situation correctly.

I have this table:

Date ex: 2020/02/28

 CLIENT VALUE IN CURRENCY= RON RATE CURRENCY VALUE_EUR A 500 2.6 AUD 104.17 =500/4.8 B 200 3.1 CAD 41.67 C 150 4.8 EUR 31.25 D 100 5.3 GBP 20.83 E 600 0.4 NOK 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:

 CLIENT VALUE IN CURRENCY= RON RATE CURRENCY VALUE_EUR A 500 2.6 AUD 192.31=500/2.6 B 200 3.1 CAD 64.52 C 150 4.8 EUR 31.25 D 100 5.3 GBP 18.87 E 600 0.4 NOK 1,500.00  Community Support

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 )
`````` 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])
)`````` 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. Announcements #### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories. #### Power BI Dev Camp - September 30th, 2021  