cancel
Showing results 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

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.

Sample data of the Actual table with headers.

 Account ACCDATE Company Sign Amount 11501001 1/9/2019 U07 -1 23.43 11501001 1/10/2019 U09 -1 7.41 11501001 1/11/2019 U10 -1 73.28 11501001 1/12/2019 U12 -1 35.95 11501001 1/13/2019 U21 1 14.18 11501003 1/14/2019 U09 1 75.40 11501003 1/15/2019 U15 1 45.31 11501003 1/16/2019 Z02 1 43.36 11501003 1/17/2019 U08 1 23.87 11501004 1/18/2019 U09 1 62.80 11501004 1/19/2019 U15 1 45.51 11501004 1/20/2019 U08 1 53.53 11501005 1/21/2019 U09 -1 5.58 11501005 1/22/2019 U13 -1 4.84 11501005 1/23/2019 U14 -1 39.65 11501005 1/24/2019 U15 -1 99.27 11501005 1/25/2019 Z02 -1 21.32 11501005 1/26/2019 U13 -1 52.67 11501006 1/27/2019 U08 -1 79.46 11501006 1/28/2019 U21 1 61.56 11501007 1/29/2019 U11 1 84.15 11501007 1/30/2019 U11 1 65.63 11501009 1/31/2019 U21 1 13.85 11501010 2/1/2019 U21 1 34.94 11501011 2/2/2019 Z02 1 8.28 11501012 2/3/2019 U09 1 20.94 11501012 2/4/2019 U21 1 58.95 11501013 2/5/2019 Z02 1 18.27 11501013 2/6/2019 U08 1 93.30 11501013 2/7/2019 U13 1 33.46 11501014 2/8/2019 U15 1 2.53 11501014 2/9/2019 U21 1 3.34 11501014 2/10/2019 U08 1 34.80 11501014 2/11/2019 U08 1 2.41 11501015 2/12/2019 Z02 1 14.00 11501016 2/13/2019 U21 1 84.98 11501016 2/14/2019 U15 1 18.68 11501017 2/15/2019 U09 1 37.80 11501018 2/16/2019 U15 1 357.72 11501018 2/17/2019 U21 1 336.85 11501019 2/18/2019 U13 1 657.19 11501019 2/19/2019 U08 1 544.88 11501019 2/20/2019 U09 1 923.10 11501020 2/21/2019 U13 1 930.18 11501021 2/22/2019 U15 1 400.58 11501021 2/23/2019 U21 1 762.21

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

 Date ZAR 1/1/2019 3.9 1/2/2019 3.6 1/3/2019 4.1 1/4/2019 4.2 1/5/2019 4.3 1/6/2019 4.4 1/7/2019 4.5 1/8/2019 4.6 1/9/2019 4.7 1/10/2019 4.8 1/11/2019 4.9 1/12/2019 4.2 1/13/2019 4.6 1/14/2019 4.7 1/15/2019 4.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.

2 ACCEPTED SOLUTIONS
Super User

Try

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

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 😀.

9 REPLIES 9
Super User

Try

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

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?

Super User

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

Frequent Visitor

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".

Super User

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

Frequent Visitor
``````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
)``````
Super User

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 😀.

Frequent Visitor

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. 😄

Frequent Visitor

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 "

"