cancel
Showing results 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

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.

Any replies is highly appreciated.

1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
15 REPLIES 15
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.

Frequent Visitor

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]

Super User

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.

Frequent Visitor

Hi, @parry2k.

Here it is:

Super User

@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.

Frequent Visitor

Hi @parry2k ,

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

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

Thank you so much for your patience.

Community Support

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

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.
Frequent Visitor

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

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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.

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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.

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

@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.

Frequent Visitor

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

Announcements

#### 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.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors