Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
My awesome friends:
i have following question and wonder if you can help
here is the first table:
Ship to Territories | Eur Exchange Rate |
United Kingdom | 1.12881 |
Africa | 0.91358 |
MEA Gulf States | 0.91358 |
Here is the 2nd table
Ship to Territories | Local Amount | EUR Amount |
United Kingdom | 12 | |
Africa | 43 | |
MEA Gulf States | 24 |
My goal is to use lookupvalue to look up exchange rate from the first table and then multiply the local amount to get Eur amount.
So far i am able to use lookupvalue function and get what i need. However, when i do calculate (sum (Euro Amount)), the Eur amount column is a text column instead of a calculation field....
how to solve this? again, i am good with lookupvalue function...but the return value is not number.
thank you all!
Solved! Go to Solution.
Hi @leilei787 ,
Select the column, go to Modeling tab , Data Type and change the format.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hi , can u provide the pbix file minus the sensitive info .
Regards
actually i have our IT department to add Euro amount so that i do not have do formula. thanks this is solved.
Hi @leilei787 ,
Select the column, go to Modeling tab , Data Type and change the format.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hi Nathaniel
Please see my screenshot.
so exchange rate is a custom column that i use lookupvalue to look up the exchange rate from another table. ( that field is a number field. so it should be the same format as number when i lookup)
as you can see, my data type is Decimal Number, format is Decimal Number...however, if you look at exchange rate symbol ( right side, red circle)....it is not a number format.
i have another custom column that relying on this exchange rate : Euro Amount net = Local Amount net * exchange rate. But this custom field is also not number neither. (2nd screenshot. therefore, my explicit calculation on Euro Amount net is not a calculation field neither)
Hi @leilei787
Have you tried wrapping your LOOKUPVALUE in VALUE function? If the result is a number, this should work just fine.
VALUE(LOOKUPVALUE(result_columnName, search_columnName, search_value))
Proud to be a Super User!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |