cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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
Syndicate_Admin
Administrator
Administrator

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.

View solution in original post

3 REPLIES 3
Syndicate_Admin
Administrator
Administrator

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

Syndicate_Admin
Administrator
Administrator

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.

View solution in original post

Thanks for you help with this 😎

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

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.

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

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

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Users online (1,994)