Total of calculated measure

[hcze]

Hi,

I have a Profit measure to calculate the difference between Revenue and Expense.

The DAX is 

Amount2 = If(MIN(Account[Account]) = "Profit",CALCULATE(SUM('Transaction'[Amount]),ALLSELECTED('Transaction')),SUM('Transaction'[Amount]))

I would like to include the Profit into total. The total should show 0 for control check but I am unable to achieve that. This is what I have so far.

Thanks for helping.

Account	Index
Revenue	1
Expense	2
Profit	3

Account	Amount
Revenue	-200
Expense	100

[v-shex-msft · Accepted solution]

HI @hcze,

You can use lookupvalue function to simple get the result based on the current account:

Measure =
LOOKUPVALUE (
    Transaction[Amount],
    Account[Account], SELECTEDVALUE ( Transaction[Account] ),
    SUM ( Account[Amount] )
)

Regards,

Xiaoxin Sheng

[amitchandak]

@hcze , if you have table like the last one(Table2), Then better create a table like this and join with account table

Union( Table2,
summarize(Table2, "Account", "Profit", "Amount", sum(Table2[Amount])))

[samdthompson]

hello, perhaps something like :

Amount2

VAR transaction=SUM('Transaction'[Amount])

VAR Revenue=calculate(transaction,filter(account,[account]="revenue"))

VAR Expense=calculate(transaction,filter(account,[account]="expense"))

VAR Profit=Revenue+Expense

RETURN
=IF(HASONEVALUE(account[account]),transaction,Revenue+Expense-Profit)

I am on a mobile so cant error check this but think it will work. you might need to alter a couple of the table names to better suit your situation.