Sum column B by Column A IF the sum of column C by Column A <> 0

[msimmonds22]

OK, I know that subject may look like garbage, but hear me out.

One of the primary record types for my business is Jobs.  

Each Job can have multiple Transactions associated with it.

Each Transaction will have a dollar value for Amount and may have a dollar value for Retainage.

Where things get complicated is that the same TransactionID may be associated with the job multiple times.

(Yes, I know the optimal course would be to find what is causing the multiple iterations and filter down to one but all of my support resources are have yet to help me with that)

The goal is:
When total Amount for a Job is not equal to 0 (it may be positive or negative) then

Take the Maximum Retainage value for each Transaction associated with that Job and add them together so that

We arrive at a Total Sum of all Maximum Retainage values for all Jobs where the total Amount does not equal 0.

I know how I can get the sum of the Amounts per Job.

And I know how I can get the Max value of Retainage for a transaction.

What I need help with is putting that together so I am adding the max retainage values for all transactions associated wtih a job where the total value of the job <> 0.

I have an exmaple of how the process should work below.

[v-yifanw-msft · Accepted solution]

Hi @msimmonds22 ,

The answer of @123abc  is very informative, while I used another method to solve the problem for you. You can follow the steps below:

1.Add an index column in Power Query.

2.Add new columns.

Max Retainage = 
VAR _Rank =
    RANKX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Transaction] = EARLIER ( 'Table'[Transaction] )
        ),
        'Table'[Index],
        ,
        ASC
    )
VAR _MaxRetainage =
    CALCULATE (
        MAX ( 'Table'[Retainage] ),
        FILTER (
            'Table',
            'Table'[Job] = EARLIER ( 'Table'[Job] )
                && 'Table'[Transaction] = EARLIER ( 'Table'[Transaction] )
        )
    )
RETURN
    IF ( _Rank = 1, _MaxRetainage, 0 )

Result = 
VAR _Result =
    CALCULATE (
        SUM ( 'Table'[Max Retainage] ),
        FILTER ( 'Table', 'Table'[Job] = EARLIER ( 'Table'[Job] ) )
    )
VAR _TotalAmount =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( 'Table', 'Table'[Job] = EARLIER ( 'Table'[Job] ) )
    )
RETURN
    IF ( _TotalAmount = 0, 0, _Result )

3.Do not summarize.

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[123abc]

To achieve the desired result in Power BI using DAX, you can follow these steps:

1. First, you need to create a new table with the Total Amount for each Job.
2. Then, calculate the Total Retainage for each Job based on the condition where the Total Amount is not equal to 0.
3. Finally, summarize the Total Retainage for those Jobs.

Here's how you can write DAX expressions to achieve this:

Assuming you have a table named 'Transactions' with columns Job, Transaction, Amount, and Retainage:

1. Create a new table to calculate the Total Amount for each Job:

TotalAmountPerJob =
SUMMARIZECOLUMNS (
'Transactions'[Job],
"TotalAmount", SUM('Transactions'[Amount])
)

Create a new table to calculate the Total Retainage for each Job where the Total Amount is not equal to 0:

TotalRetainagePerJob =
FILTER (
ADDCOLUMNS (
TotalAmountPerJob,
"TotalRetainage",
CALCULATE (
SUMX (
FILTER (
'Transactions',
'Transactions'[Job] = EARLIER('Transactions'[Job])
),
MAX('Transactions'[Retainage])
)
)
),
[TotalAmount] <> 0
)

Finally, summarize the Total Retainage for those Jobs:

FinalOutput =
SUMMARIZE (
TotalRetainagePerJob,
'TotalRetainagePerJob'[Job],
"Retainage", SUM('TotalRetainagePerJob'[TotalRetainage])
)

These DAX expressions should provide you with the desired output where you calculate the Total Retainage for Jobs where the Total Amount is not equal to 0.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[123abc]

To achieve the desired result in Power BI using DAX, you can follow these steps:

1. First, you need to create a new table with the Total Amount for each Job.
2. Then, calculate the Total Retainage for each Job based on the condition where the Total Amount is not equal to 0.
3. Finally, summarize the Total Retainage for those Jobs.

Here's how you can write DAX expressions to achieve this:

Assuming you have a table named 'Transactions' with columns Job, Transaction, Amount, and Retainage:

1. Create a new table to calculate the Total Amount for each Job:

TotalAmountPerJob =
SUMMARIZECOLUMNS (
'Transactions'[Job],
"TotalAmount", SUM('Transactions'[Amount])
)

Create a new table to calculate the Total Retainage for each Job where the Total Amount is not equal to 0:

TotalRetainagePerJob =
FILTER (
ADDCOLUMNS (
TotalAmountPerJob,
"TotalRetainage",
CALCULATE (
SUMX (
FILTER (
'Transactions',
'Transactions'[Job] = EARLIER('Transactions'[Job])
),
MAX('Transactions'[Retainage])
)
)
),
[TotalAmount] <> 0
)

Finally, summarize the Total Retainage for those Jobs:

FinalOutput =
SUMMARIZE (
TotalRetainagePerJob,
'TotalRetainagePerJob'[Job],
"Retainage", SUM('TotalRetainagePerJob'[TotalRetainage])
)

These DAX expressions should provide you with the desired output where you calculate the Total Retainage for Jobs where the Total Amount is not equal to 0.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.