cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leilei787
Frequent Visitor

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

Accepted Solutions
Super User IV
Super User IV

Re: lookupvalue to return as number instead of text

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
Super User IV
Super User IV

Re: lookupvalue to return as number instead of text

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

leilei787
Frequent Visitor

Re: lookupvalue to return as number instead of text

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

Super User I
Super User I

Re: lookupvalue to return as number instead of text

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."
www.linkedin.com/in/danebelarminocpa
Highlighted
leilei787
Frequent Visitor

Re: lookupvalue to return as number instead of text

hi danextian

 

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

YJ Resolver II
Resolver II

Re: lookupvalue to return as number instead of text

Hi , can u provide the pbix file minus the sensitive info . 

Regards 

leilei787
Frequent Visitor

Re: lookupvalue to return as number instead of text

actually i have our IT department to add Euro amount so that i do not have do formula. thanks this is solved.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors