accounting math question

[Anonymous]

So I have a database that just keeps all transaction amounts in one column, regardless of charge or refund.  There is a seperate column that tracks if refund (true/flase)

I need to calculate the net amount charged.  how do i create a colum that will subtract the amounts if a refund is true?

I have 9 accounting item type, so I need to add them all and make sure that if the refund attribute is true, then I subtract that amount.

Im Trying to call it balance due.

[MFelix]

Hi @ Dave_C,

Try something like this

Balance due =
CALCULATE ( SUM ( Table[Amoumt] ), MAX ( Table[Refund] ) <> TRUE () )
- CALCULATE ( SUM ( Table[Amoumt] ), MAX ( Table[Refund] ) = TRUE ())

Assiming your table is a true false field.

Not on computer but should work.

Regaeds,
Mfelix

[Anonymous]

I have gotten the math to work finally, problem is I have data in two different tables to use in the equation.  Im getting an error when I try and use the total fee which is in table A, and need to subtract total charged which is in table B.  Says I cant do this in direct querry

[Anonymous]

I keep getting these stupid mesages

Column = RELATED(vwAppraisalComposite[TotalFee])-(AppraisalAccountingItem[Net Total])

Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models.

[parry2k · Accepted solution]

There is limitation in DAX when you direct query because of performance.

Goto option and Settings -> Options -> Direct Query and check "Allow unrestricated measures in Direct Query mode" and see if this works for you. 

There are lot of articles what are the restriction in direct query mode, please check those out.

[Anonymous]

FYI, I had already set this option up and it did not work.  I had to turn it off and then back on 2 more times in order to get it to work.