Joining Rows

[davidwhite83]

I am trying to create a financial report from our companies financial data. I want to compute the total gross profit from our labor sales and labor cost. Once the GP is generated I want it deduct from the expenses to give me a net profit. 

Question1: How to combine Rows so that I get overall gross profit:

Labor Type	Acct#	Acct Type	Amount
CP - CP Labor	4400	Sale	$25.00
CC - CP Labor COS	6400	COS	$12.00
WP - WP Labor	4600	Sale	$30.00
WC - WP Labor COS	6600	COS	$13.00

So I want to combine CP and WP so that the Total Sales amount is $55.00. Create a calculation to combine CP and CC to show gross as $13.00, and then combine CP Gross and WP Gross to show over gross profit of $30.00.

Any assistance will be good. 

[v-yingjl · Accepted solution]

Hi @davidwhite83 ,

You can create a calculated column to define the category first:

Category = 
IF (
    CONTAINSSTRING ( 'Table'[Labor Type], "CP" ),
    "CP",
    IF ( CONTAINSSTRING ( 'Table'[Labor Type], "WP" ), "WP" )
)

Then create two measures to calculate the total and gross profit.

Total Sales = 
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER ( 'Table', 'Table'[Acct Type] = "Sale" )
)

Gross profit = 
VAR tab =
    SUMMARIZE (
        'Table',
        'Table'[Category],
        'Table'[Acct Type],
        'Table'[Amount],
        "A",
            CALCULATE (
                SUM ( 'Table'[Amount] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Category] = EARLIER ( 'Table'[Category] )
                        && 'Table'[Acct Type] = "Sale"
                )
            )
                - CALCULATE (
                    SUM ( 'Table'[Amount] ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Category] = EARLIER ( 'Table'[Category] )
                            && 'Table'[Acct Type] = "COS"
                    )
                )
    )
RETURN
    SUMX ( SUMMARIZE ( tab, [A] ), [A] )

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ToddChitt]

This is the Power Query forum, but that won't stop us from giving DAX advice:

Create 2 new measures:

Total Sales = CALCULATE ( SUM ('Table Name'[Amount]), 'Table Name'[Type] = "Sales" )

Total COS = CALCULATE ( SUM ('Table Name'[Amount]), 'Table Name'[Type] = "COS" )

That should get you started. For more comples calculations, you will need the OR operator in the FILTER portion of the CALCULATE statement like this:

Total CC and CP = CALCULATE ( SUM ('Table Name'[Amount]), 'Table Name'[Labor Type] = "CP - CP Labor" ) ||  'Table Name'[Labor Type] = "CC - CP Labor COS" )

Hope that helps.