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.

#### Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

#### Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Users online (1,994)