topic Re: Calculate the sum for all accounts for different companies with different currencies in DAX Commands and Tips

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

Anonymous — Fri, 07 Oct 2022 08:26:00 GMT

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.

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

johnt75 — Fri, 07 Oct 2022 08:44:28 GMT

Try

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

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

Anonymous — Sat, 08 Oct 2022 05:49:24 GMT

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 "

How to Get Your Question Answered Quickly

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

Anonymous — Sat, 08 Oct 2022 06:45:33 GMT

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?

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

johnt75 — Mon, 10 Oct 2022 09:11:33 GMT

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

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

Anonymous — Mon, 10 Oct 2022 09:40:56 GMT

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

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

johnt75 — Mon, 10 Oct 2022 09:45:50 GMT

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

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

Anonymous — Mon, 10 Oct 2022 09:50:40 GMT

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 )

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

johnt75 — Mon, 10 Oct 2022 10:13:41 GMT

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

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

Anonymous — Mon, 10 Oct 2022 10:47:57 GMT

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