Sum values in Column C where the value in Column B matches value in Column A

[duane1]

Hi.

In my table below, I would like to sum the values in "FROM_COUNT2" where the value in "FROM_PRODUCTID" matches the vaue in "TO_PRODUCTID".  In this example, the TO_COUNT2 column should contain the sum of all the FROM_COUNT2 values where the TO_PRODUCTID matches the FROM_PRODUCTID.  In excel, I would use SUMIF to do this easily.  Sometimes, there is more than one value in the TO_PRODUCTID that matches the FROM_PRODUCTID.

This is in Power Pivot but I assume the same formula I would use in other power platforms like BI or query.  Let me know if it should be posted elsewhere.  Thanks.

[Anonymous · Accepted solution]

Hi @duane1 ,

Would you like to achieve the following results?

Please try to add a new column and use the code below.

TO_COUNT2 = 
CALCULATE(
    SUM(TEST[FROM_COUNT2]),
    FILTER(
        TEST,
        TEST[TO_PRODUCTID] = EARLIER(TEST[FROM_PRODUCTID])
    )
)

Could you please provide example data or sample files here if you have any confused? We could offer you more help if we have information in detail. There is sensitive data that can be removed in advance. How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thanks for your understanding. Your time and cooperation are much valued by us. We are looking forward to hearing from you to assist further.

Best regards,

Lucy Chen

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

If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Bibiano_Geraldo · Accepted solution]

Hi @duane1 ,

Create a new calculated column using this DAX:

TO_COUNT2 = 
CALCULATE(
    SUM(TableName[FROM_COUNT2]),
    FILTER(
        TableName,
        TableName[TO_PRODUCTID] = EARLIER(TableName[FROM_PRODUCTID])
    )
)

Replace table and columns names with your owns

[duane1]

Thanks, this is what worked.  I received the same help from another.  

[Anonymous · Accepted solution]

Hi @duane1 ,

Would you like to achieve the following results?

Please try to add a new column and use the code below.

TO_COUNT2 = 
CALCULATE(
    SUM(TEST[FROM_COUNT2]),
    FILTER(
        TEST,
        TEST[TO_PRODUCTID] = EARLIER(TEST[FROM_PRODUCTID])
    )
)

Could you please provide example data or sample files here if you have any confused? We could offer you more help if we have information in detail. There is sensitive data that can be removed in advance. How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thanks for your understanding. Your time and cooperation are much valued by us. We are looking forward to hearing from you to assist further.

Best regards,

Lucy Chen

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

If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[duane1]

Thanks, this worked.  I had a feeling EARLIER was involved.  I was learning about it.  This is the solution.

[lbendlin]

EARLIER is a legacy function, and not recommended. Use variables.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...