cancel
Showing results for
Did you mean:  How To create a list of Debits or Credits from a Profit and Loss Column

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

1 ACCEPTED SOLUTION

Accepted Solutions  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.

3 REPLIES 3  @GinoM  posts with sample data (in table format) and desired output move very fast in this forum.  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.  Thanks for you help with this 😎 Announcements 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.  