cancel
Showing results for
Did you mean:
Helper II

## measure to sum column B if column A meets criteria otherwise sum column C

Hi,

I have a table like

Table_Test =
DATATABLE
(
"name"      , STRING ,
"Status"    , STRING ,
"price"     , INTEGER ,
"cost"      , INTEGER ,
{
{"A", "sold", 200, 100},
{"A", "onHand", 200, 100},
{"B","sold", 300, 150},
{"B","onHand", 300,150}
}
)

I want to create a measure like this:

Total cash = IF( [Status] = "sold", SUM([price]), SUMX(Table_Test, [cost] * -1) ) ​

of course, it can not work since [status] is not in row-context.

And I do not want to new a column like this:

cash = IF([Status] = "sold", [price], [cost]*-1)

then sum it to realize my wish.
appreciate help, thanks!
2 ACCEPTED SOLUTIONS
Super User IV

@hongjyan , if they are from one table

sumx(Table_Test,IF( Table_Test[Status] = "sold", (Table_Test[price]), Table_Test[cost] * -1 ) )​

Proud to be a Super User!

Super User II

try

sumx( Table_Test,
IF( Table_Test[Status] = "onHand",
Table_Test[cost] * -1,
IF(Table_Test[price] > 0, [price], 0)
)
)

do not hesitate to give a kudo to useful posts and mark solutions as solution
4 REPLIES 4
Super User IV

@hongjyan , if they are from one table

sumx(Table_Test,IF( Table_Test[Status] = "sold", (Table_Test[price]), Table_Test[cost] * -1 ) )​

Proud to be a Super User!

Helper II

thanks you!!!

how about if I want to consider cost but ignore price if it is negetive when do sum, for example

Table_Test =
DATATABLE
(
"name"      , STRING ,
"Status"    , STRING ,
"price"     , INTEGER ,
"cost"      , INTEGER ,
{
{"A", "sold", 200, 100},
{"A", "onHand", 200, 100},
{"B","sold", 300, 150},
{"B","onHand", 300,150},
{"oil20200426", "won'tsold", -50, 100}
}
)

how to realiza something like
sumx( Table_Test,
IF( Table_Test[Status] = "onHand",
Table_Test[cost] * -1,
Table_Test[price] when [price] is positive)
)
)​?

Super User II

try

sumx( Table_Test,
IF( Table_Test[Status] = "onHand",
Table_Test[cost] * -1,
IF(Table_Test[price] > 0, [price], 0)
)
)

do not hesitate to give a kudo to useful posts and mark solutions as solution
Super User III

You can use the FILTER function instead.

Something like:

Total cash = SUMX(FILTER(Table_Test, Table_Test[Status]= "sold"), Table_Test[price]) - SUMX(FILTER(Table_Test, Table_Test[status]<>"sold"),Table_Test[cost])

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

www.excelwithallison.com

Announcements