How To create a list of Debits or Credits from a Profit and Loss Column

[GinoM]

This is so simple to do in Excel but I'm struggling to replicate using Dax measure  which im new to

 
I have a table of Sales items   and a Table of Cost items    Ive created simple sum measures to give me Total Sales   and another to give me Total Costs ..    A third Measure "Sales" - "Cost"    Gives me Profit and Loss.. by a list of projects.

Rather Than a single column for profit and Loss   Im trying to create  a column for Profit and another for Loss  ..     

I acheive a sort of result using an If statement   ie  Profit:= IF([Sales]>[Cost],[Sales]-[Cost],"0")     whilst this gives me values per  project  I don't get a total value  nor I'm i able to use the measure "Profit"    as part of any other calculation .... 

Thanks 

Gino

[v-chenwuz-msft · Accepted solution]

Hi @GinoM ,

Can you try some measures like the following:

Profit =
VAR _sales =
    CALCULATE(
        SUM( table[Sales] ),
        FILTER( table, [project] = EARLIER( table[project] ) )
    )
VAR _costs =
    CALCULATE(
        SUM( table[Costs] ),
        FILTER( table, [project] = EARLIER( table[project] ) )
    )
RETURN
    IF( _sales > _costs, _sales - _costs, 0 )

This only works fine in creating a column and each rows of one project will get the same result.

If you want a summary table, you may try SUMMARIZE()

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GinoM]

Thanks for you help with this 😎

[smpa01]

@GinoM  posts with sample data (in table format) and desired output move very fast in this forum.