cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
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

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

 


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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

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.

Top Solution Authors
Top Kudoed Authors