cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## DAX sumx and if

Hello all,

I need a little help here. I'm trying to create a new measure that converts a contract value to USD (the project table tells me the currency type of the contract_value). The below measure works for all currencies except for USD:

Contract Value (USD) = sumx(PROJECT_INFO, PROJECT_INFO[CONTRACT_VALUE]* LOOKUPVALUE(CURRENCY_EXCHANGE[SELL_RATE],CURRENCY_EXCHANGE[CURRENCY_ID], PROJECT_INFO[CURRENCY_ID]))

The currency_exchange table has all the rates except USD (the ERP is a USD based system), so I'm getting no values in the measure. Below is my attempt to forces a USD calculation.

Contract Value (USD) = if(project_info[currency_id]<>'USD', sumx(PROJECT_INFO, PROJECT_INFO[CONTRACT_VALUE]* LOOKUPVALUE(CURRENCY_EXCHANGE[SELL_RATE],CURRENCY_EXCHANGE[CURRENCY_ID], PROJECT_INFO[CURRENCY_ID])), [project_info[contract_value]*1)

Any advice from you pros is greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III

## Re: DAX sumx and if

Hi @vxmine ,

try this

``Contract Value (USD) = sumx(PROJECT_INFO, PROJECT_INFO[CONTRACT_VALUE]* LOOKUPVALUE(CURRENCY_EXCHANGE[SELL_RATE],CURRENCY_EXCHANGE[CURRENCY_ID], PROJECT_INFO[CURRENCY_ID], 1))``

Please mark my post as solution, this will also help others.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether

4 REPLIES 4
Highlighted
Super User III

## Re: DAX sumx and if

Hi @vxmine ,

try this

``Contract Value (USD) = sumx(PROJECT_INFO, PROJECT_INFO[CONTRACT_VALUE]* LOOKUPVALUE(CURRENCY_EXCHANGE[SELL_RATE],CURRENCY_EXCHANGE[CURRENCY_ID], PROJECT_INFO[CURRENCY_ID], 1))``

Please mark my post as solution, this will also help others.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether

Highlighted
Frequent Visitor

## Re: DAX sumx and if

That worked, thanks!

Highlighted
Frequent Visitor

## Re: DAX sumx and if

As a side note, how did you know the ,1 is an argument? Is it common for DAX? I'm new to Power BI.

Highlighted
Super User III

## Re: DAX sumx and if

You can find the parameter in the documentation.

``LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…[, <alternateResult>])``

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

https://dax.guide/lookupvalue/

Please mark my post as solution, this will also help others.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors