how to add SUMX

[sai1995]

Sum of Realized Proceeds =

VAR Sale = CALCULATE(SUM(vCashFlow[Amount]),vCashFlow[Category]="Sale")

VAR Interest = CALCULATE(SUM(vCashFlow[Amount]),vCashFlow[Category]="Interest")

VAR Paydown = CALCULATE(SUM(vCashFlow[Amount]),vCashFlow[Category]="Paydown")

Var Expense = CALCULATE(SUM(vCashFlow[Amount]),vCashFlow[Category]="Expense")

VAR FX = CALCULATE(SUM(vCashFlow[Amount]),vCashFlow[Category]="FX")

VAR Income = CALCULATE(SUM(vCashFlow[Amount]),vCashFlow[Category]="Income")

VAR Drawdown = CALCULATE(SUM(vCashFlow[Amount]),vCashFlow[Category]="Drawdown")

VAR Hedge_Unrealized = CALCULATE(SUM(vCashFlow[Amount]),vCashFlow[Category]="Hedge Unrealized")

VAR Hedge = CALCULATE(SUM(vCashFlow[Amount]),vCashFlow[Category]="Hedge")

VAR Total_MV = CALCULATE(SUM(vCashFlow[Amount]),vCashFlow[Category]="Total MV")

RETURN IF(Sale > 5000 || Interest > 600000 || Paydown > 250000, Interest + Expense + Sale + FX + Income + Drawdown + Paydown +

    IF(Hedge_Unrealized<>0 && Total_MV=0,Hedge_Unrealized+Hedge,IF(Total_MV=0,Hedge,0)),0)

[v-robertq-msft · Accepted solution]

Hi, 

According to your description, I can roughly understand your requirement, do you mean that you want to use the SUMX() function to get the sum value based on your logic?

I think you can transform the DAX formula like this to make it clear:

Sum of Realized Proceeds =

VAR Sale = SUMX(Filter(ALL(vCashFlow),vCashFlow[Category]="Sale"),vCashFlow[Amount])

VAR Interest = SUMX(Filter(ALL(vCashFlow),vCashFlow[Category]="Interest"),vCashFlow[Amount])

VAR Paydown = SUMX(Filter(ALL(vCashFlow),vCashFlow[Category]="Paydown"),vCashFlow[Amount])

VAR Hedge_Unrealized = SUMX(Filter(ALL(vCashFlow),vCashFlow[Category]="Hedge Unrealized"),vCashFlow[Amount])

VAR Total_MV = SUMX(Filter(ALL(vCashFlow),vCashFlow[Category]="Total MV"),vCashFlow[Amount])

return

IF(Sale > 5000 || Interest > 600000 || Paydown > 250000, SUMX(FILTER(ALL(vCashFlow), vCashFlow[Category] in {"Interest" ,"Expense","Sale "," FX ","Income ","Drawdown","Paydown"}), vCashFlow[Amount])

+

IF(Hedge_Unrealized<>0 && Total_MV=0, SUMX(FILTER(ALL(vCashFlow), vCashFlow[Category] in {"Hedge Unrealized" ,"Hedge"}), vCashFlow[Amount]),IF(Total_MV=0, SUMX(FILTER(ALL(vCashFlow), vCashFlow[Category]="Hedge"), vCashFlow[Amount]),0)),0)

And you can get what you want.

If this can’ help you to achieve the result you wanted, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

[Greg_Deckler]

@sai1995 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.