cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

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

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.

1 ACCEPTED SOLUTION
Community Support

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.

``````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:

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

Best Regards,

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

3 REPLIES 3
Community Support

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.

``````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:

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

Best Regards,

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

Community Champion

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 (
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.

Community Champion

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 (
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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors