Skip to main content
cancel
Showing results for 
Search instead 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

Reply
ghaines
Helper II
Helper II

SUMX difficulty: Does not perform as expected in the total

I've been having difficulty using SUMX in a particular situation and I've reduced the complexity of the model almost to nothing and I still can't get the expected result.

 

I have a table of fixed exchange rates consolidating to a single currency, and a list of transactions with their currency recorded.  You can see they have a different column name for the two, so the use of columns in a visual later is unambiguous:

ghaines_0-1693189753065.png

ghaines_1-1693190053060.png

 

 

I want to calculate the consolidated value:

 

 

 

ExpensesToCommonCurrency = 
SUMX(VALUES('Exchange Rates FY24'[Local Currency]),
        DIVIDE(SUM(Expenses[transactionamount]) , SELECTEDVALUE('Exchange Rates FY24'[FX]), BLANK())
)

 

 

 

which works with 'Exchange Rates FY24' context, but not in aggregate (showing rows with no data):

ghaines_2-1693190090197.png

 

I have tried changing SELECTEDVALUE to MAX or MIN but in each case it is completely losing the 'Exchange Rates FY24' context from the SUMX and just calculating the max or min across the entire XR table.

 

Edit:  If I switch to SUMX('Exchange Rates FY24', [Expression]) then I get double the expected value for EUR and CHF, despite the fact that the FX column has no direct relationship with the Expenses table at all.  And the aggregate is still blank.

 

What am I overlooking?

 

Greg

1 ACCEPTED SOLUTION
ghaines
Helper II
Helper II

Found the solution.  In the aggregate, the SUMX context is not passed to sum(transactionamount) unless it is wrapped in a "CALCULATE".  I don't know what the context is if you don't wrap it, but this works:

 

 

 

ExpensesToEUR = 
SUMX(VALUES('Exchange Rates FY24'[FX]),
        DIVIDE(CALCULATE(SUM(Expenses[transactionamount])) , 'Exchange Rates FY24'[FX])
)

 

 

Side note, I was struggling with a similar problem where table A filters table B and C, and I need to calculate something using B with a lookup of a value in C.  SELECTEDVALUE on Table C was not working within a SUMX.  Wrapping it in a CALCULATE is the likely answer.

View solution in original post

3 REPLIES 3
ghaines
Helper II
Helper II

Found the solution.  In the aggregate, the SUMX context is not passed to sum(transactionamount) unless it is wrapped in a "CALCULATE".  I don't know what the context is if you don't wrap it, but this works:

 

 

 

ExpensesToEUR = 
SUMX(VALUES('Exchange Rates FY24'[FX]),
        DIVIDE(CALCULATE(SUM(Expenses[transactionamount])) , 'Exchange Rates FY24'[FX])
)

 

 

Side note, I was struggling with a similar problem where table A filters table B and C, and I need to calculate something using B with a lookup of a value in C.  SELECTEDVALUE on Table C was not working within a SUMX.  Wrapping it in a CALCULATE is the likely answer.

DataNinja777
Skilled Sharer
Skilled Sharer

Hi Greg,

 

I would just divide the sum of Expenses[transactionamount] by the exchange rates, and the measure amount below is already in converted into EUR amount.  

Sakiko_0-1693191903222.png

I don't think you need sumx to achieve what you need to do.  

This gives me an error

ghaines_0-1693192399863.png

 

but I found the solution

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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