Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
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
Solved! Go to Solution.
Try this measure:
BalanceTotal in Currency Totals = SUMX ( VALUES ('Currency'[Month] ), [BalanceTotal in Currency] )
Data model:
Result:
Proud to be a Super User!
Try this measure:
BalanceTotal in Currency Totals = SUMX ( VALUES ('Currency'[Month] ), [BalanceTotal in Currency] )
Data model:
Result:
Proud to be a Super User!
Thanks @DataInsights
Struggling to understand exactly what you did there but will dive deeper into that soon.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
103 | |
98 | |
98 | |
38 | |
37 |
User | Count |
---|---|
154 | |
120 | |
73 | |
73 | |
63 |