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

[hongjyan]

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!

[amitchandak · Accepted solution]

@hongjyan , if they are from one table

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

[amitchandak · Accepted solution]

@hongjyan , if they are from one table

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

[hongjyan]

@amitchandak,

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)
           )
)​?

[az38 · Accepted solution]

Hi @hongjyan 

try

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

[AllisonKennedy]

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. 

If you found this post helpful, please give Kudos.

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

https://sites.google.com/site/allisonkennedycv