Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hcze
Helper I
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.
 
2020-10-19_9-38-42.jpg
 
Thanks for helping.
 
AccountIndex
Revenue1
Expense2
Profit3
 
 
AccountAmount
Revenue-200
Expense100
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
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.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
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.
amitchandak
Super User
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])))

samdthompson
Memorable Member
Memorable Member

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.