topic Re: how to add SUMX in DAX Commands and Tips

how to add SUMX

sai1995 — Fri, 03 Dec 2021 17:58:50 GMT

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)

Re: how to add SUMX

Greg_Deckler — Fri, 03 Dec 2021 19:12:40 GMT

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.

Re: how to add SUMX

v-robertq-msft — Tue, 07 Dec 2021 08:18:56 GMT

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