Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a problem calculating a "balance" into a "target currency" with proper totals. I know I need an iterator somewhere on the way but I'm "blaked out" and I don't find my way out of this.
The "basics" were taken from the article written by @ImkeF: Easy Profit and Loss and other (account) scheme reports in Power BI and Power Pivot using DAX using her "magic measure" adapted for my report
Balance for Report (LC) =
CALCULATE (
[Total Amount],
FILTER ( ALL ( dimDate[Date] ), dimDate[Date] <= MAX ( dimDate[Date] ) ),
dimReport
)
The reduced schema is the following (for convenience I have attached the PBIX file: Balance in Target currency.pbix )
And everything is fine when showing the report in the local currency, as registered in the fact table:
Now the problem(s) came.
For simplicity in the Fact table I have only 2 currency: EUR & USD. Each company will have transactions in their respective currency.
Because I need to convert all the amount to the USD, the ExchangeRates table will contain only the "other currencies" (in this case EUR) and the conversion rates to USD. Only one record per month (only the first day of the month) per currency so I have created a CurrencyKey column in the form of YYYYMM & Currency Code
Because the Balance amount is "re-evaluated" based on the rate in the "report month" (not at transaction month) I have used this measure:
Balance for Report (USD) =
VAR OriginalAMount = [Balance for Report (LC)]
VAR CurrencyCode =
MAX (dimCompany[Currency] )
VAR ExchangeRate =
LOOKUPVALUE (
ExRates[Rate],
ExRates[CurrencyKey], FORMAT ( max(dimDate[Date]), "yyyymm" ) & CurrencyCode,
1
)
VAR ConvertedAmount = OriginalAMount * ExchangeRate
RETURN
ConvertedAmount
The values are converted correctly "per company/month" but the totals are wrong:
I have tried to digg some more articles written by Marco Russo and Alberto Ferrari like:
But it seems like I don't understand DAX yet to get the proper results (totals).😢
My "closest match", because I have diferent totals from my initial test but not yet the proper totals is this:
Balance for Report (USD) (RT) =
VAR __table =
SUMMARIZE (
FactTable,
FactTable[Company],
FactTable[Account],
"__value", [Balance for Report (USD)]
)
var __result=IF (
HASONEVALUE (FactTable[Company]),
[Balance for Report (USD)],
SUMX ( __table, [__value] )
)
RETURN
__result
Is there a way to have the proper totals in USD? (The measure shoud work with or without company name as "details")
Kind Regards,
Lucian
Solved! Go to Solution.
Hello @Greg_Deckler ,
Thank you again for your time, but I think I have found a solution.
The problem in my last message were some missing balance values for months that have no transactions.
It seems that the problem was SUMMARIZE, so I have created another table using SUMMARIZECOLUMNS:
FactTable for BALANCE =
ADDCOLUMNS (
SUMMARIZECOLUMNS (
dimDate[Year-Month],
FactTable[Company],
FactTable[Currency],
FactTable[Account]
),
"Balance LCY", [Balance],
"Balance EUR", [Balance]
* LOOKUPVALUE (
ExRates[Rate],
ExRates[CurrencyKey], SUBSTITUTE ( dimDate[Year-Month], "-", "" ) & FactTable[Currency],
1
)
)
And this time, when an account does not have a transaction, will get the value from the last month.
Then, just link this calculated table to the YMBridge, dimAccount and dimCompany
Then, a simple measure did the rest:
Balance from FactTable for BALANCE =
var LastMonth=MAX ( dimDate[Year-Month] )
return
CALCULATE (
SUM ( 'FactTable for BALANCE'[Balance EUR] ),
FILTER ( ALL ( dimDate ), dimDate[Year-Month] = LastMonth ),
dimReport
)
So the final report looks correct
The only questions that remains:
1. Is it possible to create a DAX code that will avoid this physical table FactTable for BALANCE and use virtual relationships?
2. Taking into account that the real model contains over 2.5 M rows it should be better as it it now - with this physical table with physical relationships?
Kind Regards,
Lucian
(link to final report - Balance in Target currency_v4.pbix )
@Lucian - Not sure. This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Hello @Greg_Deckler ,
Thank you for your quick response and for reminded me of that article: Measure Totals, The Final Word - I am using it succesfully for a "simpler" report that basically do the same except that "extra" dimReport table that do some extra filtering.
And because of this extra table I cannot "see" how to adapt the suggested articles where using multiple columns from same table.
In my case the measure depend on two tables dimDates and dimReport and it seems that the SUMMARIZE part is the problem resulting that SUMX does not have the proper table for calculate in here:
Balance for Report (USD) (RT) =
VAR __table =
SUMMARIZE (
FactTable,
FactTable[Company],
FactTable[Account],
"__value", [Balance for Report (USD)]
)
var __result=IF (
HASONEVALUE (FactTable[Company]),
[Balance for Report (USD)],
SUMX ( __table, [__value] )
)
RETURN
__result
Can you give me a "bump" into the right direction?... 🙂
Kind Regards,
Lucian
@Lucian - Can you post some sample data as text and what you expect as output? It is much more efficient to troubleshoot this stuff if I can quickly recreate the scenario.
Hello @Greg_Deckler ,
And many thanks for your time.
I thought the PBIX file is more usefull because it contains entire model and measures I have used until now, thats why I posted a link in my initial message, and if needed, for convenience I put it again here: Balance in Target currency.pbix .
But, if you prefer data as text, here it comes (but it seems that I cannot properly format the tables - the posting engine complains that there is some error in html and remove any table formatting - sorry):
The FactTable is looking like this (date format is dd.mm.yyyy):
Company | Currency | Date | Account | Amount |
A | EUR | 01.01.2020 | 1 | 10 |
A | EUR | 15.01.2020 | 2 | 20 |
A | EUR | 16.01.2020 | 3 | 30 |
B | USD | 02.01.2020 | 1 | 40 |
B | USD | 16.01.2020 | 2 | 50 |
A | EUR | 26.02.2020 | 1 | 60 |
A | EUR | 27.02.2020 | 2 | 70 |
B | USD | 13.02.2020 | 1 | 80 |
B | USD | 27.02.2020 | 2 | 90 |
A | EUR | 22.03.2020 | 1 | 100 |
A | EUR | 23.03.2020 | 3 | 110 |
B | USD | 09.03.2020 | 1 | 120 |
B | USD | 23.03.2020 | 3 | 130 |
A | EUR | 16.04.2020 | 2 | 140 |
A | EUR | 17.04.2020 | 3 | 150 |
B | USD | 03.04.2020 | 2 | 160 |
B | USD | 17.04.2020 | 3 | 170 |
I need to calculate a balance for the account based on the report design table (dimReport) :
Description | Account |
Software | 1 |
Software | 2 |
Services | 3 |
Using the Balance for Report (LC) measure I have managed to obtain the report in the "local currency" (the exact values from the fact table) that look like this (in correct form and correct totals):
Description | 2020-01 | 2020-02 | 2020-03 | 2020-04 | Grand Total |
Services | 30 | 30 | 270 | 590 | 590 |
A | 30 | 30 | 140 | 290 | 290 |
B | 130 | 300 | 300 | ||
Software | 120 | 420 | 640 | 940 | 940 |
A | 30 | 160 | 260 | 400 | 400 |
B | 90 | 260 | 380 | 540 | 540 |
Grand Total | 150 | 450 | 910 | 1530 | 1530 |
The problem is that I have to convert all the values for the "A" company from EUR to USD.
I have managed to obtain correct values for each company but incorrect values for the totals with the measure Balance for Report (USD),
and what I need or expect is this table:
Row Labels | 2020-01 | 2020-02 | 2020-03 | 2020-04 | Grand Total |
Services | 3 | 6 | 172 | 416 | 416 |
A | 3 | 6 | 42 | 116 | 116 |
B | 130 | 300 | 300 | ||
Software | 93 | 292 | 458 | 700 | 700 |
A | 3 | 32 | 78 | 160 | 160 |
B | 90 | 260 | 380 | 540 | 540 |
Grand Total | 96 | 298 | 630 | 1116 | 1116 |
From your recommended article Measure Totals, The Final Word my Balance for Report (USD) measure should be your "m_Single" measure and the Balance for Report (USD) (RT) should be your "final measure" but it seems that I didn't create the proper combination of SUMMARIZE/SUMX because the totals are still wrong than expected.
And I'm stuck trying different SUMMARIZE but no good. I don't see the proper "granularity" or proper combination.
If there are any informations I need to provide, just let me know.
Kind Regards,
Lucian
@Lucian - Must have missed the original PBIX link, I still can't find it!! I'll have a look.
Hello @Greg_Deckler
And many thanks again for your time.
Meanwhile I have tried something and now I veeery close, but still need some help. I'll try to explain the steps I did so maybe you could point me out the error in my logic.
Because I don't fully understand how DAX works I have tried to do a "step-by-step" approach and create some intermediate tables just to chech I have the correct values.
So, back to the initial problem: the report table will "dictate" how the final data should look like, and in my previous message I draw by hand how the table should look like (I post it below as a picture, because the text tables in this post will look badly):
The last "Grand total" column is not needed, because last month Balance it contains the "final values" I need, that's why in the above picture is not drawn.
1. My first step was to get the Balance in local currency for each month, company and account (the company is required because the currency/exchange rate will depend on it). So I have created a table just to check I have proper values:
Step1 - Get Balance in LCY =
ADDCOLUMNS (
SUMMARIZE (
FactTable,
dimDate[Year-Month],
FactTable[Company],
FactTable[Currency],
dimAccount[AccountNo]
),
"Balance LCY", [Balance]
)
Where the Balance measure is:
Balance =
CALCULATE (
[Total Amount],
FILTER ( ALL ( dimDate[Date] ), dimDate[Date] <= MAX ( dimDate[Date] ) )
)
And the Total Amount is just a simple SUM on the FactTable[Amount]
Total Amount = SUM(FactTable[Amount])
And the result table:
2. Now in Step2 I have to convert the Balance LCY at corresponding month rate.
Step2 - Convert to Target Currency =
ADDCOLUMNS (
'Step1 - Get Balance in LCY',
"Rate", LOOKUPVALUE (
ExRates[Rate],
ExRates[CurrencyKey], SUBSTITUTE ( 'Step1 - Get Balance in LCY'[Year-Month], "-", "" ) & 'Step1 - Get Balance in LCY'[Currency],
1
),
"Balance EUR", [Balance LCY]
* LOOKUPVALUE (
ExRates[Rate],
ExRates[CurrencyKey], SUBSTITUTE ( 'Step1 - Get Balance in LCY'[Year-Month], "-", "" ) & 'Step1 - Get Balance in LCY'[Currency],
1
)
)
The Rate colum was added to the table just to be asured I have the correct rate I multiply the balance.
The resulting table:
The final step.
Because the tables were physicaly created I have to complete the model with proper relationships and an "bridge-table" containing just Year-Month colum.
Created a simple measure that will SUM at month level based on the dimReport table:
Total Balance USD from STEP2 =
CALCULATE (
SUM ( 'Step2 - Convert to Target Currency'[Balance EUR] ),
FILTER ( ALL ( dimDate ), dimDate[Year-Month] = MAX ( dimDate[Year-Month] ) ),
dimReport
)
And the resulting matrix is pretty close of my needed report:
Just some missing values because on that month were no transaction in that accounts and the Balance in "Step1" table did not return the same value from the previous month as it should. But now all the totals are correct in respect to displayed values.
I have attached the new PBIX file: Balance in Target currency_v2.pbix
Is there a way to obtain the "missing" values for the accounts in the month there were no transactions?... because the balance should roll up month by month.
And would be nice if there is a way to obtain the final result without these intermediate tables (probably with a giant DAX that should use some virtual relationships)?
Kind Regards,
Lucian
Hello @Greg_Deckler ,
Thank you again for your time, but I think I have found a solution.
The problem in my last message were some missing balance values for months that have no transactions.
It seems that the problem was SUMMARIZE, so I have created another table using SUMMARIZECOLUMNS:
FactTable for BALANCE =
ADDCOLUMNS (
SUMMARIZECOLUMNS (
dimDate[Year-Month],
FactTable[Company],
FactTable[Currency],
FactTable[Account]
),
"Balance LCY", [Balance],
"Balance EUR", [Balance]
* LOOKUPVALUE (
ExRates[Rate],
ExRates[CurrencyKey], SUBSTITUTE ( dimDate[Year-Month], "-", "" ) & FactTable[Currency],
1
)
)
And this time, when an account does not have a transaction, will get the value from the last month.
Then, just link this calculated table to the YMBridge, dimAccount and dimCompany
Then, a simple measure did the rest:
Balance from FactTable for BALANCE =
var LastMonth=MAX ( dimDate[Year-Month] )
return
CALCULATE (
SUM ( 'FactTable for BALANCE'[Balance EUR] ),
FILTER ( ALL ( dimDate ), dimDate[Year-Month] = LastMonth ),
dimReport
)
So the final report looks correct
The only questions that remains:
1. Is it possible to create a DAX code that will avoid this physical table FactTable for BALANCE and use virtual relationships?
2. Taking into account that the real model contains over 2.5 M rows it should be better as it it now - with this physical table with physical relationships?
Kind Regards,
Lucian
(link to final report - Balance in Target currency_v4.pbix )
@Lucian - Physical tables and relationships will always perform better than DAX code that emulates it.
Hello @Greg_Deckler ,
Thank you again for your time, and for confirmation.
Also the other suggestion how to correct measure total I have used for another reports so it worth a "kudo" 😀
Kind Regards,
Lucian
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |