cancel
Showing results for
Did you mean:
Helper I

## Total of calculated measure

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
1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
3 REPLIES 3
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User

@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])))

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Super User

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.

// if this is a solution please mark as such. Kudos always appreciated.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!