Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
leilei787
Helper II
Helper II

lookupvalue to return as number instead of text

My awesome friends:

 

i have following question and wonder if you can help

 

here is the first table:

 

Ship to TerritoriesEur Exchange Rate
United Kingdom1.12881
Africa0.91358
MEA Gulf States0.91358

 

Here is the 2nd table

Ship to TerritoriesLocal AmountEUR Amount
United Kingdom12 
Africa43 
MEA Gulf States24 

 

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!

1 ACCEPTED SOLUTION
Nathaniel_C
Super User
Super User

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


text ch.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
YJ
Resolver II
Resolver II

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.

Nathaniel_C
Super User
Super User

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


text ch.PNG

 





Did I answer your question? Mark my post as a solution!

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)

power bi.PNG

Capture2.PNG

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))






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

hi danextian

 

i tried Value, but result data type still not numerical. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.