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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
rbpamintuan
Frequent Visitor

How to compute columns based on info from other columns

Good day to you all,

 

I tried searching for a solution for my problem but I can't seem to find any(or probably I was just searching for the wrong terms). I justed wanted to compute our income based on the difference of columns "Selling Fare" and "Published Fare". I already have a column based on this(see fig1) and it works ok. Unfortunately, if "Selling Fare" and "Published Fare" is in USD, I need to convert the amount based on the exchange rate from another table. I created a measure to do this(see Fig2) and is also working fine. My challenge is how can I combine both results so I can display all Income in PHP.

 

income php.PNG

 

usd exchange.PNG

 

currency code.PNGAny replies is highly appreciated.

1 ACCEPTED SOLUTION

Hi,

You are welcome.  You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

15 REPLIES 15
parry2k
Super User
Super User

@rbpamintuan so what is the issue? What is the relationship between these two tables?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I want to sum the result of:

If [Currency Code] is PHP

[Selling Fare] - [Published Fare]

+

If [Currency Code] is USD

[Selling Fare] - [Published Fare] * [Exchange Rate]

 

 

can you share how your tables are related.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi, @parry2k.

 

Here it is:

 

2019-11-29.png

 

Thank you in advance.

@rbpamintuan you can use related function to  make the calculation, add new column in your vARInvoiceDetails

 

New Column = 
IF ( RELATED( ARInvoiceDetail[Currency Code] ) = "PHP",
vARInvoiceDetail[Selling Fare] - vARInvoiceDetails[Published Fare],
vARInvoiceDetail[Selling Fare] - (vARInvoiceDetails[Published Fare] * RELATED ( ARInvoiceDetail[Exchange Rate] ) )
)

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k ,

 

I tried your solution below but I am getting this error:

error dax.PNG

 

I made this relationship but I am not sure if I got it correctly:

 

balances.PNG

 

Thank you so much for your patience.

hi  @rbpamintuan 

Could you please share a simple sample pbix file and your expected output for us have a test? That will be a great help.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-lili6-msft ,

 

Here's the sample file.

 

As mentioned above, I justs want to compute the difference(or income) between "SELLING FARE" and "PUBLISHED FARE". If currency is in USD, difference should be multiplied by the "ËXCHANGE RATE" from the table ARBalances. If in PHP, no multiplication should be done.

 

I think the solution provided above should work already. My only challenge at the moment is getting the relationships correctly.

 

many thanks for all the help 

Hi,

Try these calculated column formulas in the vARInvoices tab

Exchange rate = RELATED(ARBalances[EXCHANGE RATE])
Column = if([CURRENCY]="PHP",[SELLING FARE]-[PUBLISHED FARE],[Exchange rate]*([SELLING FARE]-[PUBLISHED FARE]))

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

DEar @Ashish_Mathur ,

 

Thanks for the response. May I ask if it's possible to do this as a measure instead? Until now I haven't figured out why I can't make any relationship with the table ARBalances. 

 

Looking forward to your reply.

Hi,

You are welcome.  You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Both measure and your earlier solution worked as I finally figured out to create a relationship between ArBalances and vArInvoices by removing the duplicates). However the resulting amount was reduced significantly due to the removed duplicates.

 

Again I would like to thank you for your patience.

You are welcome.  If my previous reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@rbpamintuan in your original post you mentioned totally different tables and my solution was based on those tables becuase they have one to one relationship, you need to be very specific what tables you are using, what column and what is their relationship.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k . My apologies for the confusion. I thought I already have the relationships correctly(noob non-techy guy here).

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.