Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
msimmonds22
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.

 

msimmonds22_1-1709315804519.png

 

 

1 ACCEPTED SOLUTION
v-yifanw-msft
Community Support
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.

vyifanwmsft_1-1709537041050.png

 

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.

vyifanwmsft_0-1709536954847.png

 

Final output:

vyifanwmsft_2-1709537125982.png

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.

View solution in original post

3 REPLIES 3
v-yifanw-msft
Community Support
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.

vyifanwmsft_1-1709537041050.png

 

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.

vyifanwmsft_0-1709536954847.png

 

Final output:

vyifanwmsft_2-1709537125982.png

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors