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
feltaha
Frequent Visitor

Calculate the sum for all accounts for different companies with different currencies

I am trying to get the TB for multiple companies in different geographical areas with different currencies. Mainly two, one is AED and the second is ZAR for which I have created a table with historical daily exchange rates for all the past years. the table Actuals contains the transaction as is with 1 to 1 translation. Under U27 for example, it is recording in AED, also under Z02 it is recording in Rand. 

 

I already created two measures that will calculate the sum of all transactions for a single account taking into consideration the sign of the transaction. If the sign is -1 then the measure would be credit, if the sign of the transaction is 1 the measure would be debit. 

 

What I would like to achieve is, if the company is Z02, I need to pull the respective date from the actuals table and find the respective exchange rate from the Date table, then divide the Actuals[amount] by the exchange rate "Date[ZAR]" then sum it based on the sign. If -1 sum it under the credit measure, if 1 sum it under the debit measure. 

 

Credit = CALCULATE(SUM(Actuals[Amount]), Actuals[Sign]= -1)

Debit = CALCULATE(SUM(Actuals[Amount]), Actuals[Sign]= 1)

 

Table relationship. 

feltaha_0-1665129575365.png

 

Sample data of the Actual table with headers. 

AccountACCDATECompanySignAmount
115010011/9/2019U07-123.43
115010011/10/2019U09-17.41
115010011/11/2019U10-173.28
115010011/12/2019U12-135.95
115010011/13/2019U21114.18
115010031/14/2019U09175.40
115010031/15/2019U15145.31
115010031/16/2019Z02143.36
115010031/17/2019U08123.87
115010041/18/2019U09162.80
115010041/19/2019U15145.51
115010041/20/2019U08153.53
115010051/21/2019U09-15.58
115010051/22/2019U13-14.84
115010051/23/2019U14-139.65
115010051/24/2019U15-199.27
115010051/25/2019Z02-121.32
115010051/26/2019U13-152.67
115010061/27/2019U08-179.46
115010061/28/2019U21161.56
115010071/29/2019U11184.15
115010071/30/2019U11165.63
115010091/31/2019U21113.85
115010102/1/2019U21134.94
115010112/2/2019Z0218.28
115010122/3/2019U09120.94
115010122/4/2019U21158.95
115010132/5/2019Z02118.27
115010132/6/2019U08193.30
115010132/7/2019U13133.46
115010142/8/2019U1512.53
115010142/9/2019U2113.34
115010142/10/2019U08134.80
115010142/11/2019U0812.41
115010152/12/2019Z02114.00
115010162/13/2019U21184.98
115010162/14/2019U15118.68
115010172/15/2019U09137.80
115010182/16/2019U151357.72
115010182/17/2019U211336.85
115010192/18/2019U131657.19
115010192/19/2019U081544.88
115010192/20/2019U091923.10
115010202/21/2019U131930.18
115010212/22/2019U151400.58
115010212/23/2019U211762.21

 

Sample data of the Date table "which includes currency exchange rates". 

DateZAR
1/1/20193.9
1/2/20193.6
1/3/20194.1
1/4/20194.2
1/5/20194.3
1/6/20194.4
1/7/20194.5
1/8/20194.6
1/9/20194.7
1/10/20194.8
1/11/20194.9
1/12/20194.2
1/13/20194.6
1/14/20194.7
1/15/20194.1
1/1/2020

4.2

1/2/2020

4.3

1/3/2020

4.2

 

This is the result visual I am trying to reach. 

feltaha_1-1665129766828.png

 

2 ACCEPTED SOLUTIONS
johnt75
Super User
Super User

Try

Credit =
CALCULATE (
    SUMX (
        Actuals,
        IF (
            SELECTEDVALUE ( Actuals[Company] ) = "Z02",
            DIVIDE ( Actuals[Amount], RELATED ( 'Date'[ZAR] ) ),
            Actuals[Amount]
        )
    ),
    Actuals[Sign] = -1
)

View solution in original post

OK, it turns out that SELECTEDVALUE does not count row context from an iterator as being part of the filter context, which I did not know. Remove the SELECTEDVALUE calls so that it is just 

Actuals[Company] = "Z02"

and that will pick up the appropriate value based on the row context.

You learn something new every day 😀.

View solution in original post

9 REPLIES 9
johnt75
Super User
Super User

Try

Credit =
CALCULATE (
    SUMX (
        Actuals,
        IF (
            SELECTEDVALUE ( Actuals[Company] ) = "Z02",
            DIVIDE ( Actuals[Amount], RELATED ( 'Date'[ZAR] ) ),
            Actuals[Amount]
        )
    ),
    Actuals[Sign] = -1
)

Hi Johnt75, 

 

One more clarification if you may: 
As you can see in the image below, the grand total at the bottom is not calculated correctly. I just put two functions next to each other in the same visual, the grand total for all the companies at the bottom is the same for both "Rand Debit: which divides the Amount by 4 for Z02" and the "Debit: which sums all the amounts without translation". 

 

Note: the Grand total at the bottom is not a measure, it is automatically generated by the Matrix visual. Any ideas as to why this is happening?

feltaha_1-1665211185207.png

 



The grand total uses the same measure definition as for the individual rows, so normally the problem with grand totals is that they are run without a row context, which the individual line items produce. However in this case the measure introduces it's own row context through an iterator, so I don't see that that is the problem.

The first step is probably to find out which value, if any, is correct. Export the data into Excel and use that to calculate the total, and compare that figure to the ones generated in the report

Hi Johnt75, 

 

I exported the report to excel, and the totals are correct. in a sense that the Rand Debit sums up to an accurate value of 3,922,301,814 which is different from the report. The report shows the same value as the debit measure "which is a simple calculate that adds up the debit without any exchange rate dividing". 

 

feltaha_1-1665394840126.png

 

 

OK, so the problem is with the rand credit and rand debit measures. can you post their definitions.

Rand Credit =
CALCULATE (
SUMX (
Actuals,
IF (
SELECTEDVALUE ( Actuals[Company] ) = "Z02",
DIVIDE ( Actuals[Amount], RELATED ( 'Date'[ZAR] ) ),
Actuals[Amount]
)
),
Actuals[Sign] = -1
)

Rand Debit =
CALCULATE (
SUMX (
Actuals,
IF (
SELECTEDVALUE ( Actuals[Company] ) = "Z02",
DIVIDE ( Actuals[Amount], RELATED ( 'Date'[ZAR] ) ),
Actuals[Amount]
)
),
Actuals[Sign] = 1
)

OK, it turns out that SELECTEDVALUE does not count row context from an iterator as being part of the filter context, which I did not know. Remove the SELECTEDVALUE calls so that it is just 

Actuals[Company] = "Z02"

and that will pick up the appropriate value based on the row context.

You learn something new every day 😀.

Confirmed, the values are showing accurate numbers now. I double-checked the average exchange rate for the year 2021 on the totals and the numbers are very close "Which is a satisfactory result for the finance department.  

 

Indeed @johnt75, I learned a lot today as well. 😄

Hey @johnt75   

Thanks, the answer worked like a charm. I didn't even have to alter anything in the Dax formula. Kudos. 

 

Thank you @Greg_Deckler for the tips "

feltaha_0-1665208092759.png

 

How to Get Your Question Answered Quickly 

 "

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors