Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
Solved! Go to Solution.
Try
Credit =
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 😀.
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?
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".
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 "
"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
23 | |
12 | |
11 | |
10 |