topic Re: Calculating sum of values of two categories and showing it as the third category as balancing in DAX Commands and Tips

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

MiraAbel — Thu, 04 Apr 2024 14:18:47 GMT

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

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

lbendlin — Thu, 04 Apr 2024 22:11:48 GMT

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?

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

Anonymous — Mon, 08 Apr 2024 03:11:50 GMT

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.