cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hongjyan
Helper II
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
amitchandak
Super User IV
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 ) )​



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Hi @hongjyan 

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
LinkedIn

View solution in original post

4 REPLIES 4
amitchandak
Super User IV
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 ) )​



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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

Hi @hongjyan 

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
LinkedIn

View solution in original post

AllisonKennedy
Super User III
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. 

 

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

 



?? Check out my March Madness Report??


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. 


www.excelwithallison.com

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.