Multiplying the value of the exchange rate

[ANM_97]

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~

[amitchandak]

@ANM_97 ,Try Divide by 4.5 when blank

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

[az38]

Hi @ANM_97 

just create a new column and use it in your measures

Column = DIVIDE([Value], 4.5)

[ANM_97]

The value 4.5 is not static, it changes daily.

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

[amitchandak]

It depend on context, you can something like

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

[ANM_97]

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

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

[v-xuding-msft]

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)

 

[ANM_97]

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

[v-xuding-msft · 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 )

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.