How to get values from 2 different columns based on a different criteria?

[harizothman]

Hello everyone,

I would like to create a table where it shows a total value for a different criteria.

For example, my data looks like this;

Country Name	Original Currency	Original Currency Amount	Converted Currency to  GBP
America	USD	1,000.00	-
Germany	EUR	2,000.00	1,829.61
France	EUR	1,500.00	1,372.21

My plan is to show a finalized table that looks like this;

Name	Report Amount
America	1,000.00
Germany	1,829.61
France	1,372.21

Please note that, for America, I want to show the original currency amount which is in USD 1,000.

For the other two, I want to show the converted currency to GBP.

I only know a way = CALCULATE(SUM(Original Currency), Original Currency="USD") but this will only show America.

Is there any other work around for this?

Thank you.

[v-stephen-msft · Accepted solution]

Hi @harizothman ,

You can use @FarhanAhmed 's method, the measure is this.

Report Amount1 = 
IF (
    MAX ( 'Table'[Country Name] ) = "America",
    SUM ( 'Table'[Original Currency Amount] ),
    SUM ( 'Table'[Converted Currency to  GBP] )
)

Or you can use SWITCH function to create a measure.

Report Amount2 = 
VAR OCA =
    SUM ( 'Table'[Original Currency Amount] )
VAR GBP =
    SUM ( 'Table'[Converted Currency to  GBP] )
RETURN
    SWITCH (
        MAX ( 'Table'[Country Name] ),
        "America", OCA,
        "France", GBP,
        "Germany", GBP
    )

The result is this.

You can check more details from here.

Best Regards,

Stephen Tao

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

[harizothman]

hi @FarhanAhmed and @v-stephen-msft ,

yes, the solutions provided answered my queries.

thanks for the help!

[FarhanAhmed]

= IF ( Original Currency="USD" , SUM(Original Currency), SUM(Converted Currency))

OR

= IF ( Country="America" , SUM(Original Currency), SUM(Converted Currency))