Need to calculate forex rate based on whether data in a separate table is specific text

[JRHans09]

**what happened to the ability to insert code and photos? I do not see those options any more. My request: I am building a report that compares Win Loss percentage of revenue from multiple branches using different base currencies and therefore need to convert the base currency into USD per job or invoice. I have two formulas in calculated columns that correctly determines the Forex Rates based on the job (not yet invoiced because it is pending, or never will be invoiced because the job is lost), or invoice (when the job is won), but I need to write a formula in the final measure that will check whether the job has been invoiced or not. The Forex Rate will be based on the Invoice Table if won, and based on the Jobs Table if lost or still pending. To explain further, booking ratios or Win Loss percentage of revenue is based on revenue won versus all revenue possible, at any given point, which would include prospective revenue and revenue lost. On a separate note, in reading other posts, many suggest to use calculated columns as little as possible to reduce resource use. So, two questions: 1. How do I write or rewrite the formula below in the measure to check if the job has been won and if it has, use the Forex Rate from the Invoice table, otherwise, use the Forex Rate from the Jobs Table? Total Rev USD ALL = SUMX( Jobs, Jobs[Revenue] * Jobs[Forex Rate to USD] ) 2. Can problem be solved inside of a measure Here are the two Forex Rate calculate columns: 1. Jobs Table: Forex Rate to USD = IF( Jobs[Base Currency] = "USD", 1, MAXX( TOPN( 1, FILTER( ForexRates, ForexRates[Base Currency] = Jobs[Base Currency] && ForexRates[Forex Date] <= Jobs[Date Entered] ), ForexRates[Forex Date], DESC ), ForexRates[Debtor Rate] ) ) 2. Invoice Table: Forex Rate to USD = IF( Invoices[Base Currency] = "USD", 1, MAXX( TOPN( 1, FILTER( ForexRates, ForexRates[Base Currency] = Invoices[Base Currency] && ForexRates[Forex Date] <= Invoices[Invoice Date] ), ForexRates[Forex Date], DESC ), ForexRates[Debtor Rate] ) ) Basically, I would like to write a formula in a measure that checks whether the Jobs[Status] = "W" and if it does, use the Invoice Table Forex Rate, otherwise, use the Jobs Table Forex Rate. I have tried many iterations of SUMX, CALCULATE, IF, VALUES, RELATED, etc, but I am not clear on how to use these in combination. The help is greatly appreciated. Thanks.

[v-juanli-msft]

Hi @JRHans09

I have made your post in the legible format as below:

---

@JRHans09 wrote:

**what happened to the ability to insert code and photos?

I do not see those options any more.

My request: I am building a report that compares Win Loss percentage of revenue from multiple branches using different base currencies and therefore need to convert the base currency into USD per job or invoice.

I have two formulas in calculated columns that correctly determines the Forex Rates based on the job (not yet invoiced because it is pending, or never will be invoiced because the job is lost), or invoice (when the job is won), but I need to write a formula in the final measure that will check whether the job has been invoiced or not.

The Forex Rate will be based on the Invoice Table if won, and based on the Jobs Table if lost or still pending.

To explain further, booking ratios or Win Loss percentage of revenue is based on revenue won versus all revenue possible, at any given point, which would include prospective revenue and revenue lost.

On a separate note, in reading other posts, many suggest to use calculated columns as little as possible to reduce resource use.

So, two questions:

1.How do I write or rewrite the formula below in the measure to check if the job has been won and if it has, use the Forex Rate from the Invoice table, otherwise, use the Forex Rate from the Jobs Table?

Total Rev USD ALL = SUMX( Jobs, Jobs[Revenue] * Jobs[Forex Rate to USD] )

2.Can problem be solved inside of a measure Here are the two Forex Rate calculate columns:

1. Jobs Table: Forex Rate to USD1 = IF( Jobs[Base Currency] = "USD", 1, MAXX( TOPN( 1, FILTER( ForexRates, ForexRates[Base Currency] = Jobs[Base Currency] && ForexRates[Forex Date] <= Jobs[Date Entered] ), ForexRates[Forex Date], DESC ), ForexRates[Debtor Rate] ) )

2. Invoice Table: Forex Rate to USD2 = IF( Invoices[Base Currency] = "USD", 1, MAXX( TOPN( 1, FILTER( ForexRates, ForexRates[Base Currency] = Invoices[Base Currency] && ForexRates[Forex Date] <= Invoices[Invoice Date] ), ForexRates[Forex Date], DESC ), ForexRates[Debtor Rate] ) )

Basically, I would like to write a formula in a measure that checks whether the Jobs[Status] = "W" and if it does, use the Invoice Table Forex Rate, otherwise, use the Jobs Table Forex Rate.

I have tried many iterations of SUMX, CALCULATE, IF, VALUES, RELATED, etc, but I am not clear on how to use these in combination.

The help is greatly appreciated. Thanks.

---

First, please let me know how Invoice table and Jobs Table look like and what relationship between them.

You could upload a picture to One-Drive for bussiness and share the link here.

Or could you see the "Photos" icon on the top of the reply window, if so, use this to upload pictures.

Best Regards

Maggie

[JRHans09]

Hi @v-juanli-msft, I do not have any ability to include code, or post any photos in the reply. I had intended to include a screen shot of the tables structures, but I cannot access any of the icons or links that I remember seeing int he past that allowed me to insert code or include/upload photos/jpegs, etc. All I see above this box are four options: Rich Text HTML Preview Quote Clicking on any of the first three work, but I still do not see either of the code / photos options. And Quote has no link. Nothing happens, even though it is brown which would indicate a hyperlink. Any ideas?

[v-juanli-msft]

Hi @JRHans09

When you upload file or picture to OneDrive for bussniess, then generate a link,

just copy the link and paste the link as text words in this reply window, eg.

https://docs.microsoft.com/en-us/powerquery-m/list-times

Which web browser do you use?  i use goole chrome and it shows correctly.

please clear the web cash and reopen the website.

Best Regards

Maggie

[JRHans09]

Maggie,

Thanks for the tip. I just cleared the cache and now I can see the options ribbon, again. However, the link to insert a photo is not working. Clicking on it does nothing. Is there some issue with Java and Chrome?

Here are hte formulas, for now:

Forex Rate for Jobs Table:

Forex Rate to USD = 
IF( 
    Jobs[Base Currency] = "USD",
    1,
    MAXX( 
        TOPN(
            1,
            FILTER(
                ForexRates,
                ForexRates[Base Currency] = Jobs[Base Currency]
                    && ForexRates[Forex Date] <= Jobs[Date Entered]
            ),
            ForexRates[Forex Date], DESC
        ),
        ForexRates[Debtor Rate]
    )
)

Forex Rate for Invoices Table

Forex Rate to USD = 
IF( 
    Invoices[Base Currency] = "USD",
    1,
    MAXX( 
        TOPN(
            1,
            FILTER(
                ForexRates,
                ForexRates[Base Currency] = Invoices[Base Currency]
                    && ForexRates[Forex Date] <= Invoices[Invoice Date]
            ),
            ForexRates[Forex Date], DESC
        ),
        ForexRates[Debtor Rate]
    )
)

Here is a link to the OneDrive folder with the OneNote screen shot of the table structure.

https://1drv.ms/u/s!AnazmZOz6zbElRVWc_5_DksPGcEg

Thanks so much for your help. It is late here so I will reply tomorow. 

[v-juanli-msft]

Hi @JRHans09

Does this helps:

Measure =
IF (
    MAX ( Jobs[revenue] ) = "W",
    SUMX ( Jobs, Jobs[Revenue] * Jobs[Forex Rate to USD1] ),
    SUMX ( Jobs, Jobs[Revenue] * RELATED ( Invoices[Forex Rate to USD2] ) )
)

Below is my pbix,

if the formula above doesn't solve your problem,

please give the correct results based on my example so that i can work directly to your problem.

Best Regards

Maggie

[JRHans09]

Dear Maggie,

Thanks for your help on this. Sorry it took me a while to get back to you.

The formula is working, for the most part. I made some changes, which you can see below:

Here is the original measure:

Total Rev USD ALL =
SUMX(
     Jobs,
     Jobs[Revenue] * Jobs[Forex Rate to USD]
)

Here is your suggestion:

Measure =
IF (
    MAX ( Jobs[revenue] ) = "W",
    SUMX ( Jobs, Jobs[Revenue] * Jobs[Forex Rate to USD1] ),
    SUMX ( Jobs, Jobs[Revenue] * RELATED ( Invoices[Forex Rate to USD2] ) )
)

My revised attempt, based on your measure:

Total Rev USD All +
IF(
// I changed this to check if the job was invoiced, 
// not just whether the job was won. Some jobs that are won, are not yet invoiced
    MAX( Jobs[Invoiced] ) = "Y",
// You had this first part below last, but it needs to be first, if the job is invoiced
    SUMX(
        Jobs,
        Jobs[Revenue] * RELATED( Invoices[Forex Rate to USD] )
    ),
    SUMX(
        Jobs,
        Jobs[Revenue] * Jobs[Forex Rate to USD]
    )
)

Below are the results shown in a table, which are working well, per line, however the total is not calculating properly.

Example, line 4 is:

1. Invoiced = Y

2. Job Rev USD = 2553.19

3. Total Rev USD All = 2579

4. Total Rev USD W = 2579

This is correct for 2579 since that job is invoiced and the revenue should not be based on the job date, but on the invoice date.

However, when you look at the total lines, you can see that 12,460 is at the bottom of each of the columns. It should not be. 12,460 is the total of only the jobs that are Invoiced = Y. I think this has to do with Filter Context, based on what I have read, but I am not sure why it would work per line, but not per the combined total. If you can help to explain why this is incorrect, and show me what formula changes are necessary, that would be very helpful.

Thanks, and please let me know if you need any additional info.

[v-juanli-msft]

Hi @JRHans09

Please read How to Get Your Question Answered Quickly

Best Regards

Maggie