Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi community
I need your help one more time.
I have a report with one table that looks like the below:
Instrument | Used |
1,000 | |
EUR | 3,500 |
USD | 1,700 |
SGD | 600 |
2,000 |
Another table (fictive rate)
Currency | Fx rate from EUR |
USD | 1 |
SGD | 0.5 |
So what I want to have a new column in the first table that gives the amount converted into EUR except when instrument is blank or EUR.
I have made a relationship between the 2 tables with Instrument linked to Currency.
When calculating the new column I have tried:
Nw column = Sum('Table1'[Used])*Sum('Table2'[Fx rate from EUR])
But doesn't work. 🙄
I missed something most probably.
If you can help would be really appreciated.
Solved! Go to Solution.
Hi @TheBaz_57 ,
Here are the steps you can follow:
1. Create calculated column.
Nw column =
var _fx=CALCULATE(SUM('Table2'[Fx rate from EUR]),FILTER(ALL(Table2),'Table2'[Currency]=EARLIER('Table1'[Instrument])))
return
'Table1'[Used] * _fx
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @TheBaz_57 ,
Here are the steps you can follow:
1. Create calculated column.
Nw column =
var _fx=CALCULATE(SUM('Table2'[Fx rate from EUR]),FILTER(ALL(Table2),'Table2'[Currency]=EARLIER('Table1'[Instrument])))
return
'Table1'[Used] * _fx
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
User | Count |
---|---|
128 | |
112 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
116 | |
102 | |
71 | |
57 |