Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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~
Solved! Go to 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:
Filter Table = VALUES('Table'[CURRENCY])
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.
@ANM_97 ,Try Divide by 4.5 when blank
divide([value],if(isblank([RATE]),4.5,[RATE]))
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])))
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)
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 |
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:
Filter Table = VALUES('Table'[CURRENCY])
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |