Calculating sum of values of two categories and showing it as the third category as balancing

[MiraAbel]

Hello, DAX Wizzards
I have three types of accounts in the Balance Sheet:
1. Aggregation that is a measure of Running Total multiplied by the end of the month FX rate.
2. Fixed Rate Valuation that is a measure of Running Total at historical FX rate
3. Foreign Currency Translation that is to be an opposite sum of Aggregation and Fixed Rate Valuation
In example below I would like to show another line before Total  that is to be -3,391,740.18 which is then turns Total into zero

I have half written my DAX

@Test3 Trial Balance Amount  (FX Rate) = 

VAR FCTR_BS =
   CALCULATE([Aggregation BSheet Accounts],
       VALUES( 'NL Mapping'[FX Valuation]),
       'NL Mapping'[FX Valuation] = "Aggregation")
        
VAR FCTR_FixRate = 
       CALCULATE(
        [Running Totals (FX historical, with spill)],
          VALUES( 'NL Mapping'[FX Valuation]),
       'NL Mapping'[FX Valuation] = "Fixed Rate Valuation")
        

//VAR FCTR = 
// DAX to be inserted


VAR Result =
    FCTR_BS+FCTR_FixRate+FCTR
RETURN
    Result

Could you please help me, if at all? 

Thank you very much indeed

[lbendlin · Accepted solution]

You don't only need the number, you also need the statement account value for that Foreign Currency Translation.  Is that part of your dimension table?

[v-zhouwen-msft · Accepted solution]

Hi @lbendlin ,thanks for the quick reply, I'll add further.

Hi @MiraAbel ,

The Table data is shown below:(The last line was added manually by me)

1. Use the following DAX expression to create a column

Column = SWITCH(TRUE(),
'Table'[Balance Sheet] = "A",1,
'Table'[Balance Sheet] = "B",2)

2. Use the following DAX expression to create a measure

Measure = 
var _a = CALCULATE(SUM('Table'[Amount]),VALUES('Table'[Balance Sheet]),'Table'[Balance Sheet] = "A")
VAR _b = CALCULATE(SUM('Table'[Amount]),VALUES('Table'[Balance Sheet]),'Table'[Balance Sheet] = "B")
VAR _c = CALCULATE(SUMX(FILTER('Table','Table'[Balance Sheet] = "A"),[Amount]),ALL('Table'[Balance Sheet]))
VAR _d = CALCULATE(SUMX(FILTER('Table','Table'[Balance Sheet] = "B"),[Amount]),ALL('Table'[Balance Sheet]))
RETURN 
IF(MAX('Table'[Amount]) = BLANK(),-(_c+_d),
IF(SELECTEDVALUE('Table'[Column]),_a+_b,
IF(COUNTROWS('Table') <> COUNTAX(ALL('Table'),[Balance Sheet]) ,_a+_b,
0)))

3. Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

You don't only need the number, you also need the statement account value for that Foreign Currency Translation.  Is that part of your dimension table?