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

Accepted Solutions 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 Impactful Individual

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. Super User IV

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

Proud to be a Super User! 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. Announcements #### Power Platform Community Conference

Check out the on demand sessions that are available now! #### Power Platform October Community Highlights

Check out the top community contributors across all of the communities #### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella. Top Solution Authors
Top Kudoed Authors
Users online (3,519)