How to compute columns based on info from other columns

[rbpamintuan]

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.

[parry2k]

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

[rbpamintuan]

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]

[parry2k]

can you share how your tables are related.

[rbpamintuan]

Hi, @parry2k.

Here it is:

Thank you in advance.

[parry2k]

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

[rbpamintuan]

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.

[v-lili6-msft]

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

[rbpamintuan]

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 

[Ashish_Mathur]

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.

[rbpamintuan]

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.

[Ashish_Mathur · Accepted solution]

Hi,

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

Hope this helps.

[rbpamintuan]

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.

[Ashish_Mathur]

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

[parry2k]

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

[rbpamintuan]

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