Dax formula to do a lookup/related in another table

[michellepace]

Hello,

I am at the beginning of my Dax learning curve and really struggling. I'm importing accounting transactional data where income is postive and expenses are negative. So far I have this formula which works fine:

Actuals = SUM( 'Account Transactions'[Amount] )

Now, I'd like to represent my negtaive values as postive numbers in my matrix as well as graphs. If I write it in psudeo code, this is what it looks like:

Actuals_ForMatrix&Graphs = 

var isExpense = if([IncomeExpense]="Expense", true, false) **need help here!

return if (isExpense, [Actuals] * -1, [Actuals])

 

Can anyone please help me write the dax I need?

[Fowmy]

@michellepace 

Try

 

 = ABS([Actuals])

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[michellepace]

Hi @Fowmy, thank you for your reply. I'm afraid it is not as simple as wrapping with an ABS function. For my expenses, I need to change positive values to negative values and negative values to positive values. To wrap it with an ABS function would throw my numbers out. For example, you can incur an expense when buying 100 boxes but then ask your supplier for a credit note for 20 of thos boxes. I really need to do as I say, multiple by (-1) for every ledger transactional line where [IncomeExpense]="Expense".

Also, I'm really keen to wrap my head around this little piece of dax, I'm hoping it will help in my undrestanding. Surely it cannot be that difficult? It's pretty simple in exel 🙂 

[Fowmy · Accepted solution]

@michellepace 

Try this measure:

You mentioned + into - and the other way is required in the comment but your measure tries to do the following leaving Income as it is.

Actuals_ForMatrix&Graphs = 

SUMX(
    'Account Transactions',

    IF(
        RELATED('Account Categories'[IncomeExpense]) = "Expense",
        [Actuals] * -1,
        [Actuals]
    )
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[michellepace]

Thank you @Fowmy !! It's so simple when you know how :). Thanks very much, exactly what I was looking for. 

I figured out another way to achieve the same thing using PowerQuery editor. I merged the IncomeExpense column from table AccountCategories into the table AccountTransactions. Then I created a conditional column using: 

= Table.AddColumn(#"Expanded Account Categories", "Amount(+)", each if [Account Categories.IncomeExpense] = "Expense" then [Amount]*-1 else [Amount])

My Last Question:
In terms of performance, is there a preferable way and which is best? Using dax as you've shown, or doing it with Power Query (for example, is one better for performance than another)?

[Fowmy]

@michellepace 

Glad it worked for you!

It is better to keep it with DAX it is more on the calculation side. When you do in PQ, it adds additional data load and it is static.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[amitchandak]

@michellepace , Try like ?

if( [IncomeExpense]="Expense",-1*[Actuals] ,[Actuals] )

[michellepace]

Hi @amitchandak , please take a look at my original question. [IncomeExpense] is not a measure I have defined. That is the problem. I don't know how to look up a value. Please read my question and let me know your thoughts?

[Greg_Deckler]

@michellepace - You should just be able to wrap your calculation with ABS function