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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Lucian
Responsive Resident
Responsive Resident

How to correct measure to calculate totals properly (balance amount re-evaluated by currency)

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 @ImkeFEasy 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 )

Balance_ReportSchema.png

And everything is fine when showing the report in the local currency, as registered in the fact table:

 

Balance_LocalCurrency.png

 

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.

FactTable_Sample.png

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

ExchangeRateTable.png

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:

 

Balance_USD_BadTotals.png 

 

I have tried to digg some more articles written by Marco Russo and Alberto Ferrari like:

Cumulative total 

Currency conversion 

Semi-Additive Measures in DAX 

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

 

Balance_USD_RT_BadTotals.png

 

 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

1 ACCEPTED SOLUTION
Lucian
Responsive Resident
Responsive Resident

Hello @Greg_Deckler ,

 

Thank you again for your time, but I think I have found a solution.

 

FinalReport_and_CorrectData_corresponds.png

 

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

NewModel_v4.png

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

Report_from_FactTable_for_BALANCE.png

 

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 )

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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):

 

CompanyCurrencyDateAccountAmount
AEUR01.01.2020110
AEUR15.01.2020220
AEUR16.01.2020330
BUSD02.01.2020140
BUSD16.01.2020250
AEUR26.02.2020160
AEUR27.02.2020270
BUSD13.02.2020180
BUSD27.02.2020290
AEUR22.03.20201100
AEUR23.03.20203110
BUSD09.03.20201120
BUSD23.03.20203130
AEUR16.04.20202140
AEUR17.04.20203150
BUSD03.04.20202160
BUSD17.04.20203170

 

I need to calculate a balance for the account based on the report design table (dimReport) :

DescriptionAccount
Software1
Software2
Services3

 

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):

 

Description2020-012020-022020-032020-04Grand Total
Services3030270590590
A3030140290290
B  130300300
Software120420640940940
A30160260400400
B90260380540540
Grand Total15045091015301530

 

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), 

 

Balance_USD_BadTotals.png

 

and what I need or expect is this table:

 

Row Labels2020-012020-022020-032020-04Grand Total
Services36172416416
A3642116116
B  130300300
Software93292458700700
A33278160160
B90260380540540
Grand Total9629863011161116

 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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):

 

How the final report should look likeHow the final report should look like

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:

Step1-Table resultsStep1-Table results

 

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:

Step2-Table resultsStep2-Table results

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.

NewModel.png

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:

Report_from_Step2Table.png

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

Lucian
Responsive Resident
Responsive Resident

Hello @Greg_Deckler ,

 

Thank you again for your time, but I think I have found a solution.

 

FinalReport_and_CorrectData_corresponds.png

 

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

NewModel_v4.png

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

Report_from_FactTable_for_BALANCE.png

 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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