Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
akapelle
Frequent Visitor

Measure based on dynamic selection not adding up to total

Help appreciated, the solutions found online did not help me so far.
In our data model the exchange rate over the total period is determined based on the max selected period (month) and with this exchange rate all balances from the fact table should be multiplied. Ergo if months 1 and 2 are both selected then both months' balance needs to be multiplied with the exchange rate of month 2. 

I've added a simplified model below to illustrate my issue: the measure works fine on each row but not on total levels. I know this is a common issue and how PowerBI handles measure totals, but I have not managed to tackle it yet.

Exchange rate formula = 

 

ExchangeRate = 
var _maxselected = maxx(ALLSELECTED(Balances[Month]),Balances[Month])
var _currencyrate = calculate(average('Currency'[Rate]),'Currency'[Month] = _maxselected)
return 
_currencyrate

 

 

Balance total in currency formula = 

 

BalanceTotal in Currency = 
sumx(Balances,Balances[Balance] * [ExchangeRate])

 

 

Below screenshot shows the simplified sample data (top left) and outcome (bottom right); exchange rate selection working fine and balance total when not aggregated or aggregated to Month level works fine but not on total level where it ends up at 2100 instead of 2060. It's clear what's going wrong but I haven't found a workable fix.
screenshot pbi.PNG
I appreciate your help in tackling this issue in the most precise way. For further reference here is also an option I tried by calculating it with a summarize function. I didn't upload the data model since there is no connection between the currency dim table and the fact table, since the exchange rate is based dynamically on the selection and not based on a join on month columns.

 

 

BalanceTotal in Currency using Summarize = 
var _summary = summarize(Balances,Balances[AccountID],Balances[Month],"BalanceTotal",sum(Balances[Balance]))
var _sumx = sumx(_summary,[BalanceTotal] * [ExchangeRate])
return
_sumx

 

 

 

Thank you in advance

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@akapelle,

 

Try this measure:

 

BalanceTotal in Currency Totals = SUMX ( VALUES ('Currency'[Month] ), [BalanceTotal in Currency] )

 

Data model:

 

DataInsights_1-1634395957071.png

 

Result:

 

DataInsights_0-1634395656452.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
DataInsights
Super User
Super User

@akapelle,

 

Try this measure:

 

BalanceTotal in Currency Totals = SUMX ( VALUES ('Currency'[Month] ), [BalanceTotal in Currency] )

 

Data model:

 

DataInsights_1-1634395957071.png

 

Result:

 

DataInsights_0-1634395656452.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks @DataInsights 
Struggling to understand exactly what you did there but will dive deeper into that soon.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.